Copying Formats, Part 2: Cells & Columns Using the Format Painter

Similar to last week’s blog, we’re going to make the format of particular cells or columns identical to another cell or column, and, again, we’ll 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 Format Painter icon.

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 2-02b-BEFORE 

  1. Click Cell D1 thru Cell D7. In the Home tab, in the Clipboard group, double-click the Format Painter icon, as shown here:
     Copy Formats-Part 2-05b--format painter icon
  2. Double-clicking the Format Painter icon will allow us to paste formats multiple times. Place the cursor over Cell F1, but do not click. The top portion of the spreadsheet should look as shown just below. The active format painter is circled.
    Copy Formats-Part 2-10b-after double-clicking
  3. Click Cell F1, and note that the formats in Cells F1 thru F7 are now similar to Cells D1 thru D7.
  4. Click Cell H1, and note that the formats in Cells F1 thru F7 as well as Cells H1 thru H7 are now similar to Cells D1 thru D7, as shown here:
    Copy Formats-Part 2-15b-after applying twice 
  5. Did you notice that the format painter is still active? If we clicked some other cell, the formats from Cells D1 thru D7 would be applied again. To deactivate the Format Painter, press the Esc key on the keyboard. The Esc key is located in the upper left corner of most keyboards.

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 Format Painter icon.
  2. Click Column E. Note that the format from Column C has been applied to Column E.
  3. Click Column G. Note that the formats of both Column E and Column G are now similar to Column C.
  4. Press the Esc key to deactivate the Format Painter.
  5. 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.

A final note: We could have clicked the Format Painter icon once, but that would have limited us to apply formats just once. My personal preference is to have it available to apply formats multiple times.

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

About Robert Ryan

Robert F. Ryan, MBA, CPA, is an independent business consultant, educator, writer, and finance leader in a Fortune 50 multinational company. He has a passion for simplicity, quality, efficiency, and a unique ability to explain things to people, so they understand quickly and thoroughly. He's worked with spreadsheets extensively for almost thirty years, including VisiCalc, Lotus 1-2-3, and Excel.
This entry was posted in Excel Copy Formats and tagged , , , , . Bookmark the permalink.

Leave a Reply