Print Area: The Printed Invoice Listing Was Thirteen Rows Long, the File Was Twenty, and Every Customer Still Appeared on It

The pack went out on the Monday: a summary page, an invoice listing, and a covering note. The summary page said £175,698.65 of invoices raised in the quarter across seven customers, which was right to the penny. The invoice listing behind it had thirteen rows on it, and the file it came from had twenty.

The last line of that table is why the pack went round a committee, got read, got discussed and got filed without anybody stopping. Every customer in the book appears on the printed listing. Nothing is missing in the way people check for missing — no name has vanished, no row reads zero, no cell is blank, no formula has errored. Each of the seven customers is simply reported at part of itself, and the parts are all plausible.

Nothing in Excel misbehaved. The print area on that sheet is $A$1:$F$14. It was set in June, when the data genuinely ended at row 14. A print area is a fixed rectangle of cells, and when seven more invoices were appended below it, it did what a fixed rectangle does.

What this covers. Print areas, print titles, scaling, page order, headers and footers behave the same way in Excel 2016, 2019, 2021, Microsoft 365 and Excel for Mac; Excel for the web can print but cannot set a print area, which matters in section 11. SUM, COUNTA, ROWS, INDEX, SUBTOTAL, SUMIFS, COUNTIFS, MAX, LARGE and CELL in the checking sections work in every version. An invoice listing is only the example: a payroll register, a stock count, a fee schedule, a board appendix — anything that gets printed, signed, sampled or filed is the same job, and every trap below applies unchanged.

1) The Listing Was Not Truncated. It Was Complete, and Short.

Here is what each customer was invoiced in the quarter, and what the paper said:

Seven names on the sheet, seven names on the paper. Not one of them is right.

This is the shape of failure that survives review. A listing with a customer missing gets caught, because somebody knows the customer exists and looks for them. A listing where every customer is present at roughly half is a listing that reads as a listing — it has the right names in the right order with plausible numbers beside them, and the only way to catch it is to compare a total nobody computed against a total nobody printed.

The invoices are appended in date order, which is what turned a truncation into an understatement:

=SUM(F2:F21)              → 175,698.65   everything raised in the quarter
=SUM(F2:F14)              →  93,940.40   everything inside the print area
=SUM(F15:F21)             →  81,758.25   everything below it
=MAX(F2:F21)              →  18,975.00   INV-2060, row 21, outside
=LARGE(F2:F21,2)          →  16,325.00   INV-2055, row 16, outside

The two largest invoices in the book are both below the cut, because the business grew across the quarter and new rows go at the bottom. A print area set once and left alone does not drop a random 46.53% of a file. It drops the most recent 46.53%, which on almost every real sheet is the part that changed, the part that matters, and the part somebody is waiting on.

🎯 Scenario: Before you send a printed or PDF'd listing anywhere, put =COUNTA(A2:A1000) beside =COUNTA(A2:A14) — the file count against the printed count — in two spare cells and read them. If those two numbers differ, the pack is wrong and you know it in four seconds, which is three days earlier than the person who reconciles it.

2) Print_Area Is a Defined Name, So You Can Ask It What It Will Print

This is the part almost nobody knows, and it turns the whole problem from invisible to checkable.

When you set a print area, Excel does not tick a hidden setting. It creates a defined name called Print_Area, scoped to that worksheet, whose Refers to is the range you selected. It is in Formulas ▸ Name Manager with everything else you have ever named. You can look at it, edit its Refers to box, and — the useful part — point a formula at it.

=ROWS(Print_Area)                 → 14          rows that will print, header included
=COLUMNS(Print_Area)              → 6           columns that will print
=COUNTA(INDEX(Print_Area,0,1))-1  → 13          invoices that will print
=SUM(INDEX(Print_Area,0,6))       → 93,940.40   money that will print
=COUNTA(A2:A21)                   → 20          invoices that exist
=SUM(F2:F21)-SUM(INDEX(Print_Area,0,6))
                                  → 81,758.25   money that will not

