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:
- Select cells A1:E6. Ctrl+C to Copy.
- Select the upper-left corner of the next section. Open the Paste dropdown on the Home tab. Select the Paste Formats icon.
- 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.
- Move the cell pointer to the next section. Repeat the Paste Formatting command.
- 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.