You have a cell with the perfect formatting and need to apply it to other cells. Manually setting each font, color, and border is time-consuming. The Format Painter tool copies all formatting from one cell and applies it elsewhere. This article explains how to use the Format Painter button for single and multiple applications.
Key Takeaways: Using the Format Painter in Excel
- Home > Clipboard > Format Painter button: Copies all visual formatting from a selected source cell to a target cell or range.
- Double-click the Format Painter button: Locks the tool on to apply formatting to multiple non-adjacent ranges without re-selecting it.
- Escape key: Turns off the Format Painter tool after you finish applying formatting, especially when it is locked on.
What the Excel Format Painter Does
The Format Painter is a tool on the Home tab. It duplicates the visual style of a cell without copying its data or formulas. This includes font type, size, color, cell fill, borders, number formatting, and alignment. It is ideal for quickly making reports and tables look consistent. You only need one correctly formatted cell to use as a source.
Formatting Attributes Copied
The tool copies all direct cell formatting. This covers font attributes like bold, italic, and underline. It also copies cell background color, border styles, and number formats like currency or date. Text alignment, including indentation and orientation, is also transferred. Conditional formatting rules applied to the source cell are copied as well.
Steps to Use the Format Painter Button
Follow these steps to copy formatting from one cell to another.
- Select the source cell
Click the cell that has the formatting you want to copy. This cell becomes your style template. - Activate the Format Painter
Go to the Home tab on the ribbon. In the Clipboard group, click the Format Painter button. The mouse cursor will change to a paintbrush icon. - Apply to a target range
Click and drag the paintbrush cursor over the cells where you want the formatting applied. Release the mouse button to paste the formatting.
Applying Formatting to Multiple Areas
You can lock the Format Painter on to format several separate ranges.
- Select the formatted source cell
Click the cell with the desired formatting. - Double-click the Format Painter button
In the Home > Clipboard group, double-click the Format Painter icon. The paintbrush cursor will stay active. - Paint multiple ranges
Click and drag over the first target range. Then, click on another cell or drag over another area. You can repeat this as many times as needed. - Turn off the tool
Press the Escape key on your keyboard. This deactivates the Format Painter and returns your cursor to normal.
Common Mistakes and Limitations
Format Painter Copies Conditional Formatting Rules
If your source cell uses conditional formatting, the rule is copied to the target cells. This can cause unexpected color changes in the new location if the rule logic does not fit the new data. Review the conditional formatting rules manager under Home > Styles after painting to check for copied rules.
It Does Not Copy Cell Width or Height
The Format Painter copies visual styles inside the cell but not the column width or row height. To copy a column’s width, select the header of the source column, click Format Painter, then click the header of the target column. This is a specific method for column width only.
Accidentally Overwriting Existing Formatting
Applying the Format Painter completely replaces the target cell’s existing formatting. There is no merge or blend option. If you need to undo this, use Ctrl+Z immediately after painting to revert the change.
Format Painter vs Paste Special: Formatting
| Item | Format Painter Button | Paste Special > Formats |
|---|---|---|
| Primary Use | Quick visual copying with a paintbrush cursor | Precise pasting from the clipboard after Copy (Ctrl+C) |
| Method | Click button, then select target | Copy source, right-click target, choose Paste Special > Formats |
| Best For | On-the-fly formatting, multiple non-adjacent cells | Applying formatting to a large, single range or remote location |
| Copies Column Width | No, requires separate column header click | No, requires Paste Special > Column Widths option |
| Keyboard Shortcut | None for activation, Escape to cancel | Ctrl+C, then Alt+E+S+T, then Enter |
You can now quickly standardize the look of your Excel sheets. Use the double-click method to format headers across multiple tables. For more control, explore the Paste Special dialog to paste only formats from cells you have copied.