INDEX(Print_Area,0,6) is the trick worth keeping: a row argument of 0 means the whole column, so INDEX(range,0,6) hands you the sixth column of whatever the print area currently is. Change the print area and every one of those cells updates on the next calculation. That is a live audit of the paper, sitting on the sheet, costing one cell each.

From another sheet, qualify the name with the sheet it belongs to — =ROWS(Invoices!Print_Area) — because Print_Area is worksheet-scoped and every sheet has its own. And if there is no print area on a sheet at all, the name does not exist and these formulas return #NAME?, which is itself the answer: nothing has been fixed in place, and Excel will print the used range instead. Section 4 is about what that means.

🎯 Scenario: Put =ROWS(Print_Area)&" rows, "&TEXT(SUM(INDEX(Print_Area,0,6)),"#,##0.00")&" will print" in a cell above the data on any sheet that gets printed regularly. It reads as a sentence, it is right by construction, and it is the only thing on the sheet that knows what the printer is about to be told.

3) Why =SUM(Print_Area) Returns 694,473.40

Try the obvious formula on this sheet and you get a number that is not the money:

=SUM(Print_Area)              → 694,473.40
=SUM(INDEX(Print_Area,0,6))   →  93,940.40

The difference is 600,533, and it is the Issued column. The print area is $A$1:$F$14 — a rectangle six columns wide — and column D holds thirteen real dates. A real date in Excel is a number: 2 June 2026 is the serial 46175, and thirteen serials in the forties of thousands add up to 600,533 all on their own. SUM ignores the text in A, B, C and E, because SUM ignores text. It does not ignore dates, because dates are not text.

This is not a curiosity about printing. It is the thing that makes the whole "just sum the block" habit unsafe on any sheet that has a date column in the middle of it, and it is why the checks in section 2 name a column instead of summing a rectangle.

=SUM(A1:F14)                  → 694,473.40   the rectangle
=SUM(F2:F14)                  →  93,940.40   the column
=SUMIFS($F$2:$F$14,$E$2:$E$14,"Open")
                              →  37,647.90   the column, conditioned
=COUNTIFS($E$2:$E$14,"Open")  →       4      how many of them

If a number ever comes back an order of magnitude too large from a block that looks like money, count the columns it crossed and look for a date.

🎯 Scenario: Any time you sum something you did not choose the boundaries of — a print area, a named range somebody else defined, a range fed in from another sheet — sum one column of it with INDEX(range,0,n) rather than the whole thing. The rectangle is a shape; the column is the thing you meant.

4) With No Print Area, Excel Prints the Used Range

Clearing the print area — Page Layout ▸ Print Area ▸ Clear Print Area — is very often the right fix, and it is worth knowing precisely what you are switching to, because it is not "the data".

With no print area, Excel prints the used range: from A1 to the bottom-right corner of everything the sheet believes it is using. That corner is where Ctrl+End takes you, and it is not always where the data stops. A sheet that once had four thousand rows pasted into it and cleared with the Delete key still counts those rows as used, because Delete removes contents and leaves the cell formatted and in play. So Ctrl+End lands on F4000, the preview says 87 pages, and 85 of them are empty grids with a page number in the footer.

The repair is mechanical and takes ten seconds:

1. Ctrl+End and see where you actually land.
2. If it is below your data, select every row from the first empty one to the last one Ctrl+End reached — click the row header, then Ctrl+Shift+↓.
3. Right-click ▸ Delete (delete the rows, not their contents).
4. Do the same for columns to the right.
5. Save the file. The used range is recalculated on save, and until you save, Ctrl+End will keep going to the old corner.

That last step is the one people skip, and it is why the trick has a reputation for not working.

Ctrl+End before   → F4000
Ctrl+End after    → F21
Preview before    → 87 pages
Preview after     → 1 page

🎯 Scenario: Press Ctrl+End on every sheet in a workbook you are about to send, print or PDF. It takes one keystroke per sheet and it tells you the size of the thing you are about to produce, which nothing else on screen will.

5) Print Titles: the Page Where the Columns Have No Names

A twenty-row listing prints on one page and this never comes up. A four-hundred-row listing prints on nine, and pages 2 to 9 arrive with no headings on them at all — just columns of numbers whose meanings are on a sheet of paper somebody else is holding.

