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.

   Adding Last-Row Data to a Page Footer
If you want to modify information that appears in the footer of a worksheet printout, on a page-by-page basis, you can only do that using a macro. This tip shows how to pull a cell value from the last row on each page and stuff it into the footer.

   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.

   Copying Headers or Footers within a Workbook
If you want to copy headers or footers from one worksheet to another, it is a relatively easy process, as described in this tip. If, however, you want to only copy footers (without affecting headers), then the process requires a macro.

   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.

   Full-Width Headers and Footers in Excel
Excel allows you to create headers and footers. In this tip you discover that the headers and footers in Excel may not be quite as limited as they first appear.

   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.

   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 the 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 Custom Date Format in a Header or Footer
Need to show a custom date in the header or footer of a printout? You'll need to resort to using a macro, as described in this tip.

   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.

   Using X of Y in a Page Footer
If you want your printed worksheet to include page numbers, adding them is relatively easy. This tip shows two ways that you can get the desired footer.

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.