The Format Painter is one of the most useful, and time saving features of Excel. It lets you quickly copy and paste the formatting from one cell, multiple cells, or even an entire worksheet and apply it to multiple sheets, cells, or a single cell with a few clicks of the mouse.
We’ll explain not only what the Format Painter is, but explore some different use cases and highlight some tips and tricks to get the most out of this super helpful feature.
Let’s jump in!
Table of Contents
What is the Excel Format Painter?
The Excel Format Painter allows users to select a cell, series of cells, or an entire worksheet and copy the formatting of it. Then you can paste the formatting by selecting a cell or range to apply the formatting to. It’s a quick way to copy formatting from one part of your spreadsheet to another.
The Format Painter tool is available on the Home tab of the Microsoft Excel Ribbon under the Clipboard section as shown below.
Using the Format Painter is an efficient way to make sure that formatting stays consistent throughout your workbook, ultimately improving the look and credibility of the numbers being presented.
When to Use the Format Painter
Sometimes the biggest challenge in Excel is not only know what each tool does, but knowing when to use it. The Format Painter is prominently located on the Excel Ribbon, on the Home tab, for a reason and it’s because of how highly versatile the cool can be.
As a general rule of thumb, we recommend using only a handful of different colors when building an Excel worksheet and keeping formatting as consistent as possible among tabs, including the color and rows of where headers and data is located. Consistency improves the readability of reports for the end user.
Here are several use cases for the Format Painter to keep in mind.
- Consistency Across Datasets – Ensure that different sections of your spreadsheet have a consistent look.
- Efficient Formatting – The faster it is the apply a format, the more formatting can be used. Think beyond colors and utilize italics, bold, underlines, double underlines etc. to make your data stand out.
- Pre-Formatted Templates – Formatting can be applied to an entire sheet or multiple sheets at one time making it easy to copy and paste a formatted template when rolling over data or splitting sheets into categories.
One of the best features of the Format Painter is the flexibility to apply formatting to a singe cell, multiple cells, a row or column, an entire worksheet or multiple worksheets.
Now we’ll explain how to use the Format Painter along with some different use cases.
How to Use the Format Painter in Excel
To use the Excel Format Painter, start by selecting the cell or cell range that you want to copy the formatting of. Then click the Format Painter button on the Excel Ribbon from the Home section. Then click on the cell or cells you wish to apply the formatting to.
Let’s break down a few different scenarios that you may want to consider using.
Copying Formatting from One Cell to Another
The most common application of the Format Painter is to copy formatting from a single cell to another cell. You can do this by following these steps.
- Select the Cell: Click on the cell with the desired formatting.
- Activate Format Painter: Click the ‘Format Painter’ button (paintbrush icon) in the Home tab.
- Apply Formatting: Click on the target cell where you want the formatting to be replicated.
Tip: When you select the receiving cell, you can drag across a range of cells and apply the source formatting to many continuous cells in one step. Also, the cell you copy formatting to does not have to be on the same page, or even in the same worksheet or workbook.
Making the Format Painter Sticky for Multiple Applications
The Format Painter has a ‘sticky’ mode that allows you to apply formatting to multiple, non-continuous sections in your spreadsheet. This is useful when you need to copy and paste the formatting from one cell to a number of different cells where it’s not practical or possible to drag across a range of cells.
When you double click on the Format Painter the source formatting stays copied allowing you to copy and paste to many different cells with a paint brush cursor until you press Escape to go back to normal mode.
You can use stick mode with these steps:
- Activate Sticky Mode – Double-click the Format Painter button.
- Apply Formatting – Click on various cells or ranges to apply the copied formatting.
- Exit Sticky Mode – Press the ‘Esc’ key or click the Format Painter button again to deactivate.
This mode is particularly useful when you need to apply the same formatting across different parts of your spreadsheet.
Note: Sticky Mode for Format Painter can be used with single cells, or for multiple ranges, sheets, and the additional scenarios we’ll explain below.
Copying Formatting from a Range of Cells to Another Range of Cells
Similar to how you can copy and paste the formatting from a single cell to another cell, you can copy the formatting of a range of cells and apply it to another range of cells. This technique is especially useful when adding columns to an existing dataset that need to have consistent formatting across a number of rows.
- Select the Range – Click and drag to select the cells with the formatting you want to copy.
- Engage Format Painter – Click the ‘Format Painter’ button.
- Apply to Target Range – Select the range of cells where you want the formatting applied.
Tip: You can use this same technique to copy the formatting of an entire row or column to another row or column. Simply select a row or column by selecting the column letter or row number. Then apply it to another full column or full row.
Copying Formatting of a Full Worksheet to Another Worksheet
Taking things a step further, you can copy and paste formatting from an entire worksheet to another worksheet. This is a great method when you’re rolling spreadsheets over or want to ensure consistent formatting between tabs when each tab represents a different month, quarter, or year.
To copy formatting of one worksheet to another, follow these steps:
- Select Entire Worksheet: Click the corner button above row 1 and to the left of column A to select the whole sheet.
- Activate Format Painter: Click the ‘Format Painter’ button.
- Switch Sheets: Move to the target worksheet.
- Apply Formatting: Click the corner button to apply the formatting to the entire sheet.
The keyboard shortcut to select an entire page in Excel is CTRL + A as an alternative to pressing the triangle in the top left corner.
Tip: To copy and paste formatting to multiple worksheets, try enabling sticky mode for the format painter then apply formatting to different worksheets. If you need to copy and paste formatting to a large number of worksheets, you may want to use the Copy and Paste Special functionality to paste formatting.
Using Format Painter with Conditional Formatting
The Format Painter allows users to copy and paste conditional formatting along with regular formatting options. To do so, first setup conditional formatting on a range of cells. Then select the range, press the Format Painter button and click on the range of cells you wish to apply conditional formatting to.
Excel will automatically apply the same set of conditional formatting rules from the source cells to the target cells and apply the conditional formatting rules accordingly.
- Select Cells with Conditional Formatting: Click on a cell or range of cells that have the conditional formatting you want to copy.
- Apply Format Painter: Click the Format Painter button.
- Replicate Formatting: Click on or drag over the target cells where you want the conditional formatting applied.
Tip: Regardless of whether your cells are formatted with standard formatting or conditional formatting, the Format Painter will work the same. You can even mix and match traditional formatting with conditional formatting.
Using Format Painter for Charts and Graphs
Unfortunately, one area where the Format Painter does not currently work is with Excel charts and graphs. However, you can copy a graph, and paste special on an unformatted chart, and select paste formatting. This will copy the formatting from one chart to another.
Creating a Keyboard Shortcut for Format Painter
While there’s no default keyboard shortcut for the Format Painter, you can quickly create one. Right click on the Format Painter icon on the Home tab of the Excel Ribbon, and select Add to Quick Access Toolbar. Once added you can press ALT + The Quick Access Number Excel automatically assigns.
Here’s how in more detail.
Step 1.) Add the Format Painter to the Quick Access Toolbar
The Quick Access toolbar is a set of icons at the top of Excel, they are in-between the AutoSave settings and the title of the worksheet. You can pin buttons there by adding tools to the Quick Access Toolbar. The main benefit is that they are always visible regardless of which section of the ribbon is visible.
After adding the Format Painter to the Quick Access Toolbar, the icon will appear in the title bar of Excel.
Step 2.) Activate the Format Painter with a Keyboard Shortcut
To use the new shortcut, press ALT + the assigned number next to the Format Painter Icon. In the example below we would press ALT + 5. The exact number may vary depending on whether you have other items in the quick access bar. Numeric shortcuts are automatically assigned by Excel.
Even though there’s no way to execute the Format Painter with a standard keyboard shortcut, it’s very quick and easy to add it to the quick access bar and allow Excel to automatically assign a shortcut key for you.
The Format Painter in Excel is a versatile tool that extends beyond copying the formatting of a single cell, to being able to copy formatting from an entire cell range, column, row, or worksheet, It’s capable of copying static formatting like font size, or color along with copying conditional formatting rules that can be applied to other areas of your workbook or an entirely different workbook.
If you want to copy and paste chart formatting, try copying the chart and pasting formatting. This is an alternative option to using the format painter that in many times gives you more control over what is copied and pasted.
Even though no keyboard shortcut exists for the Format Painter, you can create one by adding the Format Painter to the Quick Access toolbar in Excel.