How To Use Format Painter in Excel

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.

Format Painter command
Format Painter command

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.

Highlight cells
Highlight cells

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

How to use Format Painter with single click
How to use Format Painter with a single click

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.

Using Format Painter shortcut key
Using Format Painter shortcut key

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.

Format Painter on right click menu
Format Painter on the right-click menu

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.

Copying cell formatting
Copying cell formatting

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

Click Select All button on worksheet
Click Select All button on worksheet

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.

Apply Format Painter to whole column
Apply Format Painter to the whole column

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.

Apply Format Painter to whole row
Apply Format Painter to the entire row

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.

Using Format Painter with double click
Using Format Painter with double click

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.

Copying multiple formatting using Format Painter
Copying multiple formatting using Format Painter

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

Format styles repeating
Format styles repeating

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.

Copying Conditional Formatting rule
Copying Conditional Formatting 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.

Applying Conditional Formatting with Format Painter
Applying Conditional Formatting with Format Painter

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

Applied rule to a range from single cell
Applied rule to a range from single-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.

Paste Special dialog box
Paste Special dialog box

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

Applied formatting using Paste Special
Applied formatting using Paste Special

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.

Format Painter on Chart Data Label
Format Painter on Chart Data Label

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.

Copied label formatting
Copied label formatting

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.

Copied various chart elements using Format Painter
Copied various chart elements using Format Painter

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:

A VBA script to automate Format Painter
A VBA script to automate 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

Input prompt for source
Input prompt for source

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.

Destination cell addresses
Destination cell addresses

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.

Copied cell style using VBA macro
Copied cell style using VBA macro

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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