Format Painter In Excel

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

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.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Format Painter In Excel (wallstreetmojo.com)

Key Takeaways

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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #1 – Formatting the Cells in Excel

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

  1. We must first select the formatted range.


    Format Painter Example 1

  2. Then, go to the “Home” tab, and click the option “Format Painter.”


    Format Painter Example 1-1

  3. Finally, select the range where we must apply the format. After the formatting is used, we will get the range formatted as an original.


    Format Painter Example 1-2

Example #2 – Formatting the Conditional Formatted Data

The steps to copy the apply the conditional formattingApply The Conditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more 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.”

Format Painter Example 2

The steps will be the same as explained above, but the only difference is that we also copy the conditional formatting of the cellsConditional Formatting Of The CellsConditional formatting for blank cells can be accessed from the new rules of conditional formatting tab and then in the cells that contain blank value which is the second option. In other words it means a certain cell which doesn’t have a value assigned to it. It doesn’t have a numeric, alphabetic or any sort of character as value. It is just blank.read more in this case.

Format Painter Example 2-1

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.

Example 3

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

Example 3-1

Step 3: Now, choose the shape that needs to be formatted in excelFormatted In ExcelFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more, and it will copy all the formatting onto the shape, as shown below.

Example 3-2

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.

Example 4

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

Example 4-1

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.

Download Template

This article must help understand Format Painter in Excel with its formulas and examples. You can download the template here to use it instantly.

You can download this Format Painter Excel Template here – Format Painter Excel Template

This article is a guide to Format Painter in Excel. Here we use Format Painter on cells, Shape, formatted data, worksheet, example, downloadable excel template. You may learn more about Excel from the following articles: –

Reader Interactions

Leave a Reply

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