Format Painter In Excel

Publication Date :

07 Mar, 2019

Blog Author :

Edited by :

Reviewed by :

Download FREE Format Painter In Excel Template and Follow Along!

Format Painter Excel Template.xlsx

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.

Format Painter In Excel — *You are free to use this image on your website, templates, etc..* *Please provide us with an attribution link*

Key Takeaways

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.

How to Use Format Painter in Excel

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.