If you find it highly repetitive and time-consuming to format all cells of your Excel worksheet you must learn how to use Format Painter in Excel.

Microsoft Excel offers an expansive list of text, number, and cell formatting to give your dashboards, worksheets, reports, data entry forms, etc., a unique and creative look.

However, consistently formatting your worksheet is always challenging because you may not remember the exact workflow of cell formatting for a certain cell range or you’re sharing the worksheet with family members, fellow students, co-researchers, and colleagues.

On top of everything, manually introducing multiple formatting elements to a cell range by observing the source is extremely repetitive. Here comes the Format Painter feature of Excel. You can use this automatic cell format cloning tool if you use Excel 2013 or newer desktop app, Excel for the web, Excel for iOS, and Excel for Android.

Using Excel Format Painter With Single-Click

You can find the Format Painter command button (looks like a paintbrush) in the Clipboard commands block just below the Home tab.

To clone a cell or cell range’s formatting to the destination, you must first select the source. Then, click on the Format Painter tool to copy all source formatting.

Now, go to the destination cell or cell range and click once to apply the copied formatting instantly.

The content of the destination cell won’t change but you’ll see changes in the font color, cell border, cell background color, font size, etc., identical to the source cell’s formatting.

If you wish to use a hotkey to use the Format Painter tool, you can navigate to the source cell or cell range for format copying and press Alt + H + F + P.

You can also access the Format Painter tool from the right-click context menu. Navigate to the source cell and right-click. You shall find the Format Painter button just below the Merge & Center tool located on the small text formatting toolbar above the right-click context menu.

Using Excel Format Painter for the Entire Worksheet

If you wish to apply a cell’s formatting style to the whole worksheet, you can do this for a new worksheet on the same workbook or a different workbook.

Simply, highlight the source cell and click on the Format Painter button in the ribbon menu.

Go to the destination worksheet and click on the Select All button of the worksheet. You can find this button in the top-left corner of the worksheet grid, where the row headings and column headings intersect. It’s usually denoted by a small square with a triangle pointing downwards and to the right, symbolizing the selection of all cells.

You must leave the source cell or cell range outside the range where you’re applying the Format Painter feature. Hence, you can’t apply Format Painter to all the cells of the source cell’s worksheet.

Using Format Painter for Entire Row and Column

Suppose, you’d like to apply the same cell formatting elements to the entire column. You can click Format Painter after highlighting the source cell or cell range.

Then, click on the column letters like A, B, C, D, etc., on the top of the target column. Excel shall apply the copied cell formatting to the selected column.

For a row, click on the row numbers, like 1, 2, 3, 4, etc., in the left-side border of the Excel worksheet.

Using Format Painter Excel With Double-Click

Suppose, you’d like to apply a select cell formatting to a few cell or cell ranges within the worksheet. But the destination cells and cell ranges aren’t contiguous. In that case, what you’d do is click the Format Painter tool, use it on the destination cell, and repeat the process for the next set of cells.

Again, this process becomes repetitive and manual. Instead, you can simply double-click on the Format Painter button after selecting the source cell.

Now, you’ve activated Format Painter for repeat usage. Click on as many cells or cell ranges as you like on the workbook or worksheet to clone source cell formatting. Once done, press the Esc key to deactivate the Format Painter button.

Using Excel Format Painter With Multiple Formatting

Suppose, you want to copy several formatting styles from the source cell range to one or many destination cell ranges using Format Painter. The process is the same as mentioned earlier. However, the styles shall be copied in the order they appear in the source cell range.

For example, I’ve copied cell formatting from A2:A5 to C2:C5 using a single-click Format Painter.

Now, if the destination cell range has more cells than the source cell range, Excel shall duplicate the formatting in additional cells as shown above.

Using Format Painter for Conditional Formatting

Besides copying cell formatting styles, you can also use Format Painter to clone Conditional Formatting rules.

Suppose, there are certain Conditional Formatting rules in your worksheet you want to use on other cell ranges. However, you don’t know how to create the rule yourself or don’t know the logic used behind the rule.

In that case, Format Painter comes in handy. Simply, highlight the cell that has at least one Conditional Formatting rule.

