Mastering the Format Painter

Mastering the Format Painter

The Format Painter is an essential tool for anyone looking to save time and achieve greater consistency throughout their worksheets’ formatting.

It’s a pretty simple tool, considering its power. And while I could just say “Click it and go!” there are a few things to keep in mind when you use it.

As I’m demonstrating here on my DEMO sheet, it’s great for taking the formatting from one cell – the sample cell – and applying it to one or more cells.

With a single click, you can paint the formatting from the sample to one cell, or to a range of contiguous cells. Note that the sample cell has a flashing dashed border around it – just like as if we’d used the Copy command – because it is copying. It’s just copying the formatting rather than the content.

If you want to apply it to several non-contiguous cells or ranges, you’ll need to select your sample cell and then double-click the Format Painter – and then turn it off when you’re finished using it. You can turn it off by clicking it once or pressing the Esc key.

It also works on graphic elements, so if you have a shapes on your worksheet, even if they have text in them, you can apply formatting from one shape to another.

Some other things to consider as you use the Format Painter:

Everything in terms of formatting applied to the sample cell will be painted to the target cells. That includes fonts, font sizes, fill colors, borders, and number formatting.

As shown here on the Widget Vendor and Product sheet, if I use it to paint the formatting from cell E9 to E22, the border that came along for the ride isn’t useful in the target cell.

Of course, if the format painter brings several attributes with it that you want to keep, removing one still makes it a labor-saving device, and consistency is still achieved. When I select the target cell and remove the borders by clicking No Borders from the Border tool, all is well. I still get the number formatting, bolding, and color.

And speaking of removing formatting, switching to the DEMO sheet, you can use the Format Painter for that, too – if you want to remove all formatting from a cell or range, click in a clean, unformatted cell and then turn on the Format Painter. Drag through the cell or cells you wish to “clean, ” and voila, blank formatting slate accomplished.

And one last tip. You can use the Format Painter to apply a series of formats from a single row or column to another row or column.

Here, back in the Widget Vendor and Product sheet, where Row 9 contains a variety of entries – there’s text as well as numbers, and that includes currency and numbers where decimal places have been adjusted, and it includes bolding, borders, and colors, too. And here’s the tip – I can use that row as a sample and apply the formatting, cell for cell, to rows 15 and 20 to make them match.

As long as the sample and targets are the same configuration in terms of the number of cells and their placement within the range, it works!