Format Painter In Excel

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

What Is A Format Painter In Excel?

Format Painter in Excel is a tool used to copy the formatting of a selected cell or a group of cells to the other cells. When we copy and paste data in a formatted dataset, we may lose the formatting. Instead of redoing the formatting, we can use the Excel Format Painter feature to instantly copy-paste the formatting. We can copy the formatting of cell for many things, such as font style, shape style, number formats, etc.

For example, we can use the Format Painter option to copy-paste the shape formatting, as shown in the image below.

What Is A Format Painter In Excel?

The Format Painter in Excel is a feature to copy the required format, such as cell size, font styles, etc., from a formatted cell to another cell. In other words, it is duplicating the formatting to the required cells.
For copying the format, the cells containing the format are to be selected first, and then we must click on the “Format Painter” button and select the cells we want to format.
When we double-click the “Format Painter” option the formatting remains active until we turn it off. We can turn it off by clicking the Esc key, or by clicking the Format Painter Tool in the Home tab once again.
We can also use the “Format Painter” in Excel from the paste specials options.

How To Use Format Painter In Excel?

We can use the Format Painter in Excel as follows:

First, choose the already formatted cell → select the “Home” tab → go to the “Clipboard” group → select the “Format Painter” option, as shown below.

We can use the Format Painter Feature to perform the following,

Formatting the Cells in Excel.
Formatting the Conditional Formatted Data.
Formatting the Shape of Object.
Format Painter on a Complete Worksheet.

Examples

We will consider some examples for the above-mentioned options.

Example #1 - Formatting the Cells in Excel

The steps to copy the formatting of the cell range to another cell range are as follows:

We must first select the formatted range.
Then, go to the “Home” tab, and click the option “Format Painter.”
Finally, select the range where we must apply the format. After the formatting is used, we will get the range formatted as an original.

Example #2 - Formatting the Conditional Formatted Data

The steps to copy the apply the conditional formatting from one range to another range of cells are,

Step 1: We must select the range of data with already applied Conditional Formatting, and click “Format Painter.”

The steps will be the same as explained above, but the only difference is that we also copy the conditional formatting of the cells in this case.

Example #3 - Formatting the Shape of Object

The steps to format the shape of an object are as follows:

Step 1: First, we must insert the shape by going to the “INSERT” tab and choosing the required shape.

Step 2: Now, select the existing formatted shape we need, and click “Format Painter.”

Step 3: Now, choose the shape that needs to be formatted in excel, and it will copy all the formatting onto the shape, as shown below.

Example #4- Format Painter on a Complete Worksheet

The steps to format the complete workbook instead of a single cell or range are as follows:

Step 1: We must first select the complete workbook already formatted or has the required properties and then click on the “Format Painter” option.

Step 2: Select the worksheet that has to be formatted. Then, it will be automatically applied.

Important Things To Note

While using the “Format Painter”, we should remember that it will apply all the properties/format of one cell to the new location. Therefore, we do not have the option of choosing what format has to be used.
If we are formatting a complete worksheet using a “Format Painter,” we should remember that both the sheets should have the same data in the same location. For example, if the first row has 10 headers and another sheet has only 9 headers, all 10 headers will be formatted. So, we will have one additional header formatted but no data.

Frequently Asked Questions (FAQs)

1. What are the various copy-paste features of Format Painter in Excel?

We often copy the cell values. However, we can copy only the cell’s formatting without copying the cell’s value from one cell to another cell with the help of a “Format Painter” tool.

• Using a “Format Painter” tool, we can copy a cell or multiple cell’s look and paste that into other cells or ranges.
• It is the built-in tool of Excel that belongs to the formatting class of functions. We can use this tool to copy the border, font style, and color, adjusting the cell’s width and all the interiors of the cells to another cell that is not formatted or formatted in a way that is not desired.
• The great thing about the “Format Painter” is that it is not restricted to a single cell. We can use it on the complete worksheet to get it formatted like the other worksheet.
• The worksheet format may be a border on the header, different colors on negative numbers, etc. If we have already created a workbook in the required format, we can always use the “Format Painter” tool and make the raw workbook the formatted workbook. So, now we do not need to spend time formatting a workbook again and again.

2. What are the different ways to use Format Painter in Excel on a cell?

We can use the Format Painter in Excel on a cell using 2 different ways, namely:

• The first way, we will copy the formatted cell’s formatting and then paste or apply it to another cell value.
• The second way, we will copy the formatted cell’s formatting to an empty cell and the enter cell value. We will see that the formatting will automatically apply to the cell values entered later.

3. How can we use Format Painter to apply formatting for more than one location?

After we have formatted the new location, the option of formatting the painter will disappear. To format more than one location, we need to double-click on the “Format Painter” option, which will keep the function active until we turn it off.