How to Copy Only Formatting from a Range to Another Range in Excel

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.

How to Copy Only Formatting from a Range to Another 1.png

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.

How to Copy Only Formatting from a Range to Another 2.png

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.

How to Copy Only Formatting from a Range to Another 3.png

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.

How to Copy Only Formatting from a Range to Another 4.png

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.

How to Copy Only Formatting from a Range to Another 5.png

Step2: In the VBE, select the opened sheet you want to run the VBA code, select “Insert” from the menu bar, then choose “Module“.

How to Copy Only Formatting from a Range to Another 6.png

Step3: Create VBA code.

How to Copy Only Formatting from a Range to Another 7.png

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.

How to Copy Only Formatting from a Range to Another 8.png

Step6: Select the macro you just created and click the Run button.

How to Copy Only Formatting from a Range to Another 9.png

Step7: After running the macro, the destination range is applied with the formatting properly.

How to Copy Only Formatting from a Range to Another 10.png

Leave a Reply

Your email address will not be published. Required fields are marked *