Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Changing Paper Size for a Complete Workbook.

Changing Paper Size for a Complete Workbook

by Allen Wyatt
(last updated September 14, 2015)

Bob has a workbook that has about fifteen worksheets in it, and he needs to change the page format for all the worksheet from Letter to A4. There is a quick way to make the change by following these steps:

  1. Right-click any worksheet tab. You'll see a Context menu appear.
  2. Choose Select All Sheets. All the worksheet tabs should now be selected.
  3. Choose Page Setup from the File menu. Excel displays the Page Setup dialog box.
  4. Make sure the Page tab is displayed. (See Figure 1.)
  5. Figure 1. The Page tab of the Page Setup dialog box.

  6. Using the Paper Size drop-down list, choose A4.
  7. Click OK.
  8. Right-click any worksheet tab. You'll again see a Context menu.
  9. Choose Ungroup Sheets.

That's it; the paper size is now set for all the worksheets. There is a drawback to this approach, however: If individual worksheets have differing page setup settings (different orientations, margins, headers, footers, etc.), then following these steps will set them all the same. If you only want to change the paper size and don't want to change any other settings, your only recourse is to use a macro to do the change.

Sub AllSheetsA4()
    Dim sht As Variant

    For Each sht In ActiveWorkbook.Sheets
        sht.PageSetup.PaperSize = xlPaperA4
    Next
End Sub

The macro steps through each sheet in the workbook, changing only the PaperSize property so that the sheet will print on A4 paper.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2896) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Changing Paper Size for a Complete Workbook.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Turning Off Synchronous Scrolling

Synchronous scrolling can be a real help when you are working with worksheets that are similar in layout. If your worksheets ...

Discover More

Continually Saving Normal.dot

If your Normal.dot file is continually saved when you exit Word, even when you haven't made any changes to it, the culprit ...

Discover More

Returning Blanks with VLOOKUP

Normally the VLOOKUP function returns a value, and if it can't return a value it returns a zero. Here's how you can use the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Setting Page Margins

When getting ready to print your worksheet, you may want to take a moment to check what margins Excel will use on the ...

Discover More

Changing Page Margins

If your worksheet is destined to be printed, you'll need to be concerned with how it appears on the page. One layout setting ...

Discover More

Setting Default Print Margins

Don't like the print margins that Excel uses by default? You can change the default by changing the workbook on which Excel ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share