The fourteen-page pack that prints wrong
A 14-page monthly pack lives in one worksheet. The summary occupies A1:F40 and the detail table A42:H900. You must send the client a PDF containing the summary and only the first 200 detail rows, with the sheet title row repeating on every page and 'Page n of m' bottom right. The last attempt went wrong: the summary pushed column F onto a second page, every page footer read 'Page 1 of 1', and faint gridlines printed behind a table that already had borders. The detail body text is 8 pt, so anything that shrinks it further is unacceptable. The client prints on A4; your office printer defaults to Letter.
1. Describe how you would restrict the output to the required content, and the order in which you would try remedies for the spilled column - explaining why one obvious remedy comes last.
Read the worked answer
Restrict the content. Select A1:F40, then Ctrl-select A42:H240, and apply Page Layout > Print Area > Set Print Area. Everything outside prints as nothing, including the rest of the detail table. Note the consequence: each area of a multi-area print area starts on a new page, so the summary and detail will not flow together. If the client wants them continuous, set one area covering both and hide the unwanted detail rows instead.
Fix the spill, cheapest first:
- Narrow column F, or reduce its indent or font size by one step.
- Trim left and right margins by a few millimetres.
- Switch the sheet to Landscape.
- Only then, set Scale to about 90%.
Why Fit To comes last. 'Fit To 1 page wide' is the reflex fix and it is banned here: it shrinks everything on the printout, taking 8 pt body text to something unreadable. Fit To also overrides the Scale percentage - setting one clears the other - so it must be a deliberate choice, not a click of desperation. Preview with Ctrl+P after each change.
2. How do you make a heading repeat on every page and a footer read 'Page 3 of 12', and why does the current file say 'Page 1 of 1' throughout?
Read the worked answer
Repeating rows. Page Layout > Print Titles > Rows to repeat at top: $1:$1. Constraints worth knowing: the reference must be a whole row, it must sit at the top of the sheet, and it applies only to the sheet it was set on - every other sheet needs its own setting. A title stranded in the middle of the detail table cannot be repeated; move it into the page header instead.
Footer. Insert > Header & Footer, go to the right section, click Page Number, type ' of ', click Number of Pages. This writes the fields &[Page] of &[Pages], and they update at preview and print time only - the sheet itself still shows the codes.
Why 'Page 1 of 1' everywhere. Someone typed those words as literal text (or pasted a footer from a single-page document), so every page faithfully prints the same sentence. Replace the literal text with the fields.
Extra check. Because the client prints on A4 and your printer defaults to Letter, set the paper size in Page Setup rather than relying on the printer dialog.
3. Gridlines are printing behind a table that already has borders. Explain what gridlines are, how to stop them, and what else you would verify before exporting the PDF.
Read the worked answer
What they are. Gridlines are an on-screen navigation aid, not cell formatting. They are not part of the data, cannot be styled per cell, and do not travel with copied cells. They print only when Page Layout > Sheet Options > Print (under Gridlines) is ticked, which is what has happened here - often inherited from a template.
Fix. Untick Print gridlines and leave the View box ticked so editing stays comfortable. Rely on the borders already designed into the table: borders are real formatting and always print, so the printed result loses nothing but gains legibility.
Before exporting:
- Confirm Print Area covers exactly what the client should see.
- Confirm Print Titles and the footer fields are in place.
- Set paper size to A4 in Page Setup and check the scaling mode.
- Use File > Export > Create PDF/XPS and check that 'Print area' is selected rather than Entire workbook or Active sheets.
- Open the exported PDF and look at it. Font substitution, lost margins and orphaned columns show up there first, and that file is what the client actually receives.