Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Selective Headers and Footers.
by Allen Wyatt
(last updated February 20, 2016)
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 prefer, you can create a macro that prints 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 (12101) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Selective Headers and Footers.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Add an ampersand to the text in a header or footer and you may be surprised that the ampersand disappears on your ...Discover More
You can easily create headers and footers for multiple worksheets by working with a selection set of the worksheets you ...Discover More
Yes, Excel can work with Roman numerals, and it even provides a worksheet function that converts to them. How you use ...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 ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.