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.
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.
- First, select the cell or the range and press Ctrl + C to copy the cell.
- After that, select the cells where you want to paste the formatting.
- 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.
- Next, select the format and click OK to paste the 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. Now, you need to get the same formatting for cell B1.
- Select cell A1.
- Go to the home tab and click on the format painter button.
- This will convert the mouse cursor into a paintbrush.
- Selecting no other cell or option, click on cell B1. It will instantly apply cell formatting to cell B1.
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 Alt ⇢ H ⇢ V ⇢ R.
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.
- Select cell A1 or where you have the formatting.
- Hover your mouse on the border of the cell and press the right-click button from the mouse and hold the button.
- Drop it to the cell where you want to paste the formatting.
- Release the button to get the menu.
- Select the option “Copy Here 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.
- Select cell A1, and hover your mouse on the bottom right corner of the cell.
- Hold the mouse left button and drag it down to cell A2.
- You’ll get the AutoFill options at the bottom of the cell.
- Click on the drop-down and select “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