How to Set Headers and Footers for Printouts

Formatting an Excel table for printing it out is usually troublesome. You have to adapt the print area, select rows and columns to repeat and set the headers and footers. The problem: In most cases, you can’t apply the print layout to several sheets at the same time as it easily overrides your previous formatting. In this Excel Tip, we’ll have a look at setting headers and footers.

Please also take a look at our comprehensive guide for printing Excel sheets.

Method 1: Change the headers and footers manually

The conventional method is setting the headers and footers manually. Therefore, follow these steps (the numbers are corresponding with the image on the right hand side):

1. In the Page Layout ribbon, click on the tiny grey arrow at the bottom right corner of the Page Setup group.
2. Go to the “Header/Footer” tab.
3. Click on “Custom Header…”.
4. Excel provides the important functions as buttons: Page numbers, total number of pages, date and time, file path, workbook name and worksheet name.
5. If you want to show the date on the left section, click into the text field and then click on the date button above.

Instead of preparing each sheet separately, you can mark all worksheets and format them at once. Unfortunately, there are two major disadvantages with this method:

• Existing formatting – as page orientation or margins – will be overwritten if you try to apply the new settings to several sheets simultaneously. Especially if you’ve already formatted all other worksheets and only want to update the headers and footers you have to go through all sheets manually.
• Excel doesn’t save your last header and footer settings. In many cases, you already have your own template like date in one corner, file name in another and so on. Every time you have to repeat the steps if you format a new worksheet.

Method 2: Use Professor Excel Tools for changing the headers and footers

For solving the disadvantages of the first method, the Excel add-in ‘Professor Excel Tools’ comes with a function for setting and updating comfortably. You can update or set printout headers and footers for all worksheets (or just selected worksheets) at the same time. Also, the last settings are saved.

To conclude: With only two clicks you can set headers and footers for the complete workbook in your own style.