Excel Exercises Practice Name

How to Use the Format Painter in Excel

And Other Tricks to Copy Formatting From One Cell to Another

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.

What is the Format Painter?

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:

  1. Click the cell whose format you want to copy to other cells
  2. Click the Format Painter icon in the home tab of the ribbon - the cursor will turn to a + sign with a paintbrush
  3. Click the cell that you want to format like the cell in step 1 - the cell's format will instantly update to match the format of the first cell

And just like that, the Format Painter will apply the formatting from the cell in step 1 to the cell in step 3.

What Kinds of Formatting Does the Format Painter Copy in Excel?

The Format Painter essentially copies any formatting that has been applied to the cell, which includes, but is not limited to, the following:

  • Cell background colors
  • Font size, color, and style
  • Cell borders
  • Number formats (currency, percentage, general, date, etc.)
  • Font formats (bold, italics, underline, strikethrough)
  • Text alignment (center, right align, left align)
  • Conditional formatting

How to Copy Formatting to a Range of Cells in Excel

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?

Copy Formatting to Multiple Non-Adjacent Cells

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.

  1. Select the cell whose format you want to copy to other cells
  2. Double-click the Format Painter icon in the home tab of the ribbon - this will "lock" Format Painter mode
  3. Click every cell that you want to format like the cell from step 1
  4. Press the Esc key to exit Format Painter mode

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.

Copying Formatting to Rows and Columns

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.

Copy Formats in Excel With Paste Special

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.

Learn More About Excel

Excel has many other tips and tricks to make you more productive. Learn more about Excel now by checking out Excel Exercises!