Complete Guide to Use Format Painter in Excel

When working in Excel, copying and pasting cells is a pretty common task. However, what if you only want the formatting of a cell and not the content itself?

If you come across such a situation, you can consider using the format painter tool. While there are similar options in Excel like the Paste Special, the format painter has some added advantages.

Along with copying basic cell formatting like font and background properties, it can even copy conditional formatting. Also, you can lock the Format painter, after which you can keep using it to apply formatting in multiple places with a few mouse clicks.

How to Copy and Paste the Formatting of a Cell/Cell Range?

Using the format painter, you can choose to paste formatting for a single cell or apply it to the selected cell range.

Once you paste the formatting, the Format Painter deactivates by default. However, you can lock it to use it multiple times on cells spread across your worksheet.

1. Choose the cell (s) whose formatting you want to copy. Here we are trying to apply cell formatting under the Source column to the cell range under the Destination column.
   
2. Click Format Painter under the Home tab.
3. Once the paintbrush icon appears, click and drag across the cell (s) where you want to apply the formatting.
   
4. If you want to use the Format Painter more than once, double-click the Format Painter icon. And, press the Esc key to deactivate it.
   
5. If you want to apply cell formatting across another worksheet, right-click in the bottom left corner of the tabs section.
   
6. Then, navigate to the worksheet and apply the formatting to the desired cell/cell range.
   

While using the format painter, one important thing to note is that it completely replaces the formatting of the destination cells to match the formatting of the source cells.

For instance, 

In the above image, cells under the “Destination cells” column already have bold and underline formatting prior to using the format painter. However, after using the format painter to apply formatting on the cells under the “Destination cells” column, the bold and underline formatting were completely replaced by the green fill.

How to Copy Conditional Formatting with the Format Painter?

When you use the format painter on a cell or cell range, it will also apply the same conditional formatting rules on the destination cells.

For instance, in the above image, we have applied a red color to marks below 40 and green to marks above 40 for the Math subject.

Now, to copy this conditional formatting, you can simply select the cells under the Math column and apply the Format painter to cells in the Science column as mentioned above. After using it on cells under the Science column, they also changed color accordingly as follows.

Related Questions

How to Remove Formatting Applied with the Format Painter?

If you accidentally formatted the wrong cell (s) using the Format painter, you can clear the formatting as follows. However, this will remove all the cell formatting, including the ones applied before using the Format Painter.

1. Select the cell/cell range for which you want to remove formatting.
2. Go to the Home tab.
3. Click the Clear icon and select the Clear Formats option.