Imagine you've worked all day to make an Excel file look nice. You update cell borders, fonts, colors, and numbers, only to have your boss ask you to make all the other Excel reports look as nice as this one. Do you have to spend all day trying to format the existing Excel reports like the one you just formatted?
Luckily, we have the Format Painter in Excel's seemingly endless bag of tricks. The Format Painter will let you quickly and easily copy formatting from one cell to another cell or group of cells. With the Format Painter (and other tricks like Paste Special) you can copy formats to new cells within a specific worksheet, to other worksheets in your Excel workbook, and even to entirely different Excel files. Now you can quickly copy the formats to all of the other Excel reports for your boss.
The Format Painter in Excel lets you copy the formatting of one cell (or group of cells) and quickly "paint" the same formatting elsewhere in your workbook (or even to another Excel file). The Format Painter essentially offers a way to "copy and paste" formats in Excel. At a high level, this is how it works:
And just like that, the Format Painter will apply the formatting from the cell in step 1 to the cell in step 3.
The Format Painter essentially copies any formatting that has been applied to the cell, which includes, but is not limited to, the following:
You may have noticed that after you use the Format Painter to copy formatting to a single cell, Excel then exits Format Painter mode and your cursor returns to normal. This is fine if you want to copy formatting to just a single cell, but what if we want to copy formatting to a range of adjacent cells?
To copy a format to multiple adjacent cells (that is, cells that are all touching) we can use the Format Painter in almost the same way, but instead of simply clicking once on the target cells, we can hold the mouse button and drag the cursor across multiple cells. By selecting multiple cells with a single mouse click we can quickly copy formatting to multiple adjacent cells. But what if we want to copy formatting to several cells that aren't touching?
What if we want to copy formatting to multiple non-adjacent cells? We can "lock" Format Painter mode by double clicking the Format Painter icon in the Home tab of the ribbon.
By double-clicking the Format Painter button and locking Format Painter mode, you will be free to paste the chosen format to as many cells as you want, using as many clicks as you want, until you press the Esc key and exit Format Painter mode.
Sometimes you want to format an entire column the same way as another column. This can be done easily by clicking the column header of the column whose formatting you want to copy, clicking the Format Painter button, and then clicking the column header of the target column. The formatting of the first column will copy, row by row, to the target column.
The same method can be applied to rows to copy formatting from one row to another.
The Paste Special functionality in Excel offers ways to paste certain attributes of a cell or group of cells to another area. Typically we think of any copy & pasting values of cells, but we can also copy & paste formats, formulas, and other attributes with Paste Special.
First, click the cell with the formatting you want to copy. Either press Ctrl+C to copy or right-click and copy the cell. Next, select the target cell(s) and right-click and choose Paste Special. The Paste Special menu will appear. Choose Format and click ok. This will paste only the formatting from one cell to another and will not paste the actual values contained within the cells.
Excel has many other tips and tricks to make you more productive. Learn more about Excel now by checking out Excel Exercises!