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!
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
