Copying Formats, Part 1: Cells & Columns Using Paste Special

Have you ever had a situation where you wanted to make the format of particular cells or columns identical to another cell or column? I encounter this situation somewhat frequently, and when I do, I use Excel’s format copying feature. With interest rates being as low as they are, I thought we’d use a spreadsheet showing how to analyze refinancing options to demonstrate this feature. (This file also includes a break-even analysis worksheet from the Federal Reserve.)

We can copy formats using either the Paste Special feature, or the Format Painter icon. This week, we’ll use the Paste Special feature. Next week, we’ll use the Format Painter icon to copy formats.

Please click this link to open an Excel 2007 file (zipped, no macros) named “Refinancing-Analysis-BEFORE.xlsx.” Note that the cells in the top portion of Columns E thru H are unformatted, as shown here:
Copy Formats-Part 1-02b-BEFORE

  1. Click Cell D1 thru Cell D7. In the Home tab, in the Clipboard group, click the Copy icon, as shown here:
    Copy Formats-Part 1-05b--thecopy icon
  2. Click Cell F1. In the Home tab, in the Clipboard group, click the dropdown arrow below the Paste icon, and click Paste Special, as shown here:
    Copy Formats-Part 1-10b-Paste Special
  3. In the box that appears labeled “Paste Special,” click the radio button to the left of the word “Formats,” as shown here:
    Copy Formats-Part 1-15-Paste Special box
  4. Click OK. 
  5. Click Cell H1 and repeat the steps we just followed for Cell F1. Note that we didn’t have to copy Cells D1 thru Cell D7 again. When finished, press the Esc key located in the upper left corner of most keyboards to remove the blinking lines around Cells D1 thru D7.

We’ll now use the same method to copy the formats from Column C to Columns E and G. Please use the screenshots above as needed.

  1. Click Column C. In the Home tab, in the Clipboard group, click the Copy icon.
  2. Click Column E. In the Home tab, in the Clipboard group, click the dropdown arrow below the Paste icon, and click Paste Special. In the box that appears labeled “Paste Special,” click the radio button to the left of the word “Formats” and click OK.
  3. Click Column G and repeat the steps we just followed for Column E. Note that we didn’t have to copy Column C again. When finished, press the Esc key to remove the blinking lines around Column C.
  4. Click Cell A1 to remove the highlighting in Column G.

Please click this link to open the Excel 2007 file (zipped, no macros) named “Refinancing-Analysis-AFTER.xlsx” and compare it to your file. Note that the cells in the top portion of the “AFTER” version are now formatted, as shown here:
Copy Formats-Part 1-20b-AFTER

I hope this has been helpful for you. Next week we’ll cover copying formats using the Format Painter icon. Until then, I hope you all have a wonderful week.

This entry was posted in Excel Copy Formats and tagged , , , . Bookmark the permalink.

5 Responses to Copying Formats, Part 1: Cells & Columns Using Paste Special

  1. DV says:

    In 2007 if i used Ctrl V the smart Tag at bottom had such options but one specific option paste formating only ..I can not see in 2010 and then symbols with no written description make it even difficult to paste the data…

  2. Pingback: Copying Formats, Part 2: Cells & Columns Using the Format Painter | Simply Learning Excel 2007

  3. Pingback: Copying Formats, Part 2: Cells & Columns Using the Format Painter | Simply Learning Excel 2007

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>