11. Headers, Footers and PDF Export

A report that goes to a manager or a client almost always ends up as a PDF. To make it look neat, set the sheet up for printing first.

Views (the View tab or the icons at the bottom right): Page Layout view shows sheets of paper with margins, headers and footers, and Page Break Preview shows blue page-break lines that you can drag with the mouse.

The Page Layout tab:

• Print Area › Set Print Area prints only the selected range and leaves out scratch calculations at the side;
• Orientation is Portrait or Landscape (wide tables need Landscape);
• Scale to Fit — Width: 1 page, Height: Automatic, so the columns do not spill onto extra pages;
• Print Titles › Rows to repeat at top, for example $1:$1, repeats the row with column names on every page;
• Breaks › Insert Page Break starts a new page at the selected row.

Headers and footers are text at the top and bottom of every page: Insert › Header & Footer, or double-click the margin in Page Layout view. A header or footer has three sections: left, center and right. Instead of typing text by hand, you insert fields that Excel fills in itself:

• &[Page] and &[Pages] give "Page 2 of 5";
• &[Date] and &[Time] show when the sheet was printed;
• &[File], &[Tab] and &[Path] show the workbook name, the sheet name and the folder;
• you can also set a "Different First Page" and different headers and footers for odd and even pages.

Saving as PDF: File › Export › Create PDF/XPS Document, or File › Save As and choose PDF as the type. Under Options, pick what to export: the selection, the active sheets or the entire workbook. On Mac, use File › Save As › File Format: PDF. Before exporting, check the result in File › Print (Ctrl+P), which shows how many pages you will get.

The trainer has no printing, headers or PDF export, so this topic is covered by the quiz. Set up any report of your own for printing in real Excel by following the steps above.