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 a File Path and Filename
If you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the formulas in this tip. They are quick and easy to use.
Adding Ampersands in Headers and Footers
Place an ampersand into the text of a page header or footer, and you might be surprised to see it missing in your printout. This is due to the way in which Excel interprets the ampersand. Here's how to get around the problem.
Adding Graphics to a Header or Footer
Excel makes it easy to add graphics to a header or footer, as long as you are using at least Excel 2002. 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 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 a Footer
Adding a predefined footer to your worksheets is easy, and it helps convey valuable information when you make a printout. Excel even allows you to create your own custom footers.
Creating a Header
When preparing your worksheet for printing, you may want to add a header that appears at the top of each page that you print. Excel allows you to use either predefined headers or custom ones, as described in this tip.
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.
Default Headers and Footers
Changing the look of default headers and footers for your Excel workbooks.
Deleting a Footer
Excel allows you to add footers to the worksheets you create. If you decide you don't need a footer any more, here's how to get rid of it.
Deleting a Header
Each new Excel worksheet contains a page header, by default. Follow this tip to get rid of headers you don't need.
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.
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 Path Names in Headers or Footers
Using a macro to add the full path name into a header or footer in an Excel worksheet.
Header and Footer Background Color
Want to add some color to the printing of your page headers and footers? Your options are limited, as disclosed in this tip.
Header and Footer Data Codes
When creating headers and footers in an Excel worksheet, you can use special codes to add or format information. This tip details the codes you can use to add information.
Header and Footer Formatting Codes
When creating headers and footers in an Excel worksheet, you can use special codes to add or format information. This tip details the different formatting codes you can use.
Inserting the Saved Date In a Header or Footer
When preparing a worksheet for printing, you may want to include in the header or footer the last date the workbook was saved. This can't be done directly, but it can be done with the macro in this tip.
Keep Your Headings in View
When working with lots of data rows, it is easy to forget what the column headings say. Here's how to keep those headings visible.
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.
Multiple Line Headers and Footers
When working with headers and footers in a macro, you might find this tip helpful. It describes how you can create headers or footers that occupy multiple lines.
Positioning Headers and Footers
If your workbook is destined for the printed page, you need to be concerned with the layout of that page. Excel allows you to control where a header and footer appear on each page of a worksheet.
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.
Putting Spreadsheet Names in Headers or Footers
One of the things you can add to your page header or footer is the name of your workbook file name. Here's how to make the addition.
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.
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.
Turning Headers On and Off
Normally Excel displays row and column headers in a worksheet. If you prefer, you can turn these navigational aids off completely.
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 couple of ways you can get the desired changes.
Using Color in Headers and Footers
Applying color to the text in your headers and footers is a bit of a dream in some versions of Excel. Here's an overview of where color can be used and how you can get around some version limitations.