How to Use the Format Painter Excel Feature? — 3 Bonus Tips

Note: This guide on how to use the Format Painter Excel feature is suitable for all Excel versions including Office 365. 

In this tutorial, I’ll walk you through how to easily use the Excel format painter feature.

You’ll learn:

Related:

How to Superscript in Excel? (9 Best Methods)

How to Indent in Excel? 3 Easy Methods

How to Enable Excel Dark Mode? 3 Simple Steps

What is the Format Painter Excel Feature?

It is always a good practice to format your Excel sheet before sharing it with others. This improves not only the aesthetics but also the usability of your worksheet. 

Though formatting tasks like applying borders, headers, alignment, etc. look simple, they can become very daunting if you have to do them repeatedly. 

Let us say, for example, you have spent a decent amount of time carefully editing and formatting an Excel spreadsheet. What if you come across another spreadsheet that requires the same formatting to be applied? Do you begin all over from scratch?  

The format painter in Excel solves this very problem by quickly copying the previous formatting to your new spreadsheet. This will save you a lot of time and effort when working with multiple worksheets. 

The format painter copies:

1. Font size, colour, font type, etc.
2. Text Alignment ( Left, right & centre)
3. Cell borders
4. Cell Background
5. Font Style ( Bold, Italics, Underline, etc.)
6. Number format type ( Number, currency, accounting, text, etc.)
7. Conditional Formatting. 

Without further ado, I’ll show you how to easily use the format painter in Excel. 

How to Use the Format Painter in Excel? 

To use the format painter to copy formatting follow these simple steps: 

1. Select the cell or range of cells whose formatting you want to copy. 

2. Click on the Format Painter button in the Clipboard section of the Home tab. 

3. Go to the destination cell or range of cells where you want to apply the formatting and click on it. 

Excel will instantly apply the same formatting of your selected cell to the destination cell range. 

Please keep in mind that it will not copy original values with the formatting. 

You can use it to copy formatting within a worksheet, between worksheets, and between workbooks. In the following section, I’ll show you some interesting examples. 

Also Read:

How to Add Error Bars in Excel? 7 Best Methods

Easily Make a Bullet Chart in Excel—2 Examples

How to Shade Every Other Row in Excel? (5 Best Methods)

Copy Conditional Formatting Using the Format Painter in Excel  

This is one of the best use cases for the format painter. Conditional formatting requires a lot of work while setting up. Doing it again from scratch is a waste of time. 

You can instantly apply conditional formatting to other cells, worksheets, or workbooks simply by copying it using the format painter option. 

What’s more interesting? You can copy conditional formatting from one workbook to another. 

This will immensely help you save your precious time. So keep this in mind the next time you use conditional formatting. 

Duplicate Shape Formatting Using the Excel Format Painter

The format painter does not only apply to normal cells. It also works with shapes in Excel. Let us say for example you have to add buttons for actions items in your workbook. For this, you need to create many rectangular boxes with text inside them. 

You can instantly copy the formatting from the first shape to other shapes using the format painter in Excel. 

Useful tips while using the Format Painter in Excel

These are some important things to remember while using the format painter. 

1. When you want to copy a formatting style over a range of cells, simply drag the cursor over the range of the destination cells after clicking on the Format Painter button. 

2. When you want to copy a formatting style over many separate ranges of cells, double click on the Format painter button. This will lock the format painter option. You can now apply the formatting to all the ranges, without clicking the format painter each time. 

Once you are done, press Esc or once again click on the Format Painter icon to unlock it. 

3. You can copy the formatting of entire rows or columns to other rows or columns simply by selecting the row or column headers while using the format painter. 

Suggested Reads:

How to Autofit Excel Cells? 3 Best Methods

The FORMULATEXT Excel Function – 2 Best Examples

How to Apply the Accounting Number Format in Excel? (3 Best methods)

FAQs

Closing Thoughts

In this short tutorial, I have covered all the important details about the format painter in Excel. You can start using these tips right away in your next Excel worksheet. 

You can find more high-quality Excel guides here. 

Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.