When you want to ensure consistency in the formatting of a range of cells, copying only the formatting can be helpful. For instance, if you have a large spreadsheet with several rows and columns, you may want to ensure that all the cells have the same font, size, and color, which can be achieved by copying the formatting from one range to another. And copying formatting is a great time saver, it avoids errors when manually adjusting fonts and styles.
See the example below. We want the two “SALES” columns have the same range formatting. This post shares three methods including VBA code to copy only formatting from one range to another in Excel worksheet.
Copy Only Formatting from a Range to Another by Format Painter Tool
Format Painter is useful in Excel and it can copy the style and formatting from a cell or a range and apply it on your destination cell or range in a quick way.
You can copy only the formatting from one range to another range by Format Painter tool in Excel by the following steps:
Step1: Select the cell that has the formatting you want to copy. In this example, the source range is B4:B8 which contains one number format and two background colors in cells.
Step2: Click on the “Format Painter” button in the “Home” tab of the Excel ribbon. The cursor will change to a paintbrush.
Step3: Select the range of cells that you want to apply the formatting to. The copied range’s formatting will be applied to the entire destination range.
Copy Only Formatting from a Range to Another by Paste Special -> Formatting Feature
You can also copy only formatting from a range to another by Excel “Paste Special” feature, this feature contains several “Paste” methods, and “Formatting” which copies only formatting and ignores the contents.
Here are the steps:
Step1: Select the cell or range that contains the formatting you want to copy.
Step2: Press “Ctrl + C” or right-click the selection and choose “Copy” from the context menu.
Step3: Select the cell or range where you want to apply the formatting.
Step4: Right-click the selection and choose “Paste Special” -> “Formatting” from the context menu.
This will copy only the formatting from the original range to the destination range.
Copy Only Formatting from a Range to Another by Excel VBA Module
If you are familiar with Excel VBA, you can also copy only formatting from a range to another range in Excel using VBA code. Here are the steps:
Step1: Press Alt+F11 or click Visual Basic in Develop tab to open the Visual Basic Editor.
Step2: In the VBE, select the opened sheet you want to run the VBA code, select “Insert” from the menu bar, then choose “Module“.
Step3: Create VBA code.
a. Define the source range and destination range by declaring two range variables using the “Range” object in VBA.
b. Use the “Copy” method of the source range object to copy the formatting only. This can be done by setting the “Paste Special” method to paste only the formatting.
c. Clear the clipboard to remove any copied data by using the “Application.CutCopyMode” method.
The complete VBA code should look like this:
Sub CopyFormattingOnly()
Dim rngSource As Range
Dim rngDestination As Range
Set rngSource = Range("A1:D10") 'Change to your desired source range
Set rngDestination = Range("F1:I10") 'Change to your desired destination range
rngSource.Copy
rngDestination.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub
Step4: Save the VBA code, close the editor and go back to the worksheet.
Step5: To run the macro, press Alt+F8 to open the Macro dialog box or click Macros in Developer tab.
Step6: Select the macro you just created and click the Run button.
Step7: After running the macro, the destination range is applied with the formatting properly.
