Headers and Footers

Excel provides an area for headers and footers so you can add information to the top and bottom of a sheet. They are especially useful if your purpose for using Excel is to create a printed or PDF version of your sheet. The following articles explore the tools Excel provides to edit headers and footers and how you can achieve the look you need on your sheet.

Tips, Tricks, and Answers

The following articles are available for the 'Headers and Footers' topic. Click the article''s title (shown in bold) to see the associated article.

   Adding Ampersands in Headers and Footers
Add an ampersand to the text in a header or footer and you may be surprised that the ampersand disappears on your printout. There's a reason for that (and a way around it) as discussed in this tip.

   Adding Graphics to a Header or Footer
Excel makes it easy to add graphics to a header or footer. Here's the steps to make it happen.

   Ampersands in Headers and Footers
Add an ampersand to the text in a header or footer and you may be surprised that the ampersand disappears on your printout. There's a reason for that (and a way around it) as discussed in this tip.

   Changing Page Number Format
Need your page numbers to not appear as regular Arabic numerals? Here's a way to get them to appear in a different way--in this case in alphabetic order.

   Changing Section Headers
Add subtotals to a worksheet and you can instruct Excel to start each new subtotal section on a new printed page. You may want to also include different headers for each new section, but Excel doesn't include such a capability. Here's how to get the differing headers you want.

   Copying Headers and Footers
Need to copy headers and footers from one worksheet to another? How about from one workbook to another? Here are some techniques you can use to make the copying easier.

   Creating Long Page Footers
Ever wish that you could create nice, long footers that appear at the bottom of each page when you print your worksheet? Excel is lacking a bit in this capability, but there are ways you can get around the limitation.

   Date Last Edited
When adding headers or footers to your worksheets, you may want to include the date that the workbook was last edited. Excel doesn't provide such information, but you can create your own date.

   Dynamic Headers and Footers
Do you want to change the headers and footers that appear on different pages of your printout? Here's how you can get just what you want.

   Find and Replace in Headers
Using Find and Replace is something quite routine in Excel, as it easily allows you to find and replace information in your worksheets. What if you want to find and replace information in headers or footers, however? That isn't quite as easy.

   First and Last Names in a Page Header
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 the page's header when printed. This is easy to do in Word, but more difficult in Excel.

   Last Saved Date in a Footer
When printing out a worksheet, you may want Excel to include, in the footer, the date the data was last saved. There is no easy way to include that information without using a macro. Here's how you can get the output you need.

   Leading Zeros in Page Numbers
Page numbers in Excel printouts are typically simple counters, without much chance for embellishment. If you want to add leading zeros to your page numbers, the best solution is to use a macro to do your printing.

   Moving Part of a Footer Down a Line
Setting up a single footer line for your printouts is fairly easy. If you want to move part of the footer down a line so that the footer occupies two lines, well that can be tricky.

   Putting a Different Date in a Header
Today's date is easy to add to a header, but what if you want to add a date that is adjusted in some manner? Adding yesterday's date or tomorrow's date, for instance, is easy if you use the macro provided in this tip.

   Putting Cell Contents in Footers
Referencing information between cells in a worksheet is a piece of cake using some elemental formulas. You cannot, however, use formulas to reference cell information in page footer. Here's a way you can get around this apparent shortcoming.

   Putting Headers and Footers on Multiple Worksheets
You can easily create headers and footers for multiple worksheets by working with a selection set of the worksheets you want to affect. Here's how the process works.

   Roman Numerals for Page Numbers
Yes, Excel can work with Roman numerals, and it even provides a worksheet function that converts to them. How you use those Roman numerals as page numbers on a printout is not nearly as easy, however.

   Selective Headers and Footers
Want to print different headers or footers on different parts of your worksheet? Excel has no inherent way to do this, but a short macro can make sure that the headers and footers are printed on only the pages you want.

   Sequential Page Numbers Across Worksheets
How do you want your page numbers to appear on your printed worksheets? Chances are good that you want them to be sequential, regardless of what you print. Here's how to make sure that the page numbers are, in fact, in sequential order.

   Setting Header/Footer Margins
Do you find that there is a lot of extra space around that data on your worksheet when it is printed? Changing the margins in Excel is easy, and might cut down on how much paper you use.

   Specifying Date Formats in Headers
Don't like the default date format used by Excel when you place the date in a header or footer? You can use a macro to get just the exact type of date format you want.

   Specifying the Y Value in X of Y Page Numbering
Page numbers in Excel headers and footers have always been a source of frustration for users. This tip recounts one frustration with page numbering and a way that you can work around it.

   Using a Different Footer on Secondary Pages
When printing a worksheet, you may want to have the footer different on the first page of your document than it is on subsequent pages. Here's a way you can get the desired changes.

   Using a Formula in a Footer
Excel won't let you place a formula directly into a footer. You can, however, create a simple macro that will produce the same results as a formula.

This Site

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.

Newest Tips
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