What Is A Format Painter In Google Sheets?
Format Painter in Google Sheets, also known as the Paint Format, 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 lose the formatting. Instead of redoing the formatting, we can use the Google Sheets Format Painter to instantly copy-paste the formatting. We can copy the formatting features, such as font style, shape style, number formats, etc. For example, we have random values in cells A1:B2, where cells A1:A2 are formatted with bold, italics and fill-color.
Select cells A1:A2, select the “Paint Format” tool and paste it on cells B1:B2, as shown below.
We see the output above where the formatting changed however the values are the same. Therefore, the Paint Format tool helps to copy-paste the cell formatting.
Table of contents
Key Takeaways
- The Format Painter in Google Sheets or the Paint Format in Google Sheets is a feature that copies the required format, such as cell size, font styles, etc., from a formatted cell to another unformatted cell. In other words, it duplicates the formatting to the required cells.
- To copy the format, we first select the cells with the format, then, we click the “Paint Format” tool and select the cells to format.
- To format multiple cells or different cells not in the cell range, we must click the “Paint Format” tool once, apply to a cell range, go to the other cell to format and press the shortcut key “Ctrl+Y” to format the selected cells.
- We can also use the “Format Painter” in Google Sheets from the paste specials options.
How To Use Format Painter In Google Sheets?
We can use the Format Painter in Google Sheets as follows:
First, select the formatted cell or cells, click the “Paint Format” tool from the toolbar and paste it on the non-formatted cells, as shown below.
We can use the Format Painter Feature to perform the following,
- Copy-Paste Color To Multiple Cells in Google Sheets.
- Formatting the Conditional Formatted Data.
- Copy font characteristics,
- Format Painter on a Complete Worksheet.
Examples
We will consider specific examples for the above-mentioned options.
Example #1 – Copy and Paste Color to Multiple Cells
We have the data given below where we have Calculated Age using Google Sheets using the start and the end dates. However, only one of the output cells, i.e. cell C2 is highlighted in yellow color. We must use the Format Painter in Google Sheets to format the others cells too, i.e. cells C3:C7.
The procedure to format Copy and Paste Color to Multiple Cells using the Paint Format is,
First, select cell C3 and click the “Paint Format” tool on the toolbar, as shown below.
Next, paste the copied format to the cells C3:C7.
The format color is copy-pasted to multiple cells.
Example #2 – Copy font characteristics
We have the data given below of the periodic payment of a customer in cells A1:B7. We have calculated the PPMT value as well. The data has font characteristics such as BOLD, ITALICS, FILL-COLOR for headers and result cell, center-aligned, etc. as shown below.
In cells E1:F11, the data of another customer to format as cells A1:B7.
The procedure to copy font characteristics using the Paint Format are as follows:
First, select cells A1:B7 and click the “Paint Format” tool on the toolbar, as shown below.
Next, paste the copied format to the cells E1:F7.
The font format is copy-pasted to the new cell range.
Example #3 – Formatting the Conditional Formatted Data
We have the data below where we have used Conditional Formatting in Google Sheets to highlight the blank cells.
The steps to perform Formatting the Conditional Formatted Data are as follows:
Step 1: We will try to change the color of one of the conditional formatted data, here, cell D2.
Therefore, select cell D2, click the “Fill Color” tool and select the “Yellow” color. As we see below there are no changes happening as the cell is conditional formatted.
Step 2: In such scenarios we can use the “Paint Format” tool to format the formatted cells. So, let us select any cell, here cell F4, click the “Fill Color” tool and select the “Yellow” color. We can see that the selected cell is now yellow in color.
Step 3: Now, select cell F4 and click the “Paint Format” tool on the toolbar, as shown below.
Step 4: Finally, paste the copied format to the cell D2.
We performed the Formatting the Conditional Formatted Data.
Example #4 – Format Painter on a Complete Worksheet
We will consider the formatted data of Example 2 worksheet and apply the formatting to Example 4 worksheet. The below data is of Example 2 worksheet.
Let us see the Example 4 worksheet which is empty, as shown below.
The steps to Format Painter on a Complete Worksheet using the “Paint Format” tool are,
1: First, select the complete workbook that is already formatted, here Example 2 worksheet, by clicking the small box-like space where the row-numbers and the column-letters start, as shown below.
2: Next, click the “Paint Format” option, as shown below.
3: Select the worksheet to format, here Example 4 worksheet, and click the same small box-like space. Then, the format is pasted, as shown below,
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.
a. Using a “Format Painter” or the “Paint Format” tool, we can copy a cell or multiple cell’s format and paste that into other cells or ranges.
b. Since it is a built-in tool, 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.
c. The advantage of this tool is that it is not restricted to a single cell. We can use it on multiple cells, a cell range or the complete worksheet to format like the other worksheet.
d. 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, that helps us save time in formatting a workbook all over again.
We can use the Format Painter in Google Sheets 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.
In Excel, 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.
However, in Google Sheets the double-click option does not work like Excel. Therefore, we can click the “Paint Format” tool once and apply to multiple cells or a cell range, and to format again go to the respective cell to format and press the shortcut key “Ctrl+Y” to format the selected cells.
Download Template
This article must help understand Format Painter in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to Format Painter In Google Sheets. We use the Paint Format tool to copy paste fonts, conditional format, and colors with examples & work template. You can learn more from the following articles –
Leave a Reply