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.
Table of contents
- 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 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..
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.
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 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. 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 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. 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 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., 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)
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.
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.
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.
This article must help understand Format Painter in Excel with its formulas and examples. You can download the template here to use it instantly.
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: –