Most valuable professional
  • Excel Book Excerpt

Excel Copy Formatting to a New Range

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Copy Formatting to a New Range

Problem: I have several similar report sections on a spreadsheet. When I get the first report nicely formatted, I would like to copy the format to the other reports.


Figure 1206 Copy the formatting to other report sections.

Strategy: You can use Paste Special Formats to copy just the formats from one range to another:

1. Select cells A1:E6. Ctrl+C to Copy.

2. Select the upper-left corner of the next section. Open the Paste dropdown on the Home tab. Select the Paste Formats icon.


Figure 1207 Paste only the formatting.

Gotcha: If the target range contains any merged cells, you can not simply select the top left cell as indicated in step 2. Instead, you must select a rectangular range of the same size and shape as the range copied in step 1.

3. Move the cell pointer to the next section. Repeat the Paste Formatting command.

4. Repeat for any additional sections.

Results: The cell formats will be copied, but their values and formulas will not.

In prior versions of Excel, you can use Paste Special and then choose Formats from the Paste Special dialog.

Alternate Strategy: You can also use Format Painter mode to copy formats. You select A1:E6, double-click the Format Painter icon in the Home ribbon tab, and click A8 and A15. At each click, Excel will copy the formats to the new range. When you are finished, you can either click the Format Painter icon or press Esc to exit Format Painter mode.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: