Copying Formats, Part 5: Page Layouts

Excel certainly has lots of options for formatting a page to be printed, but do we have to apply each of those features every time we print, especially if we want to print out a page in a way that’s similar to another worksheet we just printed? Thankfully, the answer is no, and showing  how to copy page layouts is the subject of this week’s blog. Since setting up page layouts, including Print Preview, is covered in detail in Chapter 3 “Printing the Worksheet” in the book “Simply Learning Excel 2007” available at, we won’t cover that in this article.

Here are the steps we’re going to follow to copy page layouts:

  • Click here to open an Excel 2007 file (zipped, no macros) named “Checkbook-PageLayout-BEFORE.xlsx.” Note that Sheet2 is to the left of the sheet named Checkbook, and Sheet1 is to the right.
  • If not already selected, click the sheet named Checkbook. This is the sheet containing the page layout that we’d like to copy to the other two sheets in the file.
  • While holding down the Ctrl key, click the sheet named Sheet1 then the sheet named Sheet2.
  • Release the Ctrl key.
  • All three sheet names should now be highlighted, as shown here:
    Copy Formats-Part 5-02c-All three sheets selected
  • Print Preview the three selected sheets (Office Button > Print > Print Preview). Use the PgDn and PgUp keys on the keyboard or the mouse wheel to view each page. Note that the first and third pages have a blank page layout, while the Checkbook sheet has an extensive, customized page layout – landscape orientation, header, footer, etc.
  • Use the PgDn and PgUp keys on the keyboard or the mouse wheel so that the sheet named Checkbook is in the Print Preview window.
  • Close the Print Preview.
  • Click the Page Layout tab in the Ribbon.
  • In the Page Layout tab, in the Page Setup group, click the little Page Setup dropdown arrow, as shown here:
    Copy Formats-Part 5-05b-Page Setup dropdown arrow
  • Click OK. That one little step has now copied the page layout of the Checkbook sheet to the other two sheets.
  • Let’s verify this by Print Previewing all three pages (Office Button > Print > Print Preview). Notice that all three pages now have the same, extensively customized, page layout that only the Checkbook sheet had before we started.
  • Close the Print Preview.
  • We have one more, critical step. Right-click one of the three selected sheet names at the bottom of the window.
  • In the menu that appears, click Ungroup Sheets. If we didn’t do this, then whatever we entered in any cell in one sheet would automatically appear in the same cell in both of the other sheets that were selected. Uugghh. (Can you tell that’s happened to me before?)

Click here to open an Excel 2007 file (zipped, no macros) named “Checkbook-PageLayout-AFTER.xlsx” and compare it to your results.

While what we covered today should help make setting up Page Layouts easier and faster, we still need to have a sheet in our workbook that has a page layout we want in order to copy it to the remaining sheets. But what if the workbook we’re working with doesn’t have such a sheet? One workaround is to copy a sheet that has the page layout we want from another workbook, and follow the steps above. Another approach – and the one I use – is to set up a default worksheet that we can insert at any time in any workbook. That’s what we’ll talk about next week. Until then, I hope you all have an enjoyable 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