The final step in most spreadsheet preparation is making all your hard work look pretty and presentable for the people who will use it. Whether you are exporting charts and tables to a presentation program like PowerPoint, or building a summary worksheet to explain the data to your board of directors, formatting cells to make all the numbers and figures line up can be tedious and time consuming. Fortunately, Excel has an often overlooked feature that can save you tons of time when you are formatting your work! Here’s a quick how-to for using Format Painter…

What is Format Painter?

Format Painter is located in the Home menu tab right next to the main Paste menu. It has a little paintbrush icon that can be easy to miss if your window is sized too small. When full-screen, the paintbrush has a label that reads Format Painter next to it.

Format Painter works by taking all of the visual and formatting settings of one cell and copying them over to other cells. It can change most, if not all of the settings of the cell it’s applied to, including:

• number formats
• font face
• font size
• font modifiers like bold, underline, and italic
• font color
• text justification (left, right, center)
• cell background color
• cell borders

How to Use Format Painter

Here is a simple example. Let’s say you have a nicely formatted cell (here with the green background), and you want to copy its formatting to the un-formatted cell above it.

Start by selecting the cell with the formatting you want to copy. Click the Format Painter button.

You’ll see your cursor change to a paintbrush icon next to a white cross. Click on the cell that you want to change.

That’s it! The formatting in the cell you clicked will now be identical to the cell you chose with the Format Painter brush.

Format Painter Tips and Tricks

• When you click on the Format Painter brush and your cursor changes, you don’t have to change one cell at a time. When you click with the brush cursor, you can drag it over multiple rows and columns to change the format of an entire block of cells!
• If you need to change multiple cells that aren’t adjacent (next to each other in a block), you can double-click the Format Painter button when you choose the source cell. This will “lock” the Format Painter on, so you can change as many cells and cell ranges that you need! To turn off Format Painter, just click on the paintbrush one final time.
• Format Painter isn’t just for Excel! You can use it to speed up formatting in all Microsoft Office applications, including PowerPoint, Word, Outlook, and Publisher.