This quick Excel tutorial will help you learn how to copy formatting in Excel.

Ever found yourself staring at a meticulously formatted spreadsheet, wishing you could replicate that same style across multiple cells or even another worksheet? The copy formatting skill in Excel is your answer. It’s a simple yet powerful skill that allows you to quickly and easily apply the same formatting, including fonts, font sizes, colors, borders, and cell alignment, to multiple cells with just a few clicks.

Whether you’re a seasoned Excel user or a basic-level user, understanding how to copy formatting can save you time and effort. It also helps you to ensure your spreadsheets look professional and consistent.

Follow along with the methods outlined below where I’ll walk you through the step-by-step process of copying formatting in Excel in various ways, like user interface tools, secret methods, as well as programmatic approaches.

Using the Format Painter Tool

Format Painter is the most convenient way to copy formatting in Excel and apply the same to different cells, cell ranges, worksheets, and workbooks.

Firstly, explore below the quick actions you can make to use this effortless format copying feature:

• One Click on the Format Painter Button: When you click once on the Format Painter command after selecting the source format, Excel will apply the copied formatting only once on the destination cell range you select.
• Double-click on the Format Painter Button: If you click on the Format Painter command twice, Excel keeps on applying the copied formatting to multiple cell range selections one after the other. It only stops when you hit the Esc key.
• Keyboard Shortcuts: After selecting the source table or cell range, you can press the Format Painter keyboard shortcut, Ctrl +Alt + C. Now, you can go to a destination cell range and press Ctrl + Alt + V to use the Excel Format Painter feature. In this method, you won’t see the default Excel cursor transforming into a paintbrush icon.

Let’s look at an example below. For this exercise, I’ve used the above worksheet dataset. The cell range A1:D11 is the source formatting that I want to apply to the dataset in the cell range F1:I11.

I’ll select the source dataset that has the target formatting.

Click on the Format Painter button once in the Clipboard block of the Home tab.

Now, I’ll select the destination cell range up to the cell range where I exactly want to apply the formatting.

Excel will automatically transform the destination cell range with the copied formatting.

Let’s say, there are more such datasets matching the structure and organization of the source dataset.

You need to apply the source formatting to the rest of the datasets in the same or different worksheets.

In this scenario, you must click on the Format Painter button twice.

Now, you can select one dataset to apply the formatting.

Then, select another one to keep copying the copied text and cell formatting configurations.

Excel will keep transforming datasets when you click on a cell range until you hit the Esc key.

Format Painter also works on various Excel objects, like AutoShapes, formatted pictures, and other manually drawn objects.

For instance, you’ve created a custom-formatted rectangular shape as a Form Control button in a worksheet.

If you wish to copy the same shape formatting styles to all other Form Control buttons in the same workbook, select the source shape, and double-click on it.

Now, navigate to the destination worksheet and select a default button to custom-format it.

Keep selecting the basic shapes to transform those. When you’re done, press the Esc key.

Using Paste Special – Formats Feature

While the Format Painter tool makes format copying in Excel super easy, it’s only available in Excel 2016 or later editions.

You can’t use the Format Painter if you’re working on an older Excel edition, like Excel 2010. Here, the Paste Special tool steps in. Also, it’s available in most of the Excel versions.

To practice this method, select a table or dataset from which you only want to copy the formatting to a different cell range.

Press the shortcut for copying, which is Ctrl + C.

Navigate to the destination cell range. Alternatively, you can go to a different worksheet as well.

Select the target cell range and press Ctrl + Alt + V.

The Paste Special dialog box will show up.

Select the Formats category under the Paste section.

Click OK to apply the formatting.

Using the Drag-and-Drop Formatting Action

The right-click drag-and-drop method for copying formatting from one table or dataset to another table or dataset only works in the same worksheet. So, if you want to reproduce formatting in a new dataset from an existing table on a small scale, you can use this method.

For instance, you want to import formatting to F1:I11 from A1:D11.

Select the entire source formatting cell range.

Hover the cursor over the border of the dataset.

Right-click and drag the silhouette of the dataset onto the destination dataset or table.

As you let go of the right-click, a context menu shows up.

Select Copy Here as Formats Only option.

Excel will modify the style of the dataset according to the source dataset.

Using the Same Cell Style

Often a format you like might have come from the Cell Styles library of Excel. So, if you can figure out the name of the style you can apply it whenever you like to whichever dataset you want. Also, you don’t need to look for a matching dataset structure or organization.

Click on any cell on the source dataset.

Navigate to the Styles block of the Home tab and click on the Cell Styles drop-down menu.

You should see one of the cell styles has been selected in the Cell Styles menu.

For example, in the current exercise, it’s the Output style.

So, once you’ve located the used cell style, apply this style to the other dataset to give a consistent look to your workbook.

Using the Excel Templates Feature

Do you need to copy formatting from one workbook to another? Instead of manually copying the cell styles, you can simply save the source workbook as a template. Then, open the template as a new workbook and paste new data into the available datasets or tables.

Here’s a step-by-step explanation of the entire process:

Firstly, open the source workbook and click on the File tab.

The Excel backstage view will show up. There, click on the Save As button in the left-side navigation panel.

Click Browse below the Other locations section on the right-side menu.

The Save As dialog box will open.

Enter a preferred template name in the File name field.

Click on the Save as type drop-down menu and choose the Excel Template option.

The Save As dialog box will automatically select the destination directory.

Change that to an easily accessible folder on your PC by manually choosing it from the left-side navigation panel of Save As.

Click the Save button to save the template file.

Now, go to the destination directory and open the Excel template.

