Excel.Tips.Net ExcelTips (Menu Interface)

Changing Paper Size for a Complete Workbook

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.

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
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.

Related Tips:

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!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

MClark    12 Oct 2016, 17:31
Is there a way to use this macro on just the worksheets you have selected when you run the macro (rather than all worksheets in the workbook)?
JIMIL    11 May 2016, 23:38
vijay    02 Oct 2015, 04:49
I face the same problem ,now it s okay as default printer setting was wrong.
phreaddy    13 Jan 2015, 15:26
Is it possible to print a workbook with sheets of different-sized paper? I have a workbook with a single 11X17, a few 8.5x14, and mostly 8.5x11. All the pages print out on the same size of whatever sheet is at the front.
Say Samlot    09 Sep 2014, 05:01
Dear sir
I don't about How to set the full margins of A4? because we want to get a full of A4. but when We print preview, we saw a full of A4 but we printed it. We can not get what we have done.
Nasty Habit    31 Jul 2014, 22:02
I need to change the paper size to legal. Can't seem to do it in Excel (Microsoft Office 2007 Pro). I have a new laptop with Windows 8.1. Wish I had my old XP back. And, wish I could manipulate this Excel like I could the old 2003 edition I used to have. Should never have gotten rid of it or XP. But had to.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.