What is a Format Painter in Excel?

We often copy the content of cells but have ever wondered how we can copy only the cell’s formatting without copying the cell’s value. Yes, it is possible to copy and paste only the formatting of one cell to another cell with the help of a “Format Painter” tool.

  • Using a “Format Painter” tool, we can copy one cell’s look or the complete range of the cells and paste that into other cells or ranges.The “Format Painter” 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 its use is not restricted to a single cell. Therefore, we can use the “Format painter” on the complete workbook to get it formatted like the other workbook.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 with the help of a “Format Painter.”

So, now we do not need to spend time formatting a workbook again and again as we have the “Format Painter” tool in Excel.

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

How to Use Format Painter in Excel?

Example #1 – Formatting the Cells in Excel

Example #2 – Formatting the Conditional Formatted Data

In this method, we can use the “Format Painter” to 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.

  • We must first select the formatted range. Then, go to the “Home” tab. From the “Home” tab, there will be the option “Format Painter.” Click on this option and then select the range where we must apply the format. After the formatting is used, we will get the range formatted as an original.

Click on this option and then select the range where we must apply the format. After the formatting is used, we will get the range formatted as an original.

Step 1: We must select the range of data and click on “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 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.

Example #3 – Formatting the Shape of Object

In this method, we will format the object.

Step 1: First, we must insert the shape.

We can insert a shape by going to the “INSERT” tab and choosing the required shape.

Step 2: Now, select the shape with all the formatting we need and click on “Format Painter.”

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.

Example #4- Format Painter on a Complete Worksheet

In this case, we will format the complete workbook instead of a single cell or range.

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 wants to be formatted. Then, it will be automatically applied.

Things to Remember

  • 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.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, as this will keep the function alive until we choose to turn it off by pressing the key “ESC.”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.We can also use the “Format Painter” in Excel from the paste specials optionsPaste Specials OptionsPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more.

This article is a guide to the Format Painter in Excel. We discuss using the “Format Painter” tool on cells, the object’s shape, conditional formatting with practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • VBA Conditional FormattingCustom Number Format in ExcelPivot Table Conditional FormattingAutoFormat Excel