How to use the Format Painter in Excel
to Copy Formatting – in 2 Clicks!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Cell formatting is very useful.

It makes you spreadsheet user-friendly and awesome to look at!

In addition to making your spreadsheet nicer to look at, you can conditionally format cells, use formats in your searches, and even filter by format.

These features are great…

… but sometimes you just want to get a lot of cells formatted quickly, and they can’t help you there šŸ™

What you need is theĀ format painter! šŸ™‚

The format painter is exactly what it sounds likeā€”you use a paintbrush-like tool to format a bunch of cells at once.

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

To help you learn how to use the format painter, we’ve created an example workbook for you.

Download it for free below so you can follow along!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to use the format painter

The format painter is extremely simple. Open up the example spreadsheet to see how it works.

First, select the cell that contains the formatting you’d like to apply to other cells. In our case, that’s cell A1.

select-cell-format

Now, click theĀ Format Painter button in theĀ Home tab of the Ribbon.

format-painter-button

You’ll see the selected cell highlighted with a moving border, and your cursor change to a cross with a paintbrush.

format-painter-highlight

At this point, you can use the painter. Select any cells that you’d like to receive the same formattingā€”without lifting your finger from the mouse button.

When you let go, those cells will be formatted in the manner of the cell you selected. In our case, the selected cells become bold and get a blue background:

format-painter

It’s not just color and text styles that you can use, though. The format painter works with any formatting you can think of!

Kasper Langmann, Co-founder of Spreadsheeto

Here’s the format painter changing the typeface and color:

format-painter-font

Here, the typeface remains the same, but the size changes:

format-painter-size

You can even change the cell format with the format painter.

Below, you’ll see that I’ve used the format painter to change the cells in the two right-hand columns from General to Text (you can tell by their changing alignment):

format-painter-cell-format

Pro tips on format painting

While the format painter is pretty straightforward, there are a few things you can do to make it even more useful.

You probably noticed that you can’t use the format painter onĀ non-adjacent cells, because it stop formatting as soon as you let go of the mouse button. You can solve this problem byĀ double-clicking the Format Painter button.

Once you’ve done this, every cell you click on will receive the same formatting.

format-painter-non-adjacent

Don’t forget toĀ press ESC when you’re done!

Kasper Langmann, Co-founder of Spreadsheeto

Another cool trick you can do isĀ paint the formatting from an entire column or row.

Instead of selecting a single cell before clickingĀ Format Painter, select the column you’d like to serve as the basis of your formatting.

Then click theĀ Format Painter button and click on the column you’d like to receive the formatting.

(If you want to try it yourself, open the second sheet in the example workbook.)

format-painter-column

Note that the cell you click on is where the format painter will start. In the example above, I clicked on the first cell in the second column, so it matched the formatting of the first column exactly.

If I had clicked a few cells down, the sequence would have started where I clicked.

You can also use this same method for painting a format for an entire row. Just select the row before clickingĀ Format Painter.

Kasper Langmann, Co-founder of Spreadsheeto

Start saving time with
the format painter

If you do a lot of formatting in your spreadsheets, the format painter can save you a lot of time. Just format a few cells, then paint them to the rest of your spreadsheet.

It doesn’t get much simpler than that!

Now that you’ve seen how it works, play around with the format painter in the example workbook. You might find even more great uses for it!

Kasper Langmann, Co-founder of Spreadsheeto
2019-10-16T13:01:15+00:00