Add a Standard Header or Footer
Headers and footers are simply standard text that appears on every page of the report. A header is printed in the top margin of the page, and a footer is printed in the bottom margin. Both are centered vertically in the margins. Unless you specify otherwise, Excel does not automatically add either a header or footer to a new workbook.
Use headers and footers in a report to identify the document used to produce the report and display the page numbers and the date and time of printing. The place to add a header or footer to a report is in Page Layout view. You can switch to this view by clicking the Page Layout View button on the Status bar or by clicking the Page Layout View button on the Ribbon's View tab, or by just pressing Alt+WP.
When the worksheet's displayed in Page Layout Preview, position the mouse pointer over the section in the top margin of the first page marked Click to Add Header or in the bottom margin of the first page marked Click to Add Footer.
To create a centered header or footer, highlight the center section of this header/footer area and then click the mouse pointer to set the insertion point in the middle of the section. To add a left-aligned header or footer, highlight and then click to set the insertion point flush with the left edge of the lefthand section or to add a right-aligned header or footer, highlight and click to set the insertion point flush with the right edge of the right-hand section.
Immediately after setting the insertion point in the left, center, or right section of the header/footer area, Excel adds a Header & Footer Tools contextual tab with its own Design tab. The Design tab is divided into Auto Header & Footer, Header & Footer Elements, Navigation, and Options groups.
Create a Custom Header or Footer
Most of the time, the stock headers and footers available on the Header button's and Footer button's drop-down menus are sufficient for your report printing needs. Every once in a while, however, you may want to insert information not available in these list boxes or in an arrangement Excel doesn't offer in the readymade headers and footers.
Choose View> Worksheet Views> Page Layout View. Click Go To Footer to jump to the footer area.
In any desired header or footer section, type the text you want for the header (or footer). You can format the header and
footer text just as you would any cell data. Click any options from the Header & Footer Elements group:
Page Number:
Insert a code that indicates the page number.
Number of Pages:
Insert a code that indicates the total number of pages.
Current Date or Current Time:
Insert the print date or time of day.
File Path, File Name, or Sheet Tab Name:
Include file information.
Picture:
Insert a graphic image such as a company logo.
Format Picture:
Resize, rotate, or crop a header or footer graphic image.
To use these command buttons in the Header & Footer Elements group to create a custom header or footer, follow these steps:
- Put your worksheet into Page Layout view by clicking the Page Layout View button on the Status bar or by clicking View | Page Layout
View on the Ribbon or pressing Alt+WP.
In Page Layout view, the text, Click to Add Header, appears centered in the top margin of the first page and the text, Click to Add Footer, appears centered in the bottom margin. - Position the mouse pointer in the top margin to create a custom header or the bottom margin to create a custom footer
and then click the pointer in the left, center, or right section of the header or footer to set the insertion and left-align,
center, or right-align the text.
When Excel sets the insertion point, the text, Click to Add Header and Click to Add Footer, disappears and the Design tab on the Header & Footer Tools contextual tab becomes active on the Ribbon. - To add program generated information to your custom header or footer (such as the filename, worksheet name, current date,
and so forth) click its command button in the Header & Footer Elements group.
Excel inserts the appropriate header/footer code preceded by an ampersand (&) into the header or footer. These codes are replaced by the actual information (filename, worksheet name, graphic image, and the like) as soon as you click another section of the header or footer or finish the header or footer by clicking the mouse pointer outside of it. - (Optional) To add your own text to the custom header or footer, type it at the insertion point.
When joining program-generated information indicated by a header/ footer code with your own text, be sure to insert the appropriate spaces and punctuation. For example, to have Excel display the Page 1 of 4 in a custom header or footer, you do the following:
- Click the Page Number command button and press the spacebar again.
- Type the word of and press the spacebar a third time.
- Click the Number of Pages command button.
This inserts Page &[Page] of &[Pages] in the custom header (or footer).
- (Optional) To modify the font, font size, or some other font attribute of your custom header or footer, drag through its
codes and text and then click the Home tab and then click appropriate command button in the Font group.
In addition to selecting a new font and font size for the custom header or footer, you can add bold, italics, underlining, and a new font color to its text with the Bold, Italic, Underline, and Font Color command buttons on the Home tab. - After you finish defining and formatting the codes and text for your custom header or footer, click a cell in the Worksheet
area to deselect the header or footer area.
Excel replaces the header/footer codes in the custom header or footer with the actual information, while at the same time removing the Header & Footer Tools contextual tab from the Ribbon.
View Other Header and Footer Options
- Choose View> Worksheet Views> Page Layout View.
- Choose Header and Footer Tools Design. From the Options group, select any of the options:
Different first page:
For the first time in Excel history, you can now define a header or footer for the first page that's different from all the rest of the pages. Simply, click the Different First Page check box to put a check mark in it. If you choose this option, Excel won't print the header or footer on the first page.
After selecting the Different First Page check box, go ahead and define the unique header and/or footer for just the first page (now marked First Page Header or First Page Footer) and then, on the second page of the report, define the header and/or footer (marked simply Header or Footer) for the remaining pages of the report.
Use this feature when your spreadsheet report has a cover page that needs no header or footer. For example, suppose that you have a report that needs the current page number and total pages centered at the bottom of all pages except the cover page. To do this, select Different First Page check box on the Design tab of Header & Footer Tools contextual tab on the Ribbon and then define a centered Auto Footer that displays the current page number and total pages (Page 1 of ?) on the second page of the report, leaving the Click to Add Footer text intact on the first page.
Excel will correctly number both the total number of pages in the report and the current page number without printing this information on the first page. So if your report has a total of six pages (including the cover page), the second page footer will read Page 2 of 6; the third page, Page 3 of 6; and so on, even as the first printed page has no footer at all.
Different odd and even pages:
If you plan to do two-sided printing or copying of your spreadsheet report, you may want to define a one header or footer for even the pages and another for odd pages of the report. That way, the header or footer information (such as the report name or current page) alternates from being rightaligned on the odd pages (printed on the front side of the page) to being left-aligned on even pages (printed on the back side of the page).
To create an alternating header or footer for a report, you click the Different Odd and Even Pages check box to put a check mark in it.
After that, create a header or footer on the first page of the report (now marked Odd Page Header or Odd Page Footer) in the third, right-aligned section header or footer area and then recreate this header or footer on the second page (now marked Even Page Header or Even Page Footer), this time in the first, left-aligned section.
Scale with document: This option is selected by default and tells Excel to use the same font size and scaling as the worksheet. If you want the header and footer font size and scaling independent of the worksheet scaling, clear this check box.
Align with page margins: Choose this option to align the header and footer with the left and right margins of the worksheet.