Page Layout ▸ Print Titles ▸ Rows to repeat at top, then click row 1. Excel stores it as another built-in defined name, Print_Titles, referring to $1:$1, and every printed page from then on carries the header row. If the header is two rows deep, use $1:$2. For a listing wider than a page, Columns to repeat at left does the same job sideways, which is what keeps the invoice number beside the numbers when column F lands on page two.

The cost of not doing it is not aesthetic. On this listing, columns D, E and F are a date, a word and a number. On page four of a nine-page pack, a column of numbers with no heading gets read as whatever the reader is expecting, and Amount and a VAT column and a running balance all look identical at arm's length.

Page Layout ▸ Print Titles ▸ Rows to repeat at top   $1:$1
Page Layout ▸ Print Titles ▸ Columns to repeat left  $A:$A
Name Manager                                          Print_Titles

Two things that catch people out. Print Titles is greyed out when you open it from inside the Print Preview dialog — set it from the Page Layout tab instead. And repeating rows is not the same as freezing them: Freeze Panes fixes the headings on screen and does nothing to the printout, Print Titles fixes them on paper and does nothing to the screen. Most sheets want both, and setting one tells you nothing about the other.

🎯 Scenario: If a listing is more than one page long, set Print Titles before you set anything else. It is the single highest-value page-setup change available, it takes four clicks, and it is the only one whose absence is invisible on screen and obvious on paper.

6) Filtered and Hidden Rows Print Nothing and Total Everything

Set the print area aside for a moment; this is the same failure through a different door, and it is the one that produces a printed page that contradicts itself.

Excel does not print hidden rows, and it does not print rows filtered out by AutoFilter. It does print every formula on the sheet, evaluated over everything. So filter this listing to Status = Open and print it:

• the page shows seven rows, which are the seven open invoices;
• a total cell built as =SUM(F2:F21) prints 175,698.65, because SUM has no idea anything is hidden;
• and the two numbers are on the same piece of paper, four centimetres apart.

=SUM(F2:F21)              → 175,698.65   every row, hidden or not
=SUBTOTAL(109,F2:F21)     →  80,560.25   the rows you can see
=SUBTOTAL(9,F2:F21)       →  80,560.25   filtered out — same; manually hidden — counted
=AGGREGATE(9,5,F2:F21)    →  80,560.25   ignores hidden rows, and nested subtotals
=COUNTIFS($E$2:$E$21,"Open") →      7    what the page is showing

The distinction between SUBTOTAL(9,…) and SUBTOTAL(109,…) is worth memorising because it is the only one that is not obvious: both ignore rows removed by a filter; only the 1xx versions also ignore rows you hid by hand with right-click ▸ Hide. A pack printed from a sheet where somebody hid three rows manually will foot correctly with 109 and overstate with 9.

And one that has cost people a morning: hiding a row does not remove it from anything else either. A SUMIFS over a hidden row still counts it, a chart plots it by default, and a PivotTable built on the range includes it. Hiding is a display property of a row. It is not a statement about the data.

🎯 Scenario: On any sheet that gets both filtered and printed, make the total cell =SUBTOTAL(109,F2:F21) and put =SUM(F2:F21) next to it with the label "unfiltered". Two cells, and the page can no longer disagree with itself in silence — the disagreement becomes a printed line.

7) Fit Sheet on One Page Is a Scale Factor, Not a Layout

Page Layout ▸ Scale to Fit has three boxes, and the difference between them is the difference between a readable pack and a compliant one.

• Width: 1 page, Height: Automatic — squeeze horizontally only, let the rows run over as many pages as they need. This is what people mean nine times in ten.
• Width: 1, Height: 1 (Fit Sheet on One Page) — squeeze until everything fits on a single sheet of paper, in both directions, at whatever percentage that takes.
• Adjust to: n% — the manual scale, which is what the other two are really setting.

