Printable Datasheets: Page Setup, Headers, Footers and Printing

45 min

Eleven pages for a five-page list

The deputy head asked for a printed copy of the S3 marks sheet: 250 students, with columns from Name to Average. The printer produced eleven pages. The last two columns came out alone on pages of their own; only page 1 had the column headings, so nobody could tell which number was which; no page had a number; and the chart was cut in half across two sheets. Nothing was wrong with the data. The worksheet had never been prepared for paper.

A printable datasheet is a worksheet set up so that its printout is complete, readable and labelled. This lesson sets one up and prints it.

Definition

Margins: the blank space between the printed area and the edges of the paper.

Orientation: portrait (taller than wide) or landscape (wider than tall).

Print area: the range of cells that will be printed; the rest of the sheet is left out.

Print titles: rows (or columns) that are repeated at the top (or left) of every printed page, such as the heading row.

Header and footer: text printed in the top and bottom margin of every page, such as a title, the date or the page number.

Scaling: shrinking or enlarging the printout so that it fits a chosen number of pages.

The Page Layout tab

Button	Choices	Use it to
Margins	Normal, Wide, Narrow, Custom Margins…	Fit more on a page (Narrow), or centre the data with Custom Margins → Center on page: Horizontally
Orientation	Portrait, Landscape	Print a wide sheet across the paper
Size	A4, A3, Letter …	Match the paper in the printer (A4 is the usual size outside North America)
Print Area	Set Print Area, Clear Print Area	Print only the selected range
Breaks	Insert Page Break, Remove Page Break	Start a new page at a chosen row or column
Print Titles	Rows to repeat at top; Columns to repeat at left	Repeat the heading row on every page, for example `$1:$1`
Scale to Fit	Width, Height, Scale %	For example Width: 1 page, Height: Automatic
Sheet Options	Gridlines: Print; Headings: Print	Print the cell lines, or the row numbers and column letters

The small arrow at the corner of the Page Setup group opens the Page Setup dialog, which has all of these on four tabs: Page, Margins, Header/Footer and Sheet.

Headers, footers and page numbers

Insert → Header & Footer. Excel switches to Page Layout view and opens the header, which has three boxes: left, centre and right.
Click a box and type, or use the buttons on the Header & Footer tab: Page Number, Number of Pages, Current Date, Current Time, File Name, Sheet Name, Picture.
Each button inserts a code. Typing Page , clicking Page Number, typing of and clicking Number of Pages gives Page &[Page] of &[Pages], which prints as “Page 2 of 5”.
Click Go to Footer to do the same at the bottom. Click any cell to finish, then View → Normal.

Diagram drawn by Ganzaa. The dashed box is the printable area inside the margins; the school name is an example.

Three views and the Print screen

View → Normal: the usual working view. After a preview, dotted lines show where pages break.
View → Page Layout: the sheet drawn as pages, with headers, footers and margins you can click and edit.
View → Page Break Preview: the pages outlined in blue, labelled “Page 1”, “Page 2”. Drag a blue line to move a page break.

File → Print (Ctrl + P) shows the preview on the right; check every page with the arrows at the bottom before printing. The settings on the left, top to bottom:

Copies and Printer (choose Microsoft Print to PDF to make a PDF instead).
What to print: Print Active Sheets, Print Entire Workbook or Print Selection. With a chart selected, this reads Print Selected Chart.
Pages: from … to …
One-sided or two-sided; Collated; Orientation; paper size; Margins.
Scaling: No Scaling, Fit Sheet on One Page, Fit All Columns on One Page or Fit All Rows on One Page.

Then click Print.

Worked example 1: fixing the marks sheet

Columns spilling over. Orientation → Landscape; Margins → Narrow; Scale to Fit → Width: 1 page, Height: Automatic. All the columns now fit across one page.
Headings only on page 1. Print Titles → Rows to repeat at top: click row 1 (Excel writes $1:$1) → OK.
How many pages? In landscape, with the title row repeated, 45 student rows fit on each page. $\frac{250}{45} \approx 5.6$ , so the list needs 6 pages: five full pages and a sixth with $250 - 5 \times 45 = 25$ rows.
Labels. Header: school name (left), “S3 Marks, Term 1” (centre), date (right). Footer: “Page &[Page] of &[Pages]”.
Check. File → Print; step through pages 1 to 6. Eleven pages became six, each one readable on its own.

Worked example 2: printing part of a sheet, and a chart

