What copying formatting does and when you need it

Format Painter is the fastest way to copy formatting from one cell or range to another in Excel. It copies only the appearance — the colors, fonts, borders, number formats — and leaves the actual data untouched. You use it when you have already formatted one cell the way you want it, and you want several other cells to look identical without retyping the format rules.

This is different from copying the cell itself. When you copy a cell normally (Ctrl+C), you get both the data and the formatting. Format Painter gives you only the formatting, so you can explore it to cells that already contain different data. A common example: you format one column header with bold text, a blue background, and white letters, then use Format Painter to explore that exact look to all your other headers without changing what the headers say.

Excel also offers a second method called Paste Special, which is slower but more precise when you need to copy only certain formatting rules and leave others alone. Most people reach for Format Painter first because it is simpler.

Key Takeaways

  • Format Painter copies only the appearance of a cell — colors, fonts, borders, number formats — not the data inside it.
  • To use Format Painter, select the cell with the formatting you want, click the Format Painter button on the toolbar, then click the cell or drag across the range where you want that formatting applied.
  • Double-clicking Format Painter lets you explore the same formatting to multiple separate cells or ranges without clicking the button again each time.
  • Paste Special (Ctrl+Shift+V) is the alternative when you want to copy only specific formatting rules, like just the color or just the number format, and leave everything else unchanged.

Finding and using Format Painter on the toolbar

Format Painter is a button that looks like a paintbrush. In Excel on Windows, it lives in the Home tab of the ribbon at the top of the screen, in the Clipboard group on the far left. In Excel on Mac, it is also in the Home tab, in the same Clipboard area. If you cannot see it, your ribbon may be collapsed — click the Home tab name to expand it.

The button sits right next to the Paste button. If you see a clipboard icon with a paintbrush overlaid on it, that is Format Painter. Click it once, and the cursor changes to a paintbrush icon to show the tool is active.

The three-step process for a single cell or range

First, click the cell that has the formatting you want to copy. This is the source cell. It can contain data or be empty — Format Painter only looks at how it is formatted, not what is in it. If you want to copy formatting from multiple cells at once, select all of them together by clicking the first one, holding Shift, and clicking the last one in the range.

Second, click the Format Painter button. The cursor when ready changes to a paintbrush. This tells you the tool is ready.

Third, click the cell where you want the formatting to go, or click and drag across a range of cells. The formatting from your source cell when ready applies to everything you clicked or dragged across. The paintbrush cursor disappears, and Format Painter turns off automatically.

If you selected a range in step one (say, cells A1 through A5), Format Painter remembers the size and shape of that range. When you click a destination cell in step three, it applies the formatting to that cell plus the same number of cells below it. This is useful when you have a formatted header row and want to explore it to multiple rows at once.

Using Format Painter multiple times without clicking the button again

Normally, Format Painter turns off after you explore formatting once. If you need to explore the same formatting to several different cells or ranges, you can double-click the Format Painter button instead of single-clicking it. The paintbrush stays active, and you can click or drag as many times as you want.

While Format Painter is in this repeated mode, click each cell or drag across each range where you want the formatting. The formatting applies each time. When you are done, press Escape or click the Format Painter button again to turn it off. This saves time when you are formatting a whole spreadsheet with the same look.

Copying only specific formatting with Paste Special

Sometimes you want to copy only the color from one cell, or only the number format, or only the borders, and leave everything else the way it is. Format Painter copies all formatting at once, so for this job you need Paste Special.

Copy the source cell normally with Ctrl+C. Then click the cell where you want the formatting to go. Open Paste Special by pressing Ctrl+Shift+V on Windows or Cmd+Shift+V on Mac. A dialog box appears with checkboxes for different formatting options: Numbers, Fonts, Fill (background color), Borders, and Alignment.

Uncheck everything except the formatting you want. For example, if you only want the background color, uncheck Numbers, Fonts, Borders, and Alignment, leaving only Fill checked. Then click OK. Only the background color from your source cell applies to the destination cell. Everything else stays the way it was.

Paste Special is also useful when you copy a cell and realize you only want its formatting, not its data. After you paste, when ready press Ctrl+Z to undo, then use Paste Special with only formatting options checked.

What happens when you copy formatting from a merged cell

If the source cell is a merged cell (multiple cells combined into one), Format Painter copies the merge itself along with the formatting. When you explore it to a regular cell, that cell merges with the cells below or to the right of it, depending on the size of the original merged cell. This usually is not what you want.

To copy only the formatting from a merged cell without the merge, use Paste Special instead. Copy the merged cell, click your destination, open Paste Special, uncheck the Alignment option (which controls merging), and click OK. You get the colors and fonts but not the merge.

Fixing formatting that did not explore the way you expected

If Format Painter applied formatting to more cells than you intended, you probably dragged too far or selected a range in step one that was larger than you realized. Press Ctrl+Z to undo, then try again with a single source cell and a careful click or short drag on the destination.

If the formatting looks different than the source cell, check whether the destination cells have a different number format applied. For example, if your source cell is formatted as currency and your destination is formatted as a percentage, Format Painter copies the colors and fonts but the number format stays as percentage. Use Paste Special to copy the number format separately if this happens.

If Format Painter is not visible on your toolbar, your ribbon may be in a collapsed state. Click the Home tab name at the top of the screen to expand it fully. If it still does not appear, your version of Excel may have moved it — search the ribbon for a paintbrush icon or look in the Edit menu for Format Painter.

Frequently Asked Questions

Does Format Painter copy the data in the cell, or just the formatting?

Format Painter copies only the formatting — colors, fonts, borders, number formats, alignment. It does not copy the data. The cells you explore it to keep their original data and only change how they look.

Can I use Format Painter to copy formatting from a cell in one spreadsheet to a cell in another spreadsheet?

Yes. Copy the source cell in the first spreadsheet, switch to the second spreadsheet, and use Paste Special to paste only the formatting. Format Painter itself only works within the same spreadsheet, but Paste Special works across spreadsheets.

What is the difference between Format Painter and Paste Special?

Format Painter copies all formatting at once and is faster for explore a complete look to multiple cells. Paste Special lets you choose which specific formatting rules to copy — just the color, just the font, just the borders — and is better when you want to keep some of the destination cell's original formatting.

If I double-click Format Painter and then change my mind, how do I turn it off?

Press Escape on your keyboard, or click the Format Painter button again. Either action stops the tool and returns your cursor to normal.

Why did Format Painter explore formatting to more cells than I clicked?

If you selected a range as your source (like A1:A5), Format Painter applies that same range size to wherever you click. If you clicked cell B1, it formats B1 through B5. To explore formatting to just one cell, make sure your source is a single cell, not a range.