The pack in this article printed at 42%, because somebody chose Fit Sheet on One Page on a listing that had grown. Excel does not warn about the consequence: 8pt body text at 42% arrives on paper at 3.4pt. The page is technically complete and practically unreadable, and the number on the page is correct, which makes it worse — it will be re-keyed by somebody squinting.

8.0pt × 42%  → 3.4pt on paper
11.0pt × 42% → 4.6pt on paper
Readable floor for most people, most printers: about 7pt
So the usable floor for 8pt body text is roughly 85% scale

Two practical notes. The scale setting is stored in the file, per sheet, so the 42% one person chose in a hurry is what everyone who opens the file prints at afterwards. And Page Break Preview (View ▸ Page Break Preview) is the honest view of all of this: it shows the page boundaries as blue lines, lets you drag them, and shows you at a glance that your listing is one column too wide — which is usually fixable by narrowing one column rather than shrinking every one of them.

🎯 Scenario: Use Width: 1 page, Height: Automatic as the default for any listing, and never Fit Sheet on One Page for anything with more than about thirty rows. A pack that runs to three readable pages is a pack; a pack on one page at 42% is a picture of a pack.

8) Page Order, and the Column That Lands on Page Seven

When a sheet is both too wide and too long for one page, Excel has to decide which way to walk through it. Page Setup ▸ Sheet ▸ Page order offers two choices, and the default is Down, then over.

Down-then-over prints the whole left-hand block of columns for every row first, and only then comes back to the top for the next block of columns. On a six-page job that means pages 1–3 are columns A–F for rows 1–120, and pages 4–6 are columns G–J for the same rows. Anyone reading the pack in order sees every row twice, half a table at a time, and the two halves are three pages apart.

Over, then down prints all the columns for one band of rows before moving down — pages 1–2 are the full width of rows 1–40, pages 3–4 the full width of rows 41–80. Each pair of pages is a complete slice of the table. On a wide listing this is nearly always what a reader wants, and it is one radio button that nobody has ever looked at.

The better fix, when it is available, is not to be wide. Hide the columns nobody reads before printing — or set the print area to just the columns that matter, which is the one thing a print area is genuinely good at:

Print area for the committee version   $A$1:$B$21,$F$1:$F$21
Print area for the full working copy   cleared

A print area can be non-contiguous — hold Ctrl while you select, and Excel stores both blocks in the Print_Area name separated by a comma. Each block then prints on its own page, which is either exactly what you wanted or a surprise, depending on whether you knew.

🎯 Scenario: In Page Break Preview, look at where the vertical blue line falls. If it cuts your table in half, decide deliberately between narrowing columns, setting Width: 1 page, and switching Page order to Over, then down. All three are one click; doing nothing is also a decision, and it is the one that produces page seven.

9) Three Colours That Print as One Grey

The listing had conditional formatting on the Status column: green fill for Paid, amber for Open inside terms, red for Open past terms. On screen the state of the book is instant. On the office mono laser it is three rectangles of almost exactly the same grey.

Excel's three standard conditional-formatting fills, converted to greyscale the way a printer driver does it, land like this:

Those three greys sit inside a band 15.5 points wide out of 255 — about 6% of the available range. Printed, they are indistinguishable, and a status column that carries its entire meaning in colour carries no meaning at all on paper.

The fix is to make the value visible as a value, not only as a colour:

=IF(E2="Paid","✓ paid",IF(TODAY()-D2>30,"▲ OVERDUE","• open"))
=REPT("■",ROUND(F2/2000,0))       an in-cell bar that survives greyscale
=IF(F2>10000,"*","")              a flag column that photocopies

Three more things about colour and paper. Page Setup ▸ Sheet ▸ Black and white forces exactly this greyscale conversion, so you can preview what the mono printer will do without walking to it. Draft quality on the same tab drops most formatting — useful for a working copy, disastrous for a pack. And gridlines and row/column headings do not print by default: tick Page Setup ▸ Sheet ▸ Gridlines and Row and column headings when you are printing something for somebody to mark up, because "the number in C7" is not a sentence anyone can act on without them.

🎯 Scenario: Print one page of any colour-coded report in black and white before you rely on the colour. If the categories survive the conversion, the report works on every printer, every photocopier and every scan of it that anybody forwards. If they do not, add a character column — it costs one column and it is legible forever.