The class teacher only needs S3 B, rows 52 to 101, columns A to F. Select A52:F101 → Page Layout → Print Area → Set Print Area. Only that range prints until the print area is cleared. (For a one-off, select the range and choose Print Selection instead.)
To print the class chart on its own page, click the chart → File → Print: the setting reads Print Selected Chart and the preview shows the chart filling the page. If the chart is on the worksheet next to the data, use Page Break Preview to make sure no page break cuts through it.

Warning

Before you press Print

Fit Sheet on One Page on a long list shrinks the text until no one can read it. Fit the columns to one page and let the rows run on.
Always look at the preview of every page. Paper, ink and toner cost the school money, and wasted paper is wasted trees.
A forgotten print area makes later printouts mysteriously incomplete. Clear it when you no longer need it.
If the datasheet holds personal data, such as marks or phone numbers, collect the printout at once and do not leave copies on the printer.

GCFLearnFree (LearnFree): Excel page layout and printing: the Print pane, orientation, margins, scaling, print titles and page breaks.

Tip

Using LibreOffice Calc? Format → Page Style… holds the margins, orientation, header and footer. Print titles are under Format → Print Ranges → Edit (Rows to repeat). File → Print Preview shows the pages.

✦

Hands-on: a datasheet ready for the notice board

In a new sheet, make a list of at least 60 rows. Use real class data if your teacher allows it, or fill a sample: in A1:F1 type No., Name, Maths, English, ICT, Average; in C2 type =RANDBETWEEN(20,100) and fill it across to E2 and down to row 61; in F2 type =AVERAGE(C2:E2) and fill down.
Format the list as a table (last lesson). Add a column chart of the first ten students' averages beside it.
Set the orientation, margins and scaling so that every column fits across one A4 page.
Repeat row 1 at the top of every page.
Add a header with your school's name, a title and the date, and a footer “Page X of Y”, using the buttons, not typed numbers.
Use Page Break Preview to make sure no page break cuts the chart.
File → Print: note how many pages the preview shows. Print to PDF (or to paper, if your teacher says so), and check the result against your preview.
Print only the chart, on its own page, using Print Selected Chart.

Practice

Define print area, print titles and scaling.
Give the Page Layout settings you would use for a sheet with 14 columns that spills onto a second page across.
Write the steps to put “Page 1 of 4” style numbering in the centre of the footer.
What is the difference between Print Selection and setting a print area?
A list of 380 rows prints 50 rows on each page, with the heading row repeated. How many pages are printed, and how many rows are on the last page?
Name the three worksheet views and one use of each.
Nshuti chose Fit Sheet on One Page for a 600-row list, and the printout was unreadable. Explain why, and give a better setting.
Your class must print 40 copies of a 3-page datasheet for parents. Evaluate printing one-sided against two-sided and collated against uncollated, and recommend settings.

Show answersHide answers

Print area: the range of cells that will be printed. Print titles: rows or columns repeated at the top or left of every printed page. Scaling: shrinking or enlarging the printout to fit a number of pages.
Orientation: Landscape; Margins: Narrow; Scale to Fit: Width 1 page, Height Automatic (and check the preview).
Insert → Header & Footer → Go to Footer → click the centre box → type “Page ”, click Page Number, type “ of ”, click Number of Pages → click a cell.
Print Selection prints the selected cells once; a print area is saved with the sheet and is used every time the sheet is printed, until it is cleared.
$\frac{380}{50} = 7.6$ , so 8 pages. Last page: $380 - 7 \times 50 = 30$ rows.
Normal: everyday work. Page Layout: see and edit headers, footers and margins. Page Break Preview: see and move page breaks.
Excel shrank all 600 rows onto one page, so the text became far too small. Use Fit All Columns on One Page (Width: 1 page, Height: Automatic) and let the rows run over several pages, with print titles.
Two-sided halves the paper (2 sheets instead of 3 for each copy: 80 instead of 120 sheets). Collated prints each copy in order (pages 1, 2, 3, then again), so copies need no sorting; uncollated prints all page 1s, then all page 2s. Recommend two-sided and collated, after checking the preview, to save paper and time.

1Eleven pages for a five-page list

2The Page Layout tab

3Headers, footers and page numbers

4Three views and the Print screen

5Hands-on: a datasheet ready for the notice board

Eleven pages for a five-page list

The Page Layout tab

Headers, footers and page numbers

Three views and the Print screen

Hands-on: a datasheet ready for the notice board