A new workbook with the data structures from the template will open.

You can copy raw data from your database or other data sources and paste that on the select tables and datasets of the template by pressing Ctrl + Shift + V.

This move will replace the old data with new while preserving the existing formatting.

Before closing, don’t forget to save the workbook.

📒 Read More: 3 Ways to Save a Read-Only Workbook in Microsoft Excel

Using a Conditional Formatting Rule

Often, you’ll see custom formatting show up when you paste specific data in a table or dataset. It means those cells have Conditional Formatting in the backend. Copying such formatting styles can be a bit tricky if you don’t know how to duplicate a Conditional Formatting rule.

Let me show you the process in simple steps below:

Copying in the Same Worksheet

Go to the source worksheet and click on the Conditional Formatting drop-down menu. Select Manage Rules from the context menu that shows up.

The Conditional Formatting Rules Manager dialog box will show up.

Click on the drop-down arrow for the Show formatting rules for field and select This Worksheet.

All of the available rules will now show up in the dialog box.

Select the rule you want to copy and hit the Duplicate Rule button.

A new rule will be created. Select that and edit the cell range in the Applies to field.

Click Apply to finalize the rule duplication.

You’ve successfully copied formatting created by the said Conditional Formatting rule.

Copying to a Different Worksheet

Follow the same steps as mentioned earlier to come to the phase where you need to edit the Applies to field.

Remove the existing cell range reference of the field except the equals sign.

Navigate to the other worksheet and select the input cell range.

Click Apply to complete the process.

Using an Excel VBA Script

If you’re wondering how to copy formatting in Excel with automation, you should explore this Excel VBA-based method.

Here, I’ve demonstrated two approaches, one for formatting copying from datasets or tables and another for Excel objects, like shapes.

If you’ve been using Excel VBA already, you can go straight to the scripts mentioned below and test those by creating macros.

However, if you don’t know your way around Excel VBA, go through the following tutorial first:

📒 Read More: How To Use The VBA Code You Find Online

Copying Formatting From a Dataset

Use this script to create a macro that copies formatting from cell ranges:

Sub CopyFormatting()
    Dim sourceRange As Range
    Dim destRange As Range
    Dim confirm As VbMsgBoxResult
    
    ' Show input box to select the source range
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the source range to copy formatting from:", Type:=8)
    On Error GoTo 0
    
    ' Check if the user canceled the input box
    If sourceRange Is Nothing Then Exit Sub
    
    ' Show input box to select the destination range
    On Error Resume Next
    Set destRange = Application.InputBox("Select the destination range to apply formatting to:", Type:=8)
    On Error GoTo 0
    
    ' Check if the user canceled the input box
    If destRange Is Nothing Then Exit Sub
    
    ' Show confirmation dialog box
    confirm = MsgBox("Are you sure you want to copy the formatting from the source range to the destination range?", vbYesNo + vbQuestion, "Confirm Formatting Copy")
    
    ' If user confirms, copy the formatting
    If confirm = vbYes Then
        sourceRange.Copy
        destRange.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        MsgBox "Formatting copied successfully!", vbInformation, "Success"
    Else
        MsgBox "Formatting copy canceled.", vbExclamation, "Canceled"
    End If
End Sub

To execute the macro you’ve created, press Alt + F8 and select the CopyFormatting macro. Hit Run to execute.

When prompted, select the cell range for the source of cell styles.

Enter the cell range where you want to export the selected formatting.

Excel will modify the destination cell range instantly.

Copying Formatting From an Excel Object

If you wish to copy formatting from object to object, use this script:

Sub CopyShapeFormatting()
    Dim sourceShapeName As String
    Dim targetShapeName As String
    Dim sourceShape As Shape
    Dim targetShape As Shape

    ' Get the name of the source shape
    sourceShapeName = InputBox("Enter the name of the shape to copy formatting from:")
    ' Get the name of the target shape
    targetShapeName = InputBox("Enter the name of the shape to apply formatting to:")

    ' Set the shapes
    On Error Resume Next
    Set sourceShape = ActiveSheet.Shapes(sourceShapeName)
    Set targetShape = ActiveSheet.Shapes(targetShapeName)
    On Error GoTo 0

    ' Check if shapes exist
    If sourceShape Is Nothing Then
        MsgBox "Source shape not found!", vbExclamation
        Exit Sub
    End If

    If targetShape Is Nothing Then
        MsgBox "Target shape not found!", vbExclamation
        Exit Sub
    End If

    ' Copy formatting
    With targetShape
        .Fill.ForeColor.RGB = sourceShape.Fill.ForeColor.RGB
        .Line.ForeColor.RGB = sourceShape.Line.ForeColor.RGB
        .Line.Weight = sourceShape.Line.Weight
        .TextFrame2.TextRange.Font.Name = sourceShape.TextFrame2.TextRange.Font.Name
        .TextFrame2.TextRange.Font.Size = sourceShape.TextFrame2.TextRange.Font.Size
        .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = sourceShape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB
    End With

    ' Confirm completion
    MsgBox "Formatting copied successfully!", vbInformation
End Sub

After executing this macro, you’ll need to enter the name of the object in a VBA prompt.

In the next dialog box, you’ll need to type in the destination object name.

A VBA confirmation dialog will appear if all goes successfully.

Conclusions

These are the most popular methods you must practice to learn how to copy formatting in Excel.

For small-scale applications, you can use the Format Painter tool, Paste Special dialog box, or the drag-and-drop method.

However, if the worksheets you’re working on are too large, you should practice the Excel VBA-based method for automation.

Comment below to share your feedback and suggestions about this Excel tutorial.