What is Format painter in Excel
Format Painter is a tool in Excel that allows you to copy formatting from one cell or range of cells and apply it to another cell or range of cells. This can save you time when you want to apply the same formatting.
For example, if you have a cell with bold text and a red background colour, you can use Format Painter to copy that formatting and apply it to other cells or ranges of cells.
In Excel, the Format Painter Icon is found on the Home ribbon, near the left hand side as below
Format Painter also work across a range of other Microsoft Office Products such as Word, PowerPoint and also Power BI. This post explains it in the context of Excel
How to use Format Painter in Excel
Let’s assume a scenario to demonstrate how this can save a great deal of time
We have a nicely formatted table that someone has spent time getting it to look just right, with a mixture of format settings being applied
Example 1
Now we have another table, that has not yet been formatted and we want it to look exactly the same style as the above table
Example 2
In this example the values are the same in both tables, but they could be different and the format would still apply
Step by step instructions on how to use Excel Format painter
- Select the range you want to copy the format from
- Click the format painter icon
- Select the range you want to copy the format to, whilst keeping the left mouse button pressed
- Let go of the mouse button and all of the formats are applied in one go
See it in action
Using Format Painter with Partial Ranges
If you need to select only parts of a range to copy the format from, you can do that too
What is the difference between Format Painter and Paste Special?
Format Painter and Paste Special are two tools in Excel that allow you to copy formatting from one cell or range of cells and apply it to another cell or range of cells.
Both of these tools can achieve the same results, but there are some differences
Format Painter is the quickest way to copy exact formatting options from one area to another. It copies all of the formatting from the source area, such as font size, colour of both font and fill colours, any borders, conditional formats and more
Paste Special, gives you more options to copy specific formatting options from one area to another.
Conclusion
Now you know how to use format painter in Excel
Format painter in Excel is a really efficient way to copy the look and feel from one part of a spreadsheet to another, once you have mastered it you will save countless hours
You may be interested in this post
How to create Pivot Tables in Excel
Subscribe to our channel to see more tips and timesavers
Select Distinct YouTube Channel
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.
Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
