Excel Tutorials
Excel prints badly by default, spilling columns across pages, dropping the header after page one and leaving gridlines off entirely. Every one of those is a setting, not a limitation. This lesson goes through page size, printing gridlines, print areas, margins, scaling, page breaks and repeating headers in the order you should actually set them, using the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Word documents know how wide a page is. A spreadsheet does not: it is a grid that runs on effectively forever in both directions, and printing forces Excel to decide where to cut it. Left alone it cuts at whatever the current paper size allows and pushes the remainder onto more sheets, which is how a tidy fifteen-column table becomes nine pages with three columns stranded on the last one.
On top of that, several things you would expect to be on are off. Gridlines do not print unless you tick a box, so a sheet that looks like a table on screen arrives as floating numbers. Header rows do not repeat on page two, because Freeze Panes only affects the screen. And anything parked off to the side, helper columns, scratch notes, gets printed along with everything else unless you define a print area.
The fix is a sequence rather than one button. Set the page size first, because it dictates everything else, then orientation, then what to print, then how it looks, and only reach for scaling if it is still wrong. Done in that order it takes about a minute. Done backwards it feels like Excel is fighting you, because each change undoes the last one.
Step-by-step, matching the video above.
Ctrl+P opens the Print screen, with every core setting down the left and a live preview on the right. Do not print from here on the first attempt, use it to see the damage. The page count under the preview tells you immediately whether the sheet is about to spill across nine sheets of paper.
View > Page Layout shows the sheet as pages, with margins, headers and footers visible while you edit. It is the fastest way to understand where your content actually falls. View > Page Break Preview goes further, drawing blue lines at each page break that you can drag to move.
On the Page Layout tab, click Size. Choose Letter (8.5 x 11 in) in the US and Canada, or A4 (210 x 297 mm) most other places. Changing this later re-flows every page break you have set, which is why it comes first.
Page Layout > Orientation. Portrait suits tall lists, landscape suits wide tables. Most spreadsheets that print badly are wide tables stuck in portrait, and switching to landscape solves it before any scaling is needed.
Select the range, then Page Layout > Print Area > Set Print Area. Excel now ignores everything outside it, including working notes and helper columns off to the side. Use Clear Print Area to remove it. You can add non-adjacent ranges with Add to Print Area, though each one prints on its own page.
In the Sheet Options group on the Page Layout tab there are two small columns of checkboxes, Gridlines and Headings, each with View and Print. Tick Print under Gridlines. This is off by default, which is why a sheet that looks like a table on screen prints as floating text.
Page Layout > Margins offers Normal, Wide and Narrow. Narrow buys you noticeably more room on a wide table. Choose Custom Margins and on the Margins tab tick Horizontally and Vertically under Center on page so a small table sits in the middle instead of the top-left corner.
In the print settings, No Scaling can become Fit Sheet on One Page, Fit All Columns on One Page or Fit All Rows on One Page. Fit All Columns is usually the right one: it controls the width, which is the actual problem, and lets the rows run to as many pages as they need. Fit Sheet on One Page on a 400-row list produces text nobody can read.
Page Layout > Print Titles, then in Rows to repeat at top enter $1:$1. Every page now carries the header. This is a separate setting from Freeze Panes, which only affects the screen, and it is the single fix that makes a multi-page printout usable.
Insert > Header & Footer, or the Header/Footer tab of Page Setup. Presets cover page numbers, Page X of Y, file name, sheet name and date. Anything here prints on every page without occupying cells on the sheet.
Return to Ctrl+P and page through the whole preview rather than glancing at page one. Confirm the page count, that headers repeat, and that no column has been orphaned onto a page by itself.
The most common printing complaint in Excel, and the one with the most moving parts.
Gridlines are the faint grey lines Excel draws to help you see the grid. They belong to the sheet and are never truly part of your data. Borders are formatting you apply to specific cells, and they always print. If you need control over line weight, colour or which edges show, use borders, not gridlines.
Page Layout > Sheet Options has Gridlines with a View box and a Print box. View is screen only. Print is paper only. They are completely independent: unticking View hides them while you work but still prints them if Print is ticked, which is a genuinely useful combination for a clean-looking sheet that prints as a readable table.
Click the small arrow at the bottom-right of the Page Setup group, go to the Sheet tab, and there is a Gridlines checkbox alongside Row and column headings, Comments and Cell errors. It controls exactly the same thing. Changing it in one place changes it in the other.
If you have set a print area, gridlines appear only around cells within it. Empty cells outside get nothing, which is normally what you want but confuses people expecting a full page of grid.
Gridlines are drawn under cell fill. Applying a white fill, which looks identical to no fill on screen, covers them completely on the printout. If gridlines are on but not printing for a specific block of cells, check for a fill: select the range and choose No Fill from the fill colour dropdown.
On the Sheet tab of Page Setup, Draft quality speeds up printing by skipping gridlines and most graphics. If gridlines are ticked and still not appearing, this is the setting to check next.
File > Options > Advanced lets you change gridline colour for the sheet, but printed gridlines always come out as a light grey regardless. To control the printed line colour you need borders.
The Headings Print box adds the column letters and row numbers to the printout. Rarely wanted on a report going to a client, extremely useful on a working copy you are checking formulas against.
The setting everything else depends on, and the one most likely to be wrong on a shared file.
Page Layout > Size, or the Page tab of the Page Setup dialog, where it appears as Paper size. The list is generated by your currently selected printer driver, so it changes depending on which printer is chosen.
The default in the United States and Canada. Slightly wider and shorter than A4, which is why a sheet built for Letter loses a row or two at the bottom when printed on A4.
The standard nearly everywhere else, and roughly 8.27 x 11.69 inches. Narrower and taller than Letter, so a table sized to fit Letter's width will spill a column when printed on A4.
Same width as Letter with three extra inches of length. Good for long lists where you want fewer page breaks without shrinking anything, assuming the printer has a Legal tray.
Tabloid is 11 x 17 inches and A3 is 297 x 420 mm. If a table genuinely will not fit and scaling makes it unreadable, larger paper beats a smaller font, provided your printer supports it.
Changing paper size re-flows every automatic page break and invalidates any manual break you have positioned. Set it first. Changing it at the end means redoing the page break work.
A workbook built on Letter and opened somewhere that defaults to A4 will re-paginate, and the person at the other end sees split columns you never saw. Setting the size explicitly rather than relying on the default avoids it.
More Paper Sizes at the bottom of the Size list opens Page Setup, but any genuinely custom dimension has to be defined in the printer driver's own properties first.
| Problem | Setting to Change | Result |
|---|---|---|
| Prints across 6 pages | Orientation → Landscape | Down to 2 pages |
| Two columns spill over | Scaling → Fit All Columns on One Page | Width fits, rows continue on page 2 |
| No table lines on paper | Sheet Options → Gridlines → Print | Grid prints around all data |
| Page 2 has no header | Print Titles → Rows to repeat: $1:$1 | Header on every page |
| Notes column printing | Set Print Area on A1:F40 | Only the budget prints |
| Table stuck top-left | Margins → Center horizontally | Centred on the page |
| Blank page at the end | Ctrl+End, delete stray rows | Page count drops by one |
Each row is one setting, and the order is the order to try them in. Notice that scaling comes fourth, not first. Landscape alone took this from six pages to two, and Fit All Columns finished the job without shrinking the rows. Reaching straight for Fit Sheet on One Page would have compressed all 40 rows onto a single sheet at a font size nobody could read, which is the most common way a printout gets ruined.
Controlling exactly where one page ends and the next begins.
View > Page Break Preview draws solid blue lines for manual breaks and dashed blue lines for automatic ones, with a faint page number watermark on each page. Drag any line to move the break.
Moving an automatic break outward does not add space, it applies scaling to squeeze more onto the page. Check the Scale percentage on the Page tab of Page Setup afterwards, because it can quietly end up at something like 62%.
Select the row below where you want the split, then Page Layout > Breaks > Insert Page Break. Select a column to break vertically instead. Useful for starting each department or category on its own page.
Breaks > Reset All Page Breaks clears every manual break and returns to Excel's automatic pagination. Worth doing before re-laying-out a sheet somebody else has already fought with.
The Sheet tab of Page Setup has Down, then over and Over, then down. On a sheet that is both wide and long this decides whether page 2 is the next set of rows or the next set of columns.
Page Setup's Sheet tab holds several options that exist nowhere else on the ribbon.
Choose At end of sheet or As displayed on sheet. The default is None, so annotations never print unless you ask for them.
Cell errors as lets you print #N/A and #DIV/0! as blank, as a double hyphen, or as #N/A. Printing errors as blank is the quick way to make a report presentable without touching the formulas.
Prints coloured fills and fonts as plain black and white, which is often more legible on a monochrome printer than the greyscale conversion it would otherwise apply.
The horizontal counterpart to repeating a header row. Enter $A:$A and the labels in column A appear on every page across, so a wide table stays readable past page one.
They do not. The Print box under Gridlines in Sheet Options is off until you tick it, which is why a sheet that looks like a neat table on screen prints as unanchored text.
The two checkboxes sit next to each other and do completely different things. View affects your screen only. Print is the one that reaches the paper.
On anything longer than about 40 rows this shrinks the text past readability. Fix the width with landscape orientation or Fit All Columns on One Page, and let the rows run onto more pages.
Changing the paper size re-flows every break and undoes the scaling work. Page size is the first decision, not the last.
Freeze Panes is a screen feature and has no effect on printing. Repeating a header needs Print Titles with $1:$1 in Rows to repeat at top.
Helper columns, scratch calculations and notes parked off to the right all print unless you restrict the print area, usually as extra pages nobody wants.
White fill and no fill look identical on screen but behave differently on paper, because fill covers gridlines. Set the range to No Fill if gridlines are on but missing from a block of cells.
One space in a far-off cell extends the used range and adds empty pages. Ctrl+End jumps to the last used cell, then delete the surplus rows and columns and save.
Page one almost always looks fine. The orphaned column and the missing header show up on page three, which is where the paper gets wasted.
Half an hour of page setup is what happens when the only way to share something is to print it.
Free 14-day trial. No credit card required.
Gridlines are a screen aid and are switched off for printing by default. Tick Print under Gridlines in the Sheet Options group on the Page Layout tab, or open Page Setup and tick Gridlines on the Sheet tab.
View controls whether you see gridlines on screen and has no effect on paper. Print controls whether they appear on the printout. They are independent, so you can hide them on screen and still print them, or the reverse.
Gridlines only print around cells inside the print area, and only where a cell has no fill colour. A white fill applied to cells hides the gridlines beneath them on paper even though the setting is on.
Go to Page Layout, then Size, and pick from the list. Letter, A4, Legal and Tabloid are the common options, and the list reflects what your selected printer actually supports.
Letter at 8.5 by 11 inches is standard in the US and Canada, while A4 at 210 by 297 mm is standard almost everywhere else. Legal at 8.5 by 14 inches gives extra length for tall reports, and Tabloid at 11 by 17 suits wide tables.
The columns are wider than the page, so Excel spills the extras onto separate sheets. Switch to landscape, set a print area, or use Fit All Columns on One Page under Scaling.
In the print settings, change No Scaling to Fit Sheet on One Page. Check the preview afterwards, because on a long sheet this can shrink the text past the point of being readable.
Page Layout, then Print Titles, then in Rows to repeat at top enter $1:$1. The header then reprints at the top of every page, which Freeze Panes does not do.
Select the range you want, then choose Page Layout, Print Area, Set Print Area. Excel then prints only that range until you clear it.
Yes. In the Sheet Options group on the Page Layout tab, tick Print under Headings. That adds the A, B, C column letters and 1, 2, 3 row numbers to the printout, which is useful for checking formulas.
Insert, then Header and Footer, then use the Page Number button. Page X of Y is available as a preset in the footer dropdown.
Open Page Setup, go to the Margins tab, and tick Horizontally and Vertically under Center on page. A narrow table then sits in the middle rather than hugging the top left corner.
On the View tab, in the Workbook Views group. It shows blue lines marking each page break, and you can drag them to change where pages split.
Highlight the cells, press Ctrl+P, then change Print Active Sheets to Print Selection. This prints just that range without setting a permanent print area.
Something exists in a distant cell, often a stray space or leftover formatting, extending the used range. Press Ctrl+End to find the last used cell, delete the empty rows and columns beyond your data, and save.
Press Ctrl+P and choose Microsoft Print to PDF as the printer, or use File, Export, Create PDF/XPS. Every page setup option still applies, so set them first.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →