Format Painter in Excel: Copy Formatting Fast

Format Painter in Excel copies the formatting of one cell, such as its font, colours, borders and number format, and applies it to other cells in one click. The values in the target cells don't change. This guide shows how to use it once or many times, how to copy column widths, the keyboard alternatives on Windows and Mac, and the mistakes to watch for.

What Format Painter copies#

Format Painter is the paintbrush icon in the Clipboard group on the Home tab. It copies everything about how a cell looks, and nothing about what it contains.

That makes it the fastest way to make a new section of a report match an existing one.

How to use Format Painter once#

Single-click Format Painter to copy formatting to one place:

1. Click the cell that has the formatting you want, for example a header in A1.
2. On the Home tab, click Format Painter. The pointer changes to a paintbrush.
3. Click the target cell, or drag across a range, for example D1:F1.

The formatting is applied and Format Painter turns off. If you select several source cells with different formats, such as a bold header and a plain number below it, Excel repeats that pattern over the target range.

How to use Format Painter many times#

Double-click the Format Painter button and it stays on. You can then paint as many cells or ranges as you like, even on other sheets.

1. Click the formatted cell.
2. Double-click Format Painter.
3. Click or drag over each target, one after another.
4. Press Esc, or click Format Painter again, to turn it off.

This is the trick most people don't know, and it saves a lot of clicks when you format a long report. It works between workbooks too: click Format Painter in one file, switch windows, and paint in the other.

How to copy column widths and row heights#

Format Painter only copies column widths when you copy a whole column. Click the column letter of the source (for example B), click Format Painter, then click the target column letter. The target column gets the same width and the same formatting. Whole rows work the same way for row height.

If you only want the width, copy the source cell, then use Paste Special > Column widths. That leaves the target's formatting alone.

Keyboard alternatives: Paste Special Formats#

Format Painter has no single built-in shortcut, but you have two keyboard routes.

On Windows, press Alt, then H, F, P one after another. These are the ribbon key tips for Home > Format Painter.

The second route works on both systems and is easier to repeat:

After a normal paste, you can also click the Paste Options button that appears and choose Formatting. The Excel keyboard shortcuts cheat sheet lists more time-savers like these.

Format Painter vs number formats and TEXT#

Format Painter is great for copying a look, but it's worth knowing what that look really is. A number format only changes how a number is shown. A price of 1234.5 can appear as $1,234.50, and the cell still holds 1234.5, so formulas keep working. The guide to formatting numbers as currency and percent covers the built-in formats, and custom number formats show how to build your own.

Sometimes you need the formatted version as text, for example in a label. That's what the TEXT function does:

=TEXT(B2,"$#,##0.00")

With 1234.5 in B2, the result is the text $1,234.50. Unlike Format Painter, TEXT creates a new value, and you can't do maths on it. Use Format Painter for the look of a cell and TEXT for labels and sentences.

Dates work the same way. If Format Painter gives a date the wrong look, see how to change the date format.

Common Format Painter mistakes#

Format Painter is simple, but a few things trip people up:

• It replaces all formatting. If the target had borders or a fill you wanted to keep, they're gone. Press Ctrl+Z (Mac: ⌘Z) right away to undo.
• It copies conditional formatting. Painting a cell with rules onto other cells adds rules for them too. Over time this creates many duplicate rules. Check Home > Conditional Formatting > Manage Rules.
• It copies merged cells. Painting a merged header onto a range merges the target cells too, which can cause trouble with sorting and filtering.
• It doesn't change the value type. Painting a number format onto text that looks like numbers won't turn it into real numbers.

If a range ends up with a mix of formats you no longer want, start again from a clean slate. Select the cells and choose Home > Clear > Clear Formats (the eraser icon in the Editing group). This removes every format, including number formats and conditional formatting, but keeps the values and formulas. Then paint the correct format back on with Format Painter.

Before you print, a quick pass with Format Painter makes headers and totals consistent; the print setup guide covers the rest.

Practise it#

Practise formatting shortcuts in a live spreadsheet: you'll apply number, currency and percent formats from the keyboard and see each change checked instantly. Then practise copy, cut and paste, including Paste Special, so copying formats is second nature. Try the TEXT exercise on this page first.