Format Painter in Excel: Copy Formatting to One Cell, Many Cells, or a Whole Sheet

Quick answer

Select a cell that already looks right, click Format Painter on the Home tab, then click or drag over the cells that should match. That copies number format, font, fill, borders, alignment and conditional formatting in one go — never the values. Double-click the button instead of single-clicking and the brush stays active until you press Esc, so you can paint a dozen scattered ranges without going back to the ribbon. If you would rather stay on the keyboard, copy the source cell, then press Ctrl+Alt+V and choose Formats.

Format Painter is the fastest way to make a sheet consistent, and most people use about a third of it. The single-click case is obvious. The double-click, the whole-column paint and the keyboard route are what turn a five-minute tidy-up into thirty seconds.

What Format Painter actually copies

Everything in the Format Cells dialog, plus conditional formatting rules: number format, font and size, bold or italic, text and fill colour, borders, alignment and wrap, indent, and cell protection flags. What it never copies is the content — the value or formula in the source cell stays where it is. That is why it is safe to paint a formatted total row over a block of raw formulas: the formulas survive, they just start looking like the total row.

Two things that surprise people. Column width is copied only when the source selection is an entire column (click the column letter first). And if the source cell carries a conditional-formatting rule with relative references, the rule is copied with those references shifted, exactly as a formula would be — which is either precisely what you want or the reason a rule stopped firing.

One click, one target

1. Select the cell (or range) whose formatting you want to reuse.
2. On the Home tab, in the Clipboard group, click Format Painter. The cursor gains a small paintbrush.
3. Click a single cell to format just that cell, or drag across a range to format all of it.

The brush switches off automatically after one use. If your source was a range rather than a single cell, Excel repeats the source pattern across the target — a two-cell source with alternating fills will tile that alternation across a twenty-cell target.

Double-click to keep painting

This is the feature worth learning. Microsoft’s own instruction: “To change the format of multiple selections in your document, you must first double-click Format Painter.”¹ The brush now stays active through as many clicks and drags as you like — different sheets included. “To stop formatting, press ESC.”¹ Clicking the Format Painter button again also switches it off.

Typical use: one header cell is formatted the way you want; every other header on five sheets should match. Double-click once, click each header, Esc. No copy-paste, no undo surprises.

Whole columns, rows and sheets

To copy the look of a column — including its width — click the column letter to select the entire column before you pick up the brush, then click the letter of the column you want to change. Rows work the same way with row numbers. To restyle an entire sheet from one formatted sheet, select all (click the corner box above row 1, or Ctrl+A twice), pick up the brush, switch sheets and click the same corner box.

The keyboard route

Read carefully

Microsoft’s Format Painter help page lists Alt+Ctrl+C to copy a format and Alt+Ctrl+V to paste one¹ — but that page covers Word, PowerPoint and Outlook as well as Excel, and those two shortcuts belong to Word. In Excel, Ctrl+Alt+V opens the Paste Special dialog². The Excel sequence is therefore: copy the source cell with Ctrl+C, select the target, press Ctrl+Alt+V, press T for Formats, Enter.

Paste Special → Formats does the same job as Format Painter with one useful difference: it works across workbooks and survives you doing something else in between, because the source stays on the clipboard. The same dialog is where you multiply a whole range by one value without a helper column.

Three things that go wrong

The painted cells look right but the conditional formatting does not fire. The rule came across with its references shifted. Open Conditional Formatting → Manage Rules on a painted cell and check what the formula now points at. If the rule should look at a fixed range, anchor it with dollar signs in the source before painting.

A merged cell unmerged, or a normal cell merged. Merge state is part of formatting, so painting from a merged source merges the target. Painting from an unmerged source onto a merged cell splits it. Undo (Ctrl+Z) reverses it; better still, avoid merged cells in anything that will be sorted or filtered.

The number format hides the value. If the source cell uses a custom number format such as the three-semicolon trick that hides values in a column, every painted cell inherits it and appears blank. The values are still there; the format is doing what it was told.

On the exam

Formatting cells and ranges sits inside the “Manage data cells and ranges” objective of exam MO-210, weighted at 25–30% of the Excel Associate exam³. In a timed, hands-on exam the double-click brush is the difference between formatting a block in five seconds and in forty. See the MO-210 exam guide for the full objective list.

Related

• Hide values in a column with a custom number format
• Copy only visible cells — the other Paste Special habit worth having
• Is an Excel certification worth it?
• Excel certifications compared

Sources