How to use Format Painter in Excel

You might agree that copying formatting in Excel is one of the most challenging tasks. Formatting includes cell color, font styles, borders, and number formats, which can be specific to individual cells or ranges. You need to navigate various formatting options in Excel’s ribbon, which can be time-consuming.

Here’s the good news: you have Format Painter in Excel, which simplifies this process by quickly pasting, copying, and formatting from one cell or range to another. And I’m super excited to share about this tool with you. We will learn to use it and learn about other possibilities that come with it. So, let’s get started.

What is the Format Painter?

Format Painter is an interactive option that allows you to copy the formatting of a specific cell or a range and paste it into another cell or a range. This powerful option allows you to quickly change the lengthy dataset’s styling, as manually changing the formatting of the independent cells consumes a lot of users’ time.

Where Format Painter is in Ribbon Located

It is in the Home tab with the Clipboard group. The paintbrush icon represents the “Format painter” as shown below:

How to use the Format Painter

Below are the steps to use the Format Painter are given below:

1. You may click on the specific cell containing the interactive formatting you are willing to employ in selected cell ranges.
2. Switch to the Home tab and choose the Format button icon from the Clipboard group.
3. Furthermore, once the paintbrush cursor is activated, you may impose it with the specific cell ranges in the worksheet to alter its default formatting of the selected ranges of cells.

Using the Format Painter to copy formatting to an Entire Range

Using Format Painter is quite easy, as you have a button to use it on the ribbon:

1. First, Daniel wants to change the default formatting of the cell range D3:D5. He created the sample worksheet shown below. The cell reference B3 already contains the interactive formatting. Choose the B3 cell.
   
2. Now, navigate to the Home tab and select the Format Painter from the Clipboard group.
   
3. After that, the Format Painter brush is activated. Select the range D2:D4 to apply the same formatting.
   
4. As you can observe in the screenshot, the formatting of the B3 cell has been copied and applied to the specific cell ranges D2:D4.

Using Shortcut for Format Painter

There is no straightforward shortcut for the format painter option.

• Keyword Shortcut for copying the cell formatting: First, you can use “Ctrl + C” to copy the formatting of the specific cell.
• Keyword Shortcut for pasting the formatting to another cell: Select the destination cell ranges. Furthermore, you can select “Alt + E” -> “S” -> “T” to open the Paste Special dialog box. You can click on the OK button.

Once you click the OK button, the formatting of the cells’ destination ranges will change. Open the new worksheet in Excel and enter the sample numeric value in the B column. Select the cell B3 and press the “Ctrl + C” keys like this:

You can select the cell range E3:E5.

Moreover, you can press the “Alt + E” keys. After that, choose the S key and press the T key from the keyboard. The Paste Special dialog box will automatically open, and you can click the OK button.

As you can notice in the screenshot, the formatting of the selected cell range has been changed similarly to that of the B3 cell through the keyword’s shortcut.

Copy Formatting to Multiple Cells

You can double-click on the “Format Painter” to copy the selected cell formatting and impose it on many desired cells. Let’s understand through an interesting example. Select the B4 cell and double-click on the “Format Painter” option.

After that, drop this active paintbrush cursor to the desired cells like B9, D9, and F9 cells to apply the same B4 cell formatting.

Press the “ESC” key to hide the format painter option that retains the cursor’s original property.

Using Paste Special to Copy Formatting

First, copy the formatting and use the “ctrl + c” shortcut. Then, paste the formatting using the “ALT+E+S+T” keywords or the Paste option inside the Clipboard group. For example: Select the B4 cell and press the “ctrl+ c” keys as given below:

Furthermore, you can select the range B9:B11, move to the Home tab, click on the Paste tile, and select the “Paste Special” option.

The “Paste Special” dialog box appears. In it, you may click on the Formats option under the Paste label and, finally, click on the OK button.

Therefore, the formatting of the targeted cell ranges has been altered. Despite the Paste Special option, you can use the Formatting(R) option inside the Paste tile to format the targeted fields.

Using the Format Painter to Copy the Formatting of One Chart to Another Chart

Suppose you have two different interactive charts created in the two different worksheets. Here are screenshots of both charts. In the screenshot below, the vertical axes series is 0, 500, 1000, 1500, and so on. The horizontal axes are 1, 2, 3, and 4.

In the second example, the vertical axis series is 0, 200, 400, 600 and so on that means data is depicted in the difference of 200. There is no horizontal axes series.

Now, click on the chart area in Sheet1, move to the Home tab, and select the Format Painter option under the Clipboard group as given below:

Furthermore, you can switch to Sheet 2 and drop the paintbrush cursor to the chart area. Therefore, the chart formatting of the second chart has been changed.

In the screenshot below, the vertical axes series has been changed, like the first chart, and horizontal axes are also visible, like the first chart.

Note: Only the horizontal and vertical axes series would change in the second chart through the Format Painter option.

The limited formatting from one chart to another is possible through Format Painter. For example, the background of the specific chart will not be copied and applied to the other chart using the Format Painter.

To overcome these issues, you need to select a specific chart and use the keyword “Ctrl + C” to copy one chart.

You can also use the Formats option in the Paste Special dialog box for more flexibility while copying formatting between two charts.