Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 93 ยท Getting Started

Page Setup, Page Breaks and Printing

Print Excel sheets cleanly: set the print area, use Page Break Preview, insert and move page breaks, repeat title rows, add headers and footers, scale to fit, and export to PDF.

A spreadsheet that looks perfect on screen can print as ten pages with one column spilling onto its own sheet. Excel decides where pages end from the paper size, the margins, the scaling and the page breaks. Once you know the five or six settings that control this, you can make any sheet print as exactly the pages you intend, or export it as a tidy PDF.

Excel cannot run on this site, so the menus here are real Excel and read-only. The TryIt is a JavaScript stand-in that calculates how many pages a table needs for a given paper size, margins and scale, and where the automatic page breaks fall, so you can predict a print job before you print. It is a teaching model with simplified measurements, not Excel's own page layout code.

Where the settings live

Most printing options are on the Page Layout tab (Page Setup group: Margins, Orientation, Size, Print Area, Breaks, Print Titles) and in the Page Setup dialog (click the small arrow at the bottom-right of the group). File > Print (Ctrl+P) shows a live preview and a subset of the same settings. Settings apply per worksheet, so set up each sheet you print.

By default Excel prints every cell that has data or formatting. To print only a part:

  1. Select the range.
  2. Page Layout > Print Area > Set Print Area.

To add another block, select it and choose Add to Print Area (it prints on its own page). Clear Print Area returns to the default. In File > Print > Settings you can also choose Print Selection for a one-off. The print area is saved as a hidden named range called Print_Area, so you can see and edit it in Formulas > Name Manager.

Page Break Preview

Switch to View > Page Break Preview. The sheet turns grey outside the print area, shows Page 1, Page 2 watermarks, and draws the page breaks as lines: dashed blue lines are automatic breaks and solid blue lines are manual breaks you set. Drag a line to move a break, which also changes the scaling to fit. Return to normal with View > Normal.

Inserting, moving and removing page breaks

  • Insert: click the cell below the row and to the right of the column where the break should fall. For a break above row 25, select cell A25 (or the entire row 25). Page Layout > Breaks > Insert Page Break. Selecting a row inserts a horizontal break; selecting a column inserts a vertical one; selecting a cell inserts both.
  • Move: in Page Break Preview, drag the blue line.
  • Remove one: select a cell just below or right of the break, then Breaks > Remove Page Break.
  • Reset all: Breaks > Reset All Page Breaks.

Manual breaks switch off Fit to scaling for that direction, which is a common source of confusion: if "Fit to 1 page wide" seems ignored, look for manual vertical breaks.

Orientation, paper size and margins

  • Orientation: Portrait suits tall lists; Landscape suits wide tables.
  • Size: match your printer (Letter, A4). If a shared workbook prints oddly on a colleague's machine, a different default paper size is often why.
  • Margins: choose Normal, Wide or Narrow, or Custom Margins to set exact values and tick Center on page > Horizontally to center a small table.

Scaling: Fit to pages

Under Page Layout > Scale to Fit you can set Width: 1 page and Height: Automatic, which shrinks the sheet until every column fits across one page and lets it run as many pages down as needed. This is the single most useful print setting for wide tables. In the Page Setup dialog (Page tab) the same idea is Fit to: 1 page(s) wide by (blank) tall; the alternative Adjust to: 85% of normal size sets a fixed scale. Scaling below roughly 60% makes text hard to read, so prefer Landscape or hide unneeded columns instead.

JS How many pages will this table print?

Press Run (or Ctrl + Enter) to see the console output here.

No output.

If a table spans several pages, the header row appears only on page 1 unless you repeat it. Page Layout > Print Titles, then on the Sheet tab set Rows to repeat at top to $1:$1 (click the row header, or type it) and, for wide tables, Columns to repeat at left to $A:$A. Now every page begins with the header. Also on this tab: Gridlines (print cell borders), Row and column headings, Black and white, Comments and the page order (down then over, or over then down).

Headers and footers

Headers and footers hold text that repeats on every page: page numbers, the file name, the date. Use Insert > Header & Footer, or the Header/Footer tab of Page Setup. Each has three sections (left, center, right). Insert codes with the buttons, or type them:

Code Result
&[Page] Page number
&[Pages] Total pages ("Page 2 of 5" is Page &[Page] of &[Pages])
&[Date], &[Time] Print date and time
&[File], &[Tab] File name, sheet name
&[Path] Full path
&[Picture] A logo image

Different first page and Different odd & even pages are checkboxes in the Header/Footer tab. In the header/footer editor you can type a literal ampersand with &&. Headers appear only in Page Layout view and print preview, not in Normal view.

Printing a PDF

Excel can create a PDF without a PDF printer: File > Export > Create PDF/XPS, or File > Save As > PDF. In Options choose what to publish: Active sheets, Entire workbook or Selection. Standard quality is for sharing; Minimum size is for email. The PDF follows the same print areas, breaks, titles and scaling, so check Page Break Preview first. Alternatively choose Microsoft Print to PDF as the printer in File > Print.

A print checklist

  1. Set the print area to the range readers need.
  2. Choose orientation and paper size.
  3. Use Fit to 1 page wide for tables.
  4. Set print titles for the header row.
  5. Add a footer with page numbers.
  6. Check Page Break Preview and File > Print preview, then print or export.

The project lesson Invoice Template uses these settings, and Saving and Sharing covers other export formats.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. To insert a horizontal page break above row 25, what should you select?

    A page break goes above and to the left of the selected cell. Selecting row 25 (or cell A25 for a vertical and horizontal pair) inserts the break above row 25.
  2. 2. What do you set so a table's header row appears at the top of every printed page?

    Freeze Panes affects the screen only. Print Titles repeat chosen rows (or columns) on every page of a print job.
  3. 3. Which setting prints all columns on one page width and lets the length run as needed?

    Setting Width to 1 page and Height to Automatic shrinks the sheet until all columns fit across and leaves the number of pages down unrestricted.
  4. 4. What does the footer code Page &[Page] of &[Pages] print?

    is the current page number and &[Pages] the total, so the footer reads like Page 2 of 5.

Summary

  • Settings are per worksheet: Page Layout tab, Page Setup dialog and File > Print.
  • Set Print Area limits what prints; Page Break Preview shows automatic (dashed) and manual (solid) breaks you can drag.
  • Insert a break above and to the left of the selected cell; Reset All Page Breaks removes manual ones.
  • Scale to Fit with Width 1 page is the best fix for wide tables; Print Titles repeat the header row.
  • Headers and footers use codes such as &[Page] and &[Pages]; Save As > PDF exports with the same layout.

Next: Excel Templates, Quick Analysis, Autofill and Small Time-Savers.