The Format Painter in Excel lets you copy formatting from one cell or group of cells to another cell or group of cells without copying their contents. Cell formatting includes everything from font size and color to borders, number formats, and conditional formatting.
To use the Format Painter, select the cell or cells whose formatting you want to copy, and click “Format Painter” in the Clipboard group of the Home tab on the ribbon.
Excel will then place a dotted line around the selected cell or cells to let you know that something has been copied (in this case, the formatting), and your cursor will change to a paintbrush icon.
Then, simply select the cell or cells where you want the formatting to be duplicated. In my case, I selected cell A10.
After you have pasted the formatting to those new cells, your cursor will return to its original icon, and the formatting of the original cells will no longer be copied.
What happens during this format duplication process depends on how many cells you select when copying the formatting, whether they contain the same formatting (if you select more than one cell), and how many cells you select when pasting the formatting:
|
Formatting Copied From |
Formatting Pasted To |
Result |
|---|---|---|
|
One cell |
One cell |
The formatting is duplicated from one cell to the other. |
|
One cell |
A range of cells |
The same formatting is applied to each pasted cell. |
|
A range of cells |
One cell |
The formatting is pasted to a range the same size as the copied range, starting at the selected cell. |
|
A range of cells |
A range of cells |
If the selected area for pasting the formatting is larger than the copied range, the formatting is reduplicated. |
If you need to copy the formatting to various places on the same spreadsheet, again select the cell or cells whose formatting you want to copy, but this time, double-click “Format Painter” in the Clipboard group of the Home tab on the ribbon. Now, each time you select the cell or cells where you want to paste the formatting, your cursor will retain the paintbrush icon, the copied cells will still be surrounded by the dotted line, and the Format Painter icon in the Home tab will remain selected.
To cancel the Format Painter tool, either click “Format Painter” again or press Esc.
The Format Painter tool isn’t restricted to cells you’ve formatted manually—it also works with conditional formatting. In this example, I’ve used conditional formatting to color any cells in column A containing values between 10 and 25 yellow, and any cells containing values between 26 and 50 green.
When I select column A, click “Format Painter,” and select column C, Excel duplicates the conditional formatting rule for this column.
Excel’s format painter doesn’t just work with cell formatting—you can also duplicate formatting between shapes, charts, text boxes, and most other items in your spreadsheet.
If you copy the formatting from a formatted Excel table to another range in your worksheet, only the visual formatting will be duplicated, and not the table’s properties.
As well as copying the formatting between cells on a worksheet, Excel’s format painter tool lets you copy the formatting to other sheets in the workbook, or even to sheets in other workbooks you have open. Alternatively, you can create duplicate worksheets within a workbook if you want two spreadsheets to be presented identically.
