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
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.
Now, click the Format Painter button in the Home tab of the Ribbon.
You’ll see the selected cell highlighted with a moving border, and your cursor change to a cross with a paintbrush.
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:
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 SpreadsheetoHere’s the format painter changing the typeface and color:
Here, the typeface remains the same, but the size changes:
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):
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.
Don’t forget to press ESC when you’re done!
Kasper Langmann, Co-founder of SpreadsheetoAnother 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.)
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 SpreadsheetoStart 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