If your work involves applying the same type of formatting to data sets, using Format Painter in Excel will save you a lot of time.
What is Excel Format Painter?
Excel Format Painter is a nifty tool that allows you to copy formatting from a range of cells and paste it somewhere else in the worksheet (or other worksheets/workbooks). Imagine this! You get plain ugly data from a colleague, and you spend the next few minutes applying some formatting to make it beautiful. This may include applying borders, formatting the headers, adjusting column widths, removing gridlines, etc. And while you’re patting your back for turning ugly data into a beautiful one, you get another file that needs to be formatted the same way. This is where Excel Format Painter comes into the picture and saves you a lot of time. You can now quickly copy the formatting you had already done, and paste it into the new dataset. And woosh.. it will get transformed into the same formatted data where you did everything manually.
How to Use Format Painter in Excel?
Let’s understand Format Painter in Excel using a simple example. Suppose I have a cell (B2 as shown below) and I want to copy the formatting from it to cell D2.
Here are the steps to copy formatting using Format Painter in Excel:
Select the cell(s) from which you want to copy the formatting. Go to the Home Tab –> Clipboard –> Format Painter. Select the cell where you want to copy the formatting.
Instantly, the formatting from cell B2 would be copied to cell D2 (as shown below).
Note that when you click on the Format Painter icon in the ribbon, the cursor changes and you can see a brush in it. This indicates that the format painter is active. Here is what all is copied by format painter in this above example:
Cell Color Number formatting (note the $ sign and the two decimals). Border.
Format Painter only copies the formatting and not the value in the cell. You can use format painter in Excel to:
Copy formatting from the same worksheet. Copy formatting to some other worksheet in the same workbook. Copy formatting to some other workbook.
Using Format Painter in Excel – Examples
Let’s dive in to see some examples of using Format Painter in Excel.
Example 1 – Adding formatting for an Extended Dataset
Suppose you have a dataset with existing formatting and you add a new column to this data set. You can use format painter to instantly apply the same formatting that is used in the existing data set (as shown below).
Example 2 – Copying Conditional Formatting using Format Painter
One of my favorite uses of Format Painter is to copy conditional formatting. Since conditional formatting allows you to specify multiple rules on the same data set, doing it again for different data sets could be time-consuming. For example, suppose you have a dataset as shown below, where student’s marks are highlighted in red if it is less than 35 and in green if more than 80.
Now, if I add a new column with marks in a new subject (Physics), instead of applying the conditional formatting again, I can simply use the Format Painter to copy the cell formatting as well as conditional formatting rules.
Example 3 – Copying Shape Formatting
Using Format Painter, you can also quickly copy formatting from shapes and paste it to other shapes. Here are the steps to copy formatting from the shape and paste it to another using format painter in Excel:
Keeping the Format Painter Active (to reuse it multiple times)
In some cases, you may want to copy the formatting from a range of cells and paste it to a non-contiguous range of cells. These could be on the same worksheet or different worksheets/workbooks. When you click on the Format Painter icon in the Home tab, it allows you to copy and paste the formatting only once. To copy and paste the formatting multiple times, you need to double-click on the Format Painter icon. This will allow you to copy from a range of cells and paste that formatting multiple times (until you disable the Format Painter).
To disable it, simply click on the Format Painter icon again or hit the Escape key.
Using Conditional Formatting in Excel (The Ultimate Guide + Examples). Excel AutoFormat How to Remove Cell Formatting in Excel [Quick Tip] How to Apply Superscript and Subscript Format in Excel. How to Calculate and Format Percentages in Excel. How to Quickly Transpose Data in Excel. 100+ Excel Interview Questions + Answers. How to Remove Dotted Lines in Excel Best Shortcuts to Fill Color in Excel
