Excel to PDF: Print Area, Scaling and Page Breaks That Work

Six steps to a PDF from the desktop app

Microsoft gives the route as File > Save As (or Save a copy), type a name, then open the Save as type list and choose PDF (*.pdf). Ctrl+P opens the print settings if you prefer to check them first. Next pick 1 of the 2 quality settings, Standard or Minimum size, select Options if you want to change what is exported, then select Save.

• Standard is labelled publishing online and printing. Choose it when print quality matters.
• Minimum size is labelled publishing online. Choose it when file size matters more than print quality.
• A check box opens the PDF straight after saving.

The PDF only holds what Excel would print

A PDF export follows the same rules as printing. Excel prints only defined print areas, so if a sheet has one, the PDF holds that area and nothing else. To get the full sheet, choose Ignore print area in the print settings, or clear the print area.

Under Settings, the dropdown that reads Print Active Sheets controls what goes out. In Microsoft's VBA reference the same switches exist as parameters of ExportAsFixedFormat: IgnorePrintAreas, From and To for page numbers, and Quality with 2 values, xlQualityStandard and xlQualityMinimum. The reference page lists 9 parameters in total, and its example saves sales.pdf at standard quality.

A worked example: 14 columns that print on 3 pages

Say a report runs from column A to column N, 14 columns, and the PDF comes out as 3 pages across with the last 2 columns alone on page 3. Three changes fix it.

Step 1: select A1 to F40, which is 6 columns and 40 rows, for the part readers need and use Page Layout > Print Area > Set Print Area. Microsoft's example is printing columns A through F and leaving out G through Z. Step 2: on the Page Layout tab, in Scale to Fit, set Width to 1 page and Height to Automatic. Columns now fit on one page, while rows may run to more pages. Step 3: if the text is too small, check the Scale box, switch to landscape under Orientation, or choose a larger paper size.

Setting Height to 1 page as well squeezes all 40 rows and 6 columns onto 1 page. Microsoft warns that the result may be hard to read because Excel shrinks the data, and that a low number in the Scale box means you should change orientation or paper size.

Adjust to a percentage, or Fit to a page count

Excel has 2 scaling modes in the Page tab of Page Setup (Alt+P, S, P). Adjust to takes a percentage of normal size: a value below 100% shrinks, and a value above 100% enlarges. A setting of 100% leaves the size unchanged. Fit to takes a number of pages wide and tall instead.

Microsoft does not state a minimum or maximum percentage on the scaling page, so check Print Preview after each change, for example after trying 80%. Excel will not stretch data to fill the pages either. To undo all scaling, go to File > Print > Settings > No Scaling.

Fit to cancels your manual page breaks

This is the surprise that wastes the most time. Microsoft states that Excel ignores manual page breaks when Fit to is used. If you insert a break and nothing changes, check whether Fit to is on. Switch to Adjust to with a percentage and the breaks work again.

To insert a break, open View > Page Break Preview, select the row below where the break should go (or the column to the right of it), then choose Page Layout > Page Setup > Breaks > Insert Page Break. Dashed lines mark the automatic breaks in the normal view. For a break after row 20, select row 21 first.

Moving, removing and resetting breaks

In Page Break Preview you can drag a break line to a new place. Moving an automatic break turns it into a manual break, and manual breaks are not adjusted automatically afterwards. Microsoft says you can remove manual page breaks but you cannot remove automatic ones.

Select the row below, or the column to the right, and choose Breaks > Remove Page Break. For a clean start use Breaks > Reset All Page Breaks. To change where automatic breaks fall, change column widths, margins or the scale. Microsoft's page for this topic is written for Excel for Mac, so labels may differ slightly on Windows.

Frozen panes and Page Layout view do not mix

Microsoft notes that Page Layout view is not compatible with Freeze Panes. If you need both, use the Fit to options in the Page Setup dialog and leave Page Layout view off.

Also check the paper size in your printer properties. If it differs from the size set in Excel, the output is adjusted and may not match Print Preview. A Print Preview that shows the sheet reduced to one page usually means Fit Sheet on One Page is switched on.

What a PDF loses and what it does not protect

Internal links inside the workbook are lost during conversion, so check all of them afterwards. Links to outside sources still work. Power View sheets cannot be saved as PDF at all.

A PDF also does not guarantee that the reader cannot edit it, because many programs, including Word, can open and edit PDFs. Microsoft's file format list says only that data in a PDF cannot be easily changed.

Excel for the web differs: it supports 1 print area only, and hidden rows and columns do not print. For several print areas, open the desktop app.

Who the desktop route suits

The desktop route suits reports, invoices and price lists that must look the same on every screen. For a sheet that is still changing daily, keep the .xlsx and export a PDF only when you send it.

If a print setting on screen does not do what you expect, Crade can read the dialog and explain which option it is.