10) The Footer Is the Only Evidence of What the Paper Is

A printed page that does not say what it is becomes an unidentifiable object the moment it leaves the desk. Two versions of the same pack, printed nine days apart, are the same object on paper unless something on the page distinguishes them — and the argument that follows is unwinnable in both directions.

Page Layout ▸ Print Titles ▸ Header/Footer ▸ Custom Footer takes four codes that answer every question anyone will ask of a loose sheet of paper:

&[Path]&[File]     the workbook this came from, with its folder
&[Tab]             the sheet name
&[Date] &[Time]    when it was printed — not when the data was as at
Page &[Page] of &[Pages]   whether you are holding all of it

Page &[Page] of &[Pages] is the one that would have caught this article's failure in a different form: a listing that says "Page 1 of 1" when the reader expected four pages is a question somebody asks. It would not have caught the print area, because thirteen rows genuinely is one page — but it catches every failure where pages go missing between the printer and the stapler.

The date codes deserve a warning. &[Date] is the date the page was printed, which on a pack reprinted in November for a September meeting is misleading in the most expensive direction. If the pack needs an as-at date, put it in a cell, as text, from the data — ="Invoices to "&TEXT(MAX(D2:D21),"dd/mm/yyyy") reads Invoices to 06/08/2026 and stays true whenever it is reprinted — and put that cell in the print area rather than the printing date in the footer.

The same logic covers where the file lives. =CELL("filename") returns the full path, workbook and sheet name of the cell's own sheet, which is the formula version of &[Path]&[File], and it has the advantage of being visible on screen to the person about to press print.

🎯 Scenario: Set the footer once per workbook — path, sheet, page X of Y — and put the as-at date in a cell driven by MAX over the date column. Every page that ever leaves that file then identifies itself, and nobody has to reconstruct from memory which version they are holding.

11) Print Selection, Active Sheets, Entire Workbook — and the Checkbox That Ignores All of It

The Print pane (Ctrl+P) has a dropdown above the copies box that decides what "print" even means, and it is remembered, which is how it produces surprises:

• Print Active Sheets — the selected sheet or sheets. Select five tabs with Ctrl+click and all five print, which is how a four-page pack becomes fourteen.
• Print Entire Workbook — every sheet, including the ones holding the lookup tables, the exchange rates and the notes to self.
• Print Selection — only the cells selected right now. This one overrides the print area entirely, which makes it the fastest way to print the rows you actually want, and the fastest way to print the rows you happened to have selected.

Underneath them sits Ignore Print Area, a checkbox almost nobody has ticked, which prints the whole used range and leaves the Print_Area name untouched. On the sheet in this article it is a one-click way to print all twenty invoices without disturbing anything, and it is also the first thing to check when somebody insists a print area is not being honoured — because a ticked box on their copy of the settings is exactly what that looks like.

Two more behaviours worth knowing. Selecting a chart and pressing Ctrl+P prints only the chart, full page, ignoring everything else on the sheet — which is either a feature or the reason a one-page summary arrived as a giant bar chart. And Excel for the web can print but cannot set a print area: it honours one that is already in the file, so a print area set in desktop Excel is quietly in force for every web user afterwards, with no menu anywhere in their interface that mentions it.

Ctrl+P ▸ Print Active Sheets  ▸ Ignore Print Area   the whole used range, nothing changed
Page Layout ▸ Print Area ▸ Clear Print Area          the name is deleted, permanently
Name Manager ▸ Print_Area ▸ Delete                   identical to Clear, from the other side

🎯 Scenario: When a print does not look like what you expected, read the three lines of the Print pane before changing anything on the sheet — what is selected, whether Ignore Print Area is ticked, and what the scale says. Most "Excel is printing the wrong thing" is one of those three, and none of them is visible from the worksheet.

12) The PDF Is the Same Print

Every setting in this article applies unchanged to Save As ▸ PDF, Export ▸ Create PDF/XPS and Print ▸ Microsoft Print to PDF. A PDF of this workbook contains thirteen invoices, because a PDF is a print with a file at the end of it instead of paper.

