How to Copy Formatting in Excel (2024 Guide)

puneet-gogia-excel-champs

- Written by Puneet

Let’s say you are working on a sales report, each sheet representing a different month. You’ve spent a reasonable amount of time making the January sheet look just right; you’ve got bold headers, specific number formats for sales figures, and color-coded cells to highlight important values.

Now, it’s time to prepare the report for February. Instead of applying formatting again, which can be time-consuming, you can quickly copy the formatting from the January sheet.

To copy formatting from one cell or a range of multiple cells, you need to select it first and then copy it. After that, select the cell or range where you want to paste the formatting. Here you can use the paste special option to only paste the formatting and leave the rest.

copy-formatting-in-excel

In this tutorial, we will learn different ways to copy and paste only formatting from a cell or a range of cells in Excel.

Copy Formatting in Excel (Paste Special)

Paste Special allows you choose exactly what you want to paste from copied data. For example, you can copy a cell and use Paste Special to paste only the cell’s formatting, values, or formulas into another cell.

  1. First, select the cell or the range and press Ctrl + C to copy the cell.
    copy-formatting-using-paste-special
  2. After that, select the cells where you want to paste the formatting.
    select-cells-to-paste-formatting
  3. Now, go to the home tab and open the paste option from there, or use the keyboard shortcut Alt + H + S to open the paste special.
    open-paste-special-option
  4. Next, select the format and click OK to paste the formats only.
    choose-formats-only

And once you click OK, you get the formatting to the selected cell. This way, you can quickly copy and paste the formatting from a cell or a range of cells.

Copy Formatting using Format Painter

Format Painter helps you quickly copy and apply one cell or range’s formatting to cells and range. Click on the cell with the formatting you like, click the Format Painter brush button, and then select the cells where you want that formatting to apply.

Below, you have cell A1 with a value and accounting number format. And now you need to get the same formatting for cell B1.

copy-cell-formatting-using-format-painter
  1. Select cell A1.
    select-the-cell
  2. Go to the home tab and click on the format painter button.
    click-format-painter-button
  3. This will convert the mouse cursor into a paintbrush.
    mouse-cursor-to-paintbrush
  4. Selecting no other cell or option, click on cell B1. It will instantly apply cell formatting to cell B1.
    select-the-cell-to-paste

Keyboard Shortcut to Copy Formatting

You can also use a keyboard shortcut to copy and paste formatting.

  • Select the cell and press the shortcut key Ctrl + C.
  • Select the cell where you want to apply the formatting and press the key AltHVR.

Use Drag and Drop Menu to Copy Cell Format

In Excel, the “drag and drop” menu allows you to quickly move or copy values and formatting from a cell or a range of cells to another cell and range of cells.

  1. Select cell A1 or where you have the formatting.
    copy-cell-formatting-using-drag-drop
  2. Hover your mouse on the border of the cell and press the right-click button from the mouse and hold the button.
    hover-mouse-on-the-cell-border
  3. Drop it to the cell where you want to paste the formatting.
    drag-where-to-paste-formatting
  4. Release the button to get the menu.
    release-button-for-menu
  5. Select the option “Copy Here as Formats Only”.
    copy-as-formats-only

Copy Formatting with Fill Handle

If you have formatting on a cell and you want to copy and paste that formatting to the cells downwards, you can use the fill handle for that.

  1. Select cell A1, and hover your mouse on the bottom right corner of the cell.
    copy-formatting-with-fill-handle
  2. Hold the mouse left button and drag it down to cell A2.
    hold-mouse-and-drag
  3. You’ll get the AutoFill options at the bottom of the cell.
    autofill-options
  4. Click on the drop-down and select “Fill Formatting Only”.
    choose-fill-formatting-only

If you can’t see the auto-fill drop-down, go to the File Tab ⇢ Excel Options ⇢ Advanced ⇢ Editing Options ⇢ Enable Fill Handle.

And, if you still can’t see it, go to the File Tab ⇢ Excel Options ⇢ Cut, Copy, and Paste ⇢ Show Paste Options Button when content is pasted.

VBA Copy Formatting from a Cell

Sub CopyAndPasteFormatting()
    ' Define the source cell from which the formatting will be copied
    Dim sourceCell As Range
    Set sourceCell = ThisWorkbook.Sheets("Sheet1").Range("A1")
    
    ' Copy the formatting from the source cell
    sourceCell.Copy
    
    ' Paste the formatting to a single destination cell
    Dim singleDestinationCell As Range
    Set singleDestinationCell = ThisWorkbook.Sheets("Sheet1").Range("B1")
    singleDestinationCell.PasteSpecial Paste:=xlPasteFormats
    
    ' Paste the formatting to a range of destination cells
    Dim rangeDestination As Range
    Set rangeDestination = ThisWorkbook.Sheets("Sheet1").Range("C1:C10")
    rangeDestination.PasteSpecial Paste:=xlPasteFormats
    
    ' Clear clipboard to release memory
    Application.CutCopyMode = False
End Sub

To run this code, press ALT + F11 to open the VBA editor. Once the editor is open, insert a new module by selecting Insert > Module from the menu. Then, paste the VBA code into this new module. After the code is in place, you can run the CopyAndPasteFormatting code by pressing F5 or selecting Run from the menu.

Create a New Cell Style from the Selected Cell

Let’s say you have a cell that is formatting with some specific formatting, and now you want to use that formatting for some other cells as well.

Here, we have a code that will create a new cell style from the selected cell, and then you can apply it to other cells and a range of cells.

Sub SaveCellStyle()
    Dim selectedCell As Range
    Dim styleName As String
    Dim newStyle As Style

    ' Check if a cell is selected
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a cell to create a style from its formatting.", vbInformation
        Exit Sub
    End If

    ' Initialize the selected cell
    Set selectedCell = Selection.Cells(1, 1)
    
    ' Prompt user to enter a name for the new style
    styleName = InputBox("Enter a name for the new cell style:", "Create New Style")

    ' Exit if no name is provided
    If styleName = "" Then Exit Sub

    ' Check if the style already exists to prevent errors
    On Error Resume Next
    Set newStyle = ThisWorkbook.Styles(styleName)
    If Not newStyle Is Nothing Then
        MsgBox "Style name already exists. Please choose a different name.", vbCritical
        Exit Sub
    End If
    On Error GoTo 0

    ' Create a new style with formatting from the selected cell
    Set newStyle = ThisWorkbook.Styles.Add(styleName)
    With newStyle
        .IncludeNumber = True
        .IncludeFont = True
        .IncludeAlignment = True
        .IncludeBorder = True
        .IncludePatterns = True
        .IncludeProtection = True
        .NumberFormat = selectedCell.NumberFormat
        .Font.Name = selectedCell.Font.Name
        .Font.Size = selectedCell.Font.Size
        .Font.Bold = selectedCell.Font.Bold
        .Font.Italic = selectedCell.Font.Italic
        .Font.Underline = selectedCell.Font.Underline
        .Font.Strikethrough = selectedCell.Font.Strikethrough
        .Font.Color = selectedCell.Font.Color
        .Interior.Color = selectedCell.Interior.Color
        .Interior.Pattern = selectedCell.Interior.Pattern
        .HorizontalAlignment = selectedCell.HorizontalAlignment
        .VerticalAlignment = selectedCell.VerticalAlignment
        .BorderAround Color:=selectedCell.Borders(xlEdgeLeft).Color, Weight:=selectedCell.Borders(xlEdgeLeft).Weight
    End With

    MsgBox "New style '" & styleName & "' has been created successfully.", vbInformation
End Sub
Last Updated: May 10, 2024