Click on the Format Painter button and then highlight the destination cell or cell range.

You shall see that Excel modifies the destination cell range according to the Conditional Formatting rule in the backend of the cell.

If the source cell contains more than one rule, Format Painter shall copy all of those to the new cell or cell range.

Using Paste Special as a Format Painter Alternative

Though Excel Format Painter is a great tool to automatically clone cell formatting styles, it’s not convenient to operate this tool only using keyboard navigation.

If you often use keyboard navigation in Excel for greater productivity, you can use the Paste Special tool as an alternative to Format Painter.

Copy the source cell by pressing the Ctrl + C keys.

Now, go to the destination cell or cell range and press Ctrl + Alt + V to bring up the Paste Special dialog box.

Click on the Formats selection below the Paste section.

Click OK and Excel shall apply the cell and content style formatting to the destination except the source cell content.

Using Format Painter Excel for Charts

Often, you spend hours perfecting various style elements of a chart in your Excel worksheet. When you create a new chart in the same or different worksheet, you might want to apply the same chart formatting style to the new chart.

This also enabled you to make your chart visuals consistent, which is a requirement for presentation purposes in professional spaces. Here, you can also use the Format Painter tool to quickly clone chart-style elements.

Format Painter enables you to copy chart formatting styles from elements like Axis Titles, Axis Labels, Chart Data Labels, and Chart Titles.

To copy a chart style, such as the Chart Data Label, select the source data and hit the Format Painter button on the Excel ribbon menu.

Now, go to the destination chart and click on the Chart Data Label. Excel shall quickly apply the source formatting to the destination Chart Data Label.

Repeat the steps for other chart elements like Axis Labels, Chart Titles, and so on.

Using Excel Format Painter Using Excel VBA

While Format Painter stands as a powerful tool for applying formatting across cells, harnessing Excel VBA extends its capabilities exponentially. Using a VBA macro, you can get interactive input boxes where you can enter the source cell range address, destination cell range addresses, etc.

Here’s a VBA script that you can use to automate the functions of Format Painter:

  Sub CopyCellFormatting()
    Dim SourceRange As Range
    Dim DestRanges As Variant
    Dim DestRange As Range
    Dim i As Integer

    ' Prompt the user to select the source range
    On Error Resume Next
    Set SourceRange = Application.InputBox("Select the source cell or cell range for copying cell formatting", Type:=8)
    On Error GoTo 0

    ' If no range is selected, exit the subroutine
    If SourceRange Is Nothing Then Exit Sub

    ' Prompt the user to enter the destination ranges
    DestRanges = Split(Application.InputBox("Enter the destination cells or cell ranges, separated by commas", Type:=2), ",")

    ' Loop through each destination range and apply the formatting
    For i = LBound(DestRanges) To UBound(DestRanges)
        Set DestRange = Range(DestRanges(i))
        SourceRange.Copy
        DestRange.PasteSpecial Paste:=xlPasteFormats
    Next i

    ' Clear the clipboard
    Application.CutCopyMode = False
End Sub

You can use the above script as is without modifying any code element because it’s flexible and works irrespective of any workbook, worksheet, or cell references.

To set up a VBA macro using the above script, check out the following Excel tutorial:

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

If you use this script, you shall get an input box for the cell style format’s source, like $A$2:$A$5 (for cell range) or $A$2 for a single cell.

Then, you’ll get another prompt to enter uncontiguous cells or contiguous cell ranges separated by a comma, such as $C$1:$C$5,$C$7:$C$11,'Sheet2 (4)'!$C$1:$C$5.

As you can see in the above cell references, you can also include destinations from different worksheets in the same workbook.

Finally, when you hit the OK button, Excel copies cell-style formatting from source to destination cell ranges.

Conclusions

These are the best methods you should know to effectively use Format Painter in Excel.

The Format Painter button in the Excel ribbon or right-click context menu empowers you to visually copy cell formatting from one cell to another.

If you often use this tool to copy formatting to multiple cell ranges using automation, you can try Excel VBA.

Give any or all the techniques mentioned above a try and comment below which one you liked the most. Also, if you know any better way to use Format Painter, you can mention that in your comment.