MrExcel.com - Your One Stop for Excel Tips & Solutions

Copy Formatting to a New Range

October 09, 2023 - by

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.

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.

The Paste Options dialog offers a button for Formatting.
Figure 1355. Choose the icon with a percent sign and a paintbrush

There are two sections of this report with similar shape. The Unit Sales has been nicely formatted. The Dollar Sales section is not yet formatted.
Figure 1356. Copy the formatting to other report sections.

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.

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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Dan Cristian Pădureț on Unsplash