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: Selective Headers and Footers.
by Allen Wyatt
(last updated July 27, 2017)
One of the long-time complaints about Excel is that it doesn't have a very robust method of creating and managing headers and footers. Consider the following scenario: You want to print your worksheet, but only have page numbers beginning on the second page.
There is no intrinsic method in Excel to handle this situation. There are some workarounds; for instance, you could put your first page on one worksheet (without headers or footers) and the subsequent pages on a different worksheet (which includes headers and footers). You could then print the two worksheets in one pass, and effectively achieve your goal.
If you have the Report Manager installed, you could use it to put together different reports based on the information in your worksheet. Using the Report Manager has been covered in other issues of ExcelTips. The Report Manager add-in was last distributed with Excel 2002, but you can still use it in Excel 2003. This Knowledge Base article explains how you can use it:
Neither of these approaches work for all situations, however. For instance, you may not be able to split your printout into multiple worksheets, or you may not have much experience with the Report Manager (or you don't want to download and install it). If you prefer, you can create a macro which will print your worksheet as you desire.
The following macro, GoodPrint, will print the first page of a worksheet without headers or footers, and then all subsequent pages as normal.
Sub GoodPrint() Dim hlft As String Dim hctr As String Dim hrgt As String Dim flft As String Dim fctr As String Dim frgt As String 'save current header hlft = ActiveSheet.PageSetup.LeftHeader hctr = ActiveSheet.PageSetup.CenterHeader hrgt = ActiveSheet.PageSetup.RightHeader 'save current footer flft = ActiveSheet.PageSetup.LeftFooter fctr = ActiveSheet.PageSetup.CenterFooter frgt = ActiveSheet.PageSetup.RightFooter 'remove header and footer With ActiveSheet.PageSetup .CenterHeader = "" .RightHeader = "" .LeftHeader = "" .CenterFooter = "" .RightFooter = "" .LeftFooter = "" End With 'print page one ActiveSheet.PrintOut 1, 1 'restore header and footer With ActiveSheet.PageSetup .LeftHeader = hlft .CenterHeader = hctr .RightHeader = hrgt .LeftFooter = flft .CenterFooter = fctr .RightFooter = frgt End With 'print the rest of the pages ActiveSheet.PrintOut 2 End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2189) 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: Selective Headers and Footers.
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!
When you have a worksheet that includes a long list of names, you may want the first and last names on each page to appear in ...Discover More
When adding headers or footers to your worksheets, you may want to include the date that the workbook was last edited. Excel ...Discover More
When printing a worksheet, you may want to have the footer different on the first page of your document than it is on ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.