Excel guide · 4 min read
How to use Format Painter in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 |
| 2 | North | 48200 | 51300 | 49800 |
| 3 | South | 39600 | 42100 | 44750 |
| 4 | East | 55100 | 41200 | 58300 |
| 5 | West | 31900 | 33400 | 36050 |
| 6 | Online | 22800 | 27650 | 30100 |
Download this sheet as an Excel practice file (.xlsx)
Paint once
In the sample report, the Q1 column is finished: currency format, right alignment, a fill on the header. Q2 and Q3 were added later and look plain. Format Painter copies the look of Q1 across without touching the numbers.
- Select B1:B6, the formatted column including its header.
- Click Home > Format Painter. The pointer shows a paintbrush next to the cross.
- Drag over C1:C6. The formatting is applied when you release the mouse, and the brush turns off.
Paint many places: double-click
Double-click Format Painter instead of clicking it and the brush stays on. Click or drag over as many ranges as you like, on any sheet, and press Esc (or click Format Painter again) when you're done. This is the fastest way to format every subtotal row in a long report.
If you select a pattern, such as two rows with different fills, and paint over a bigger range, the pattern repeats. For banded rows that stay right when you add data, a table or a conditional formatting rule is better; see shade alternate rows.
What it copies and what it doesn't
Because merged cells and conditional formatting come along, check the result when you paint from a fancy header or a highlighted range. Painting conditional formatting over and over can leave many copies of one rule; tidy them in Home > Conditional Formatting > Manage Rules.
- Copies: number format, font, size, bold and color, fill, borders, alignment, wrap text, merged cells and conditional formatting.
- Doesn't copy: values, formulas, data validation dropdowns, comments and notes.
- Column width: only when you select whole columns as both source and target (click the column letter, then paint over another column letter). Row height works the same way with whole rows.
Copy formatting with the keyboard
Paste Special does the same job and is easier to repeat from the keyboard. It's also the way to apply formatting to a range you've already selected.
- Select the formatted cells and press Ctrl+C.
- Select the target cells.
- Press Ctrl+Alt+V to open Paste Special, press T for Formats, then Enter.
- Press Esc to clear the copy marquee.
Is there a mouse route for Paste Special formats?
Yes. After copying, go to Home > Paste arrow and pick the Formatting icon under Other Paste Options, or right-click the target and use the same icon in the paste options.
Across sheets and workbooks
Format Painter keeps its brush while you move around. Select the source, click Format Painter, click another sheet tab (or switch to another open workbook) and paint. A table you formatted for January can set the look of February's sheet in a few seconds. To format many sheets with the same layout at once, select the source, double-click Format Painter and visit each sheet in turn.
When painting a number format does nothing
A common surprise: you paint the currency format onto a column and one cell stays plain. That cell is almost always a number stored as text, often from a paste or an export. A number format only changes how numbers look; text is left as it is. In the sample, the East Q2 figure was pasted as text.
Result: TRUE
TRUE: 41200 in C4 is text, so no number format will touch it, and it's left out of totals as well.
How do I check a whole column?
COUNT only counts real numbers, so compare it with the number of rows.
Result: 4
4: five cells hold figures but only four are numbers. Convert the text one (see numbers stored as text) and repaint.
Common mistakes
Single-clicking and wondering why the brush stopped
A single click paints once. Double-click Format Painter to keep it on, Esc to stop.
Painting from a merged header onto data
Merged cells are copied too. Undo with Ctrl+Z and paint from an unmerged cell.
Expecting it to copy dropdowns
Data validation isn't formatting. Use Paste Special > Validation for that.
Painting currency onto numbers stored as text
Text ignores number formats. Convert the cells to numbers first.
FAQ
Where is Format Painter in Excel?
On the Home tab, in the Clipboard group on the far left: the paintbrush icon. It's in the same place on Mac and in Excel for the web.
How do I keep Format Painter on in Excel?
Double-click it instead of single-clicking. It stays active until you press Esc or click it again.
What is the shortcut for Format Painter in Excel?
There's no single key for the brush. Copy with Ctrl+C, then Ctrl+Alt+V, T, Enter pastes only the formatting. Alt+H, F, P turns the brush on from the keyboard in English Windows Excel.
Does Format Painter copy column width?
Only when you select whole columns as source and target by clicking the column letters. Otherwise use Paste Special > Column widths.
Can I use Format Painter on another sheet?
Yes. Click Format Painter, switch to the other sheet or workbook and paint there.
Functions used
Related guides
- How to remove conditional formatting in Excel
- How to change the default font in Excel
- How to shade alternate rows in Excel
- How to add borders in Excel
- How to convert numbers stored as text in Excel
- How to check if a cell contains a number in Excel
Updated