That matters more than the paper case, for three reasons. A PDF gets emailed, so the truncation travels. It gets archived, so it becomes the record. And it looks authoritative in a way a photocopy does not, so it is the version people quote back at you.

The one place PDF differs usefully is that you can check it in seconds without a printer. Open it and look at the page count, then the last row on the last page:

Rows in the file          =COUNTA(A2:A1000)        → 20
Rows in the print area    =ROWS(Print_Area)-1      → 13
Last row printed          read it off the PDF      → INV-2053
Last row in the file      =INDEX(A2:A21,COUNTA(A2:A21))  → INV-2060

If the last row of the PDF is not the last row of the file, stop and find out why before it goes anywhere. That check is two numbers and it is the whole of this article.

🎯 Scenario: Make the last line of any printed or PDF'd listing a deliberate one — a total row, or a row that reads "End of listing — 20 invoices, 175,698.65". A pack whose last page announces what it should have contained cannot be short by seven rows without saying so.

Four Checks Before You Press Ctrl+P

1. =ROWS(Print_Area) against =COUNTA(A:A). The rectangle against the data. If the first is smaller, the print is short, and you now know by how much. #NAME? means there is no print area, which sends you to check 2.
2. Ctrl+End. Where does the used range actually stop? Below your data means empty pages; to the right means a page order problem you have not met yet.
3. File ▸ Print, and read the page count. It is the one number on that screen that summarises every setting behind it — area, scale, order, margins and orientation together. If it is not the number you expected, something below it is not what you think.
4. Look at the last page, not the first. The first page of a print is always right; that is what people check and it is why nothing catches a print area. Every failure in this article shows up at the bottom of the last page.

Twelve Traps

1. A print area is a fixed rectangle. It does not grow with the data, it does not follow a Table, and it does not warn. Set once in June, it is still the June range in December.
2. =SUM(Print_Area) sums dates. A print area is a rectangle across whatever columns it crosses, and a date column is a column of numbers. Sum one column with INDEX(Print_Area,0,n).
3. Clearing the print area prints the used range, not the data. If Ctrl+End is at F4000, that is eighty-seven pages. Delete the rows, then save — the used range only recalculates on save.
4. Freeze Panes is not Print Titles. One fixes headings on screen, the other on paper, and neither implies the other. Sheets that need headings need both.
5. Hidden and filtered rows print nothing and are totalled anyway. SUM counts what the page does not show. Use SUBTOTAL(109,…) on any sheet that gets filtered and printed.
6. SUBTOTAL(9,…) and SUBTOTAL(109,…) differ only on manually hidden rows — both ignore filtered ones. If somebody right-clicked and hid three rows, only 109 agrees with the page.
7. Fit Sheet on One Page is a scale, and it is stored in the file. 42% turns 8pt into 3.4pt for you and for everybody who prints the file after you.
8. Page order defaults to Down, then over. On a wide sheet that scatters each row across pages three apart. Over, then down keeps each band of rows whole.
9. Conditional-formatting colours print as one grey. Green, amber and red sit within 15 grey values of each other out of 255. Add a character, a symbol or a REPT bar.
10. Gridlines and headings do not print unless you ask. Anything meant to be marked up needs both ticked in Page Setup ▸ Sheet.
11. &[Date] is the printing date, not the as-at date. Drive the as-at from the data with MAX over the date column and put it in a cell inside the print area.
12. Print Selection overrides the print area; Ignore Print Area overrides everything. Both are remembered in the Print pane, and neither is visible from the worksheet.

The print area in this article was not a mistake. It was correct on the day it was set, and everything that happened afterwards was somebody adding an invoice to the bottom of a list, which is the only thing that was ever going to happen to a list of invoices.

That is the whole category. A print area is a statement about a range made at one moment and honoured forever, on a sheet whose entire purpose is to change. Either keep it under a check that fails loudly — =ROWS(Print_Area) against =COUNTA(A:A), in two cells, above the data — or do not set one at all, keep Ctrl+End honest, and let Excel print what is actually there.