How to use the format painter

If you use Excel frequently, you'll find that you do a lot of formatting. In this video I'll show you how to use a tool called the Format Painter to speed up manual formatting.

As you work in Excel, you'll often spend a considerable amount of time formatting data to get it to look good. And although formatting tools in Excel are easy to use, applying formatting manually can quickly get tedious.

One tool you can use to speed things up is the Format Painter. The Format Painter copies formatting from one group of cells to another.

In this example, I have a table that has a variety of custom formats applied: the header has a color fill and a white font; the movie name is in italics; year and rank are centered; the movie length is formatted as a time; and all the data cells have a gray border.

To the right, I have another table with the same kind of data, and no formatting. The question is, how can I quickly format the second table to match the first?

This is a perfect job for the Format Painter. First, select the cells with the formatting you want to copy. Next, click the Format Painter tool on the home tab of the ribbon. You'll see the cursor change to a small paint brush.

Now click the upper left cell in the group of cells you'd like to format. In one step, the Format Painter applies all formatting.

You can also use the Format Painter with small groups of cells. To illustrate, I'll click the Format Painter with just one cell selected. Now I can drag across the header in the second table and apply the same formatting.

Let's undo that and try working with columns. A single click will apply formatting to the same number of cells that were originally selected. If I select more cells, before clicking the Format Painter, the same number of cells will be formatted when I click.

Normally, the Format Painter stops working after you click once. However, you can lock the cursor in Format Painter mode by double-clicking the tool. You can then keep working with the same format. Press the Escape key to exit the Format Painter.

Finally, you can use the Format Painter to apply formatting to an entire worksheet, so long as both worksheets have the same structure and layout.

Click the origin cell in the upper left of the source worksheet to select all cells, then click the Format Painter. Switch to the second worksheet, and click the origin. All cells will be formatted to match the first worksheet.

The Format Painter can save you a lot of time in the right situation, so be on the lookout for opportunities to use it!