The Buyer Read Our Walk-Away Price Back to Us and £259,800 of Margin Went With It, Because Hidden Rows, Hidden Columns and a Hidden Sheet Are a Display Setting and Never a Redaction

Kestrel Facilities Management spent six weeks building the bid and forty minutes preparing the file.

The schedule of rates ran to 214 lines. Columns F, G and H — unit cost, margin percentage, and the rate below which the bid director would rather lose — were selected, right-clicked, and hidden. The Bid model sheet, which is where those three columns came from, was hidden from its tab. The rate card underneath the schedule, 214 further rows covering eleven other clients, was filtered down to Brantwood only. Then the workbook was protected with a password so that none of it could be undone, and Tender — Brantwood HA — Schedule of Rates.xlsx was attached to an email and sent on 14 January 2026.

The bid was £614,000 a year for three years: £1,842,000, against £476,464 of direct cost, a gross margin of 22.4%.

At the clarification meeting on 2 February the head of procurement at Brantwood said, in the pleasant tone people use when they already know the answer, that she understood £524,000 was really the floor.

It was. Bid model!D9, labelled Minimum acceptable — do not go below. Their analyst had reached it by clicking the corner box above row 1, right-clicking a tab, and choosing Unhide. He said afterwards it took about twelve seconds and that he had not been looking for anything; he opens every tender that way because nine in ten of them have something hidden in them.

Nothing was hacked. Nothing was cracked. No macro ran and no tool was downloaded. Everything Brantwood saw, it saw through the ordinary Excel interface, using commands that sit in the right-click menu, and most of them took fewer clicks than hiding the thing had taken in the first place.

The mistake was not carelessness. It was a category error, and it is one of the most expensive ones in this application: hiding is a display property. It changes what Excel draws. It does not change what the file contains, and the file is what you sent.

What this covers. Everything here applies to Excel 2016, 2019, 2021, 2024, Microsoft 365 and Excel for Mac, and most of it to Excel for the web (which hides and unhides rows, columns and sheets, and shows document properties, but has no Document Inspector and no VBA properties window). SUBTOTAL, AGGREGATE, SUM, COUNT, COUNTA, COUNTIF, COUNTIFS, SUMIF, SUMIFS, SUMPRODUCT, CELL, LEN, IF, IFERROR and ROUND work everywhere; SHEETS and FORMULATEXT need 2013 or later; FILTER, UNIQUE, SORT and LET need 2021 or 365. A tender is the example because the money is easy to count, but the same forty minutes are a board pack with the individual salaries hidden, a grant application with the other applicants filtered out, a price list with the trade column hidden, a redundancy model, a due-diligence pack, or any file where somebody removed something from view and then pressed Send.

1) Eleven Things Kestrel Hid, and How Long Each One Lasted

Read the second column against the fourth.

The second column is what a careful person did on purpose, believing it was enough. The fourth is what actually happened, and in nine of the eleven rows it is a shorter sequence of clicks than the hiding was.

Three of the rows are worth pausing on, because they are the ones people do not expect.

Row 7 is a pivot table whose source sheet was deleted before the file was sent — the strongest form of removal there is, short of not having the data. It did not work, because a PivotTable does not read its source when you look at it. It reads a cache: a private copy of the source rows that Excel writes into the file alongside the pivot. Delete the source sheet and the pivot keeps working, which is the clue. Double-click any value cell and Excel's Show Details writes the rows behind that number onto a brand new sheet, reconstructed from the cache. The deleted sheet came back in one double-click, and nobody at Kestrel had ever heard of the feature that did it.

Row 10 is a footer. Somebody set &[Path]&[File] in the page footer of the schedule sheet in about 2019, because at the time it was useful to know which file a printout came from. Brantwood circulated the tender internally as a PDF, and page one carries \\kestrel-fs\Bids\2026\Brantwood HA — Final — v11 SIGN OFF along the bottom. A version number is not a catastrophe. A network share name, a folder called Bids, and the fact that there were eleven versions is a small, free briefing.

Row 9 is the password. It is the one that feels most like security and provides the least.

🎯 Scenario: Take the last workbook you emailed outside your organisation. Click the corner box above row 1 and to the left of column A, then Home ▸ Format ▸ Hide & Unhide ▸ Unhide Rows, then Unhide Columns. Then right-click any sheet tab and look at whether Unhide is available. That is the whole attack, it takes fifteen seconds, and you should be the one running it.

2) Hiding Is a Display Property, Not a Security Boundary

This is the sentence the rest of the article unpacks, so it is worth being precise about it.

When you hide row 30, Excel does not remove row 30. It sets an attribute on it. In the file — and an .xlsx is a zip full of XML, which section 8 gets to — the row looks like this:

<row r="30" hidden="1" customHeight="1" ht="0">
  <c r="F30"><v>2213.44</v></c>
</row>

The value 2213.44 is exactly where it was. The only difference is hidden="1", and that attribute is instruction to the renderer, not a property of the data. Everything that reads the data rather than the screen carries straight on:

The CSV row surprises people every time. A workbook carefully hidden and then exported to CSV for a system import arrives at the other end complete, because a CSV has no concept of a hidden anything and Excel writes the used range.

And the last two rows are the asymmetry worth learning properly, because they are the only functions in Excel that care:

• SUBTOTAL with function numbers 1–11 (9 for sum, 3 for count) ignores rows hidden by a filter, and includes rows you hid by hand.
• SUBTOTAL with 101–111 (109, 103) ignores both — filtered out and manually hidden alike.
• AGGREGATE options 5 and 7 ignore every hidden row however it got that way, and option 5 additionally lets you skip nested subtotals.
• Everything else — SUM, AVERAGE, COUNT, COUNTA, COUNTIF, SUMIF, SUMIFS, MIN, MAX, SUMPRODUCT — ignores nothing.

That difference is a reporting feature most of the time. Here it is a detector, and section 14 uses it as one.

🎯 Scenario: Put =SUM(F2:F5000) and =SUBTOTAL(109,F2:F5000) side by side at the top of any sheet you are about to send. If the two numbers differ, something in that column is hidden from you as well as from the recipient, and you want to know which of you that was meant for.

3) Three Ways a Row or Column Gets Hidden, and One Keystroke That Finds All of Them

People say "hidden" for at least three mechanisms that behave differently, and a file usually has more than one of them in it.

1. Hidden by hand. Select, right-click, Hide; or Ctrl + 9 for rows and Ctrl + 0 for columns. The row header numbers skip — 28, 29, 34 — and the line between them thickens very slightly, which is the only visual cue Excel gives you and which nobody notices on a screen full of data.

2. Hidden by a filter. The funnel is set, the row headers skip in the same way, and the header numbers turn blue. Section 5 is about why this one is nobody's idea of removal.

3. Hidden by an outline. Data ▸ Group, then collapsing the group with the minus button or the 1/2/3 level buttons. Same skipping headers, plus a bracket in the left margin. SUBTOTAL treats this as manually hidden, not as filtered, which catches people out in exactly one direction: a SUBTOTAL(9,…) under a collapsed outline still counts the rows you collapsed.

There is also a fourth thing people call hiding which is not hiding at all — a row height or column width dragged to zero. Excel records that as hidden and unhides it the same way, so it belongs in group 1 despite feeling like a different act.

The one keystroke. All three come back with the same sequence, and it is worth doing by muscle memory on any file you did not build:

Click the corner box (above row 1, left of column A), or press Ctrl+A twice
Home ▸ Format ▸ Hide & Unhide ▸ Unhide Rows
Home ▸ Format ▸ Hide & Unhide ▸ Unhide Columns
Data ▸ Clear (clears every filter on the sheet)
Data ▸ Ungroup ▸ Clear Outline

Five commands, one sheet, about eight seconds. Ctrl + Shift + 9 unhides rows in the selection directly; Ctrl + Shift + 0 is supposed to unhide columns and is intercepted by the Windows keyboard-layout shortcut on most machines, which is why the ribbon route is the one to learn.

The awkward case is column A and row 1, because you cannot select a hidden first column by dragging across it. Type A1 into the Name Box to the left of the formula bar, press Enter — the selection is now in the hidden column even though you cannot see it — then Home ▸ Format ▸ Hide & Unhide ▸ Unhide Columns. Anybody who has ever hidden column A to tidy up a report has met this, and it is also how anyone else gets into it.

🎯 Scenario: Hidden rows are also why a sort can quietly detach a record. Rows hidden by hand do not move when the range around them is sorted — they stay physically where they are while their neighbours reorder. Unhide everything before sorting anything, every time.

4) Hidden Sheets, and the One State the Unhide Dialog Will Not Show You

A sheet has three visibility states, and only two of them are reachable from the tab bar.

Hidden is what Kestrel used, and it is one right-click away from undone. The Unhide dialog lists every hidden sheet by name, and in older versions it lets you restore only one at a time, which is the entire inconvenience involved.

Very hidden is a third state that the interface does not expose. Press Alt + F11 for the Visual Basic editor, press F4 for the Properties window, select the sheet in the project tree, and set Visible to 2 - xlSheetVeryHidden. The tab disappears and the Unhide dialog does not list it. It is genuinely more hidden than hidden.

It is still not a redaction, for three reasons that are worth saying plainly because people do treat it as one:

• The same VBA properties window undoes it, in the same four keystrokes, for anybody who knows the state exists.
• The sheet name is in docProps/app.xml in the TitlesOfParts list, along with every other sheet in the book, visible or not. A sheet called Walk away announces itself without being opened.
• Opening the file as a zip shows it completely — see section 8.

Workbook-structure protection (Review ▸ Protect Workbook) greys out Hide, Unhide, Insert, Delete, Move and Rename on the tab bar. This is genuinely useful for stopping a colleague from breaking a model by dragging a tab, and it is what Kestrel believed they had applied on top of the hiding. It stops the dialog. It does not encrypt the sheet, does not remove it from the file, and does not survive the file being opened by anything that is not Excel. A protection password is a short hash stored in the XML whose only job is to make Excel refuse to reopen a dialog.

🎯 Scenario: =SHEETS() typed into any cell returns the number of sheets in the workbook, counting hidden and very hidden ones. Count the tabs you can see and compare. If the numbers disagree, something is in there, and this is the fastest way to find out — including in a workbook somebody else sent you.

5) A Filter Is a View, and a View Removes Nothing

The 214 rows of other clients' rates below the schedule were filtered, not deleted. The funnel on the Client column was set to Brantwood, the other eleven clients vanished from the screen, and the file was sent.

Rows 217 to 430 were still rows 217 to 430. Brantwood clicked the funnel, ticked (Select All), and read a competitor rate card that also happened to be their own neighbours' — a fellow housing association on the same framework paying £1.96/m² for grounds maintenance against the £2.61/m² Kestrel had just quoted them. That difference is defensible; the two contracts are not the same size or the same scope. Defending it was not the conversation anybody had planned to have on 2 February.

Three things about filters that matter when a file is leaving:

• The filter state is saved in the file. The recipient opens it filtered, which is why it feels like the rows are gone.
• Clearing it is one click. Data ▸ Clear, or the funnel's (Select All), or Ctrl + Shift + L twice, which drops and re-adds AutoFilter and clears every criterion on the sheet in one movement.
• A copy behaves differently depending on how the rows got hidden, and this is the detail people get wrong in both directions. Copying a range with an AutoFilter applied copies the visible rows only. Copying a range containing rows you hid by hand, or collapsed in an outline, brings every one of them along. The only way to be certain of either is to be explicit: select the range, press Alt + ; (Go To Special ▸ Visible cells only), then copy.

That last point is why "I copied the visible rows into a new file" is not, by itself, a safe procedure. It is safe with Alt + ; and unreliable without it, and the failure is silent: the pasted block simply contains more than you looked at.

🎯 Scenario: Before sending any filtered sheet, press Ctrl + End. Excel jumps to the bottom-right corner of the used range. If that corner is a long way past the last row you can see, the rows in between are in the file. This works when the filter, the hidden rows and the outline are all conspiring, because it reads the used range and not the display.

6) White Font, Zero Width, and the ;;; Format: Three Things That Are Not Hiding

All three of these appear in real files, usually applied by somebody who knew perfectly well they were improvising.

White font on a white background. The value is in the cell. Click it and it is in the formula bar. Select the column and change the font colour and it is on the screen. Copy it to Notepad and it is in Notepad. Its only defence is that the recipient has to click.

Zero column width or row height. This is hiding — Excel records it as hidden and unhides it as hidden — so it is section 3's problem, not a separate one. It is worth naming only because people drag a width to nothing and do not think of it as having hidden anything, so it is missed at the checking stage.

The ;;; custom number format. Four sections in a number format — positive, negative, zero, text — and making all four empty displays nothing for any value. Home ▸ Number ▸ the small arrow ▸ Custom ▸ type ;;;. The cell appears empty. It is not: the value is in the formula bar, it is in every formula that references it, it is written to CSV, it is in the XML, and pressing Ctrl + Shift + ~ (General format) on a selection brings back everything in it at once.

There is one legitimate use for ;;; and it is worth distinguishing, because the format is not a bad thing in itself: hiding a helper column's working from a report that a colleague is going to print, inside your own organisation, where nothing turns on it. Using it on the way out of the building is what does not work.

=LEN(D3)                       a cell that shows nothing but returns 5 holds five characters
=ISNUMBER(D3)                  TRUE from a cell that appears to be empty is the tell
=COUNTA(D2:D216)-COUNTBLANK(D2:D216)   how many of those "empty" cells are not

🎯 Scenario: Select the whole sheet and press Ctrl + Shift + ~. Every number format on the sheet reverts to General for as long as you leave it that way, and anything formatted ;;; or formatted to look like something else is suddenly readable. Look, then press Ctrl + Z.

7) What Protection Actually Protects

Kestrel applied a password. The bid director's note to the team said the file was "locked". Three different features get called that, and only one of them involves encryption.

The first two store a hash in the file. When you type the password, Excel compares and decides whether to let the dialog proceed. The cell contents, the formulas, the hidden sheets and the formulas marked Hidden in Format Cells are all sitting in the file in plain XML the whole time, and any program other than Excel — including the zip viewer built into Windows and macOS — ignores the hash entirely because it has no dialog to refuse.

The third is real encryption of the whole file, and the whole file is the unit: you cannot encrypt one sheet, and anyone who can open the file at all can see everything in it. Which means the only thing it protects you from is a recipient who does not have the password, and the recipient of a tender does.

This is not an argument against sheet protection. It is a very good tool for its actual job, which is stopping well-meaning people from typing over a formula. It is an argument against reading it as confidentiality, because the word "protected" invites exactly that reading, and Kestrel's forty minutes ended with somebody typing a password and feeling finished.

🎯 Scenario: If you have a workbook you believe is protected, test the belief the cheap way. Copy it, rename the copy to .zip, open it, and look in xl/worksheets/. If you can read your values in there, so can your recipient, and no dialog anywhere is going to change that.

8) The File Is a Zip, and Sixty Seconds Is All It Takes

Every .xlsx, .xlsm and .xltx written since 2007 is a zip archive of XML files. This is not a trick and not a vulnerability; it is the published Open XML format, and it is why the files compress well and recover from corruption better than the old .xls did.

It also means that everything in this article can be checked, or extracted, without Excel:

Copy the file.  Rename the copy Tender.xlsx → Tender.zip.  Open it.

xl/workbook.xml            every sheet name, and state="hidden" or "veryHidden"
xl/worksheets/sheet1.xml   the cells, with hidden="1" on hidden rows and columns
xl/sharedStrings.xml       every distinct piece of text in the workbook, in one list
docProps/core.xml          author, last modified by, created, modified
docProps/app.xml           company, manager, and TitlesOfParts — all sheet names
xl/pivotCache/             a full copy of a pivot table's source rows
xl/threadedComments/       comments, with the display name of whoever wrote them
xl/externalLinks/          the full path of every workbook this file links to

xl/sharedStrings.xml is the one to sit with for a moment. Excel stores text once and points at it from the cells, so this single file is a flat list of every distinct string in the workbook — every label on every sheet, hidden or not, including sheets you hid, including strings left over from text you deleted but whose entry has not yet been cleaned up. Opening it is not analysis. It is reading.

None of this requires the password from section 7, because there is no dialog involved. Only Encrypt with Password changes the picture: an encrypted file is not a readable zip at all.

🎯 Scenario: Do this once, to your own file, today. You will not need to be told again what "hidden" means in this application, and the ten minutes are the cheapest training available on the subject.

9) A Deleted Sheet Keeps Living in Six Places

Row 7 of the table is the case that defeats careful people, because they did the thorough thing — they deleted the data — and it came back anyway.

1. The pivot cache. A PivotTable does not read its source range when you interact with it. When it is created or refreshed, Excel writes a private copy of the source rows into the file, and the pivot reads that. Delete the source sheet and the pivot still works, still filters, still slices. Double-click any value cell and Show Details writes the underlying rows of that number onto a new sheet, rebuilt from the cache. This is a feature, it is on by default, and it is how the source sheet Kestrel deleted came back to Brantwood on a double-click.

• Turn the feature off per pivot: right-click the pivot ▸ PivotTable Options ▸ Data, untick Enable show details. That blocks the double-click. It does not remove the cache from the file.
• The only removal is to remove the pivot, or to convert it to values (copy ▸ Paste Special ▸ Values) and delete the original. A pivot in a file is a copy of its source in that file.
• Untick Save source data with file in the same dialog and the cache is not written — at the cost that the pivot is empty until refreshed, and it cannot refresh against a sheet you deleted.

2. The Data Model. Anything loaded to the Data Model, by Power Pivot or by Add this data to the Data Model on a pivot, is stored compressed inside the file and is not visible on any sheet at all. Deleting a sheet does nothing to it. Data ▸ Queries & Connections and the Power Pivot window are where it lives.

3. Power Query. The query itself survives the deletion of its output, and the M code holds the full source path, and frequently the SQL, and sometimes a server and database name. Data ▸ Get Data ▸ Launch Power Query Editor ▸ Advanced Editor shows it in one click.

4. Charts. A chart keeps a cache of its plotted values in its own XML. Delete the source and the chart carries on drawing the last numbers it saw, and those numbers can be read from the chart element itself.

5. Defined names. Deleting a sheet leaves behind names that referred to it — usually as #REF!, which is harmless — but also leaves names that refer to other files, complete with their paths. Kestrel's had a name pointing at a workbook whose filename was a competitor's name. Formulas ▸ Name Manager lists all of them, and Data ▸ Edit Links lists the external files.

6. Conditional formatting and data validation rules. Both can reference a deleted range or another sheet, and both carry their formulas in the file where anybody can read them. A validation dropdown sourced from a list on a sheet that no longer exists still names that list.

🎯 Scenario: When a pivot table is the thing you are sending, do not send the pivot. Copy it, Paste Special ▸ Values, and send the values. A pivot is a live copy of its source; a paste of its values is a picture of its output, which is generally what you meant.

10) The Metadata Nobody Attaches On Purpose

File ▸ Info shows a panel on the right that most people have never read. Brantwood read it.

• Author and Last Modified By — two names, and in Kestrel's case the bid director and the pricing analyst, which told Brantwood exactly who to address the negotiation to.
• Created and Last Modified dates, which gave the three weeks of work and the 23:41 save the night before submission.
• Company and Manager, filled in from whoever configured Office, often years earlier and often wrong.
• Title, Subject, Keywords, Categories, Comments — free-text fields that get filled in by templates and never revisited.
• Related Documents ▸ Open File Location, which shows the path the file came from.

Beyond that panel:

• Threaded comments and notes carry the display name of whoever wrote them. Review ▸ Show Comments puts every one of them in a side panel in one click, which is how "we've gone in high, they took +9% last year without blinking" was read as a single list item rather than discovered by hovering over a cell.
• Headers and footers can contain &[Path], &[File], &[Tab] and a typed anything. They are invisible in Normal view. View ▸ Page Layout shows them, and so does every PDF you make.
• Print areas, custom views and defined names are stored per sheet and are readable in Name Manager.
• The document theme and the custom colours are not confidential, but the file path in the External Links dialog absolutely can be, because a path is a folder structure and a folder structure is an org chart.

🎯 Scenario: File ▸ Info ▸ Check for Issues ▸ Inspect Document is the fast way to see this list rather than reading it field by field — but read the next section before clicking Remove All, because two of its buttons change your data and not just its packaging.

11) The Document Inspector: What It Removes, and Four Things It Cannot

File ▸ Info ▸ Check for Issues ▸ Inspect Document. It scans, then offers a Remove All button next to each category it found something in. It is a genuinely good tool and it is the right first move on any file leaving the building.

What it finds and removes well: comments and notes; document properties and personal information, including author and last-saved-by; headers and footers; hidden rows, hidden columns and hidden worksheets; invisible content, meaning objects formatted as invisible; embedded documents; custom XML data; and ink.

Four things to know before you press it:

1. Removing hidden rows and columns deletes them. It does not unhide them for you to review. Formulas that referenced those cells become #REF!, totals change, and a VLOOKUP with a hard-coded column index silently returns the wrong column because the columns after the deletion renumbered. Always inspect a copy, and always look at your totals afterwards. There is no undo for an inspection.

2. It does not touch the pivot cache, the Data Model or a Power Query's M code. Everything in section 9 survives an inspection that reports a clean bill. This is the single most important limitation in the tool and it is not stated in the dialog.

3. It does not know about white font, ;;; formats, or values sitting in far-off cells. A number in BZ4000 on an otherwise empty-looking sheet is not hidden by any definition the Inspector uses, so it is not reported.

4. It cannot inspect what it cannot see. A very hidden sheet, a sheet hidden by a macro at open, or content inside an object are not reliably reported across versions. Do not read a clean inspection as proof; read it as one pass of several.

The practical routine is: save a copy under a new name, inspect the copy, remove what it offers, then re-check the totals on the copy against the original, then send the copy. Three minutes, and it converts the Inspector from a tool that might delete your data into one that cannot.

🎯 Scenario: There is a much simpler version of this for the commonest case. If the recipient does not need to change anything, send a PDF. Hidden rows, hidden columns, hidden sheets, filtered-out rows, pivot caches, the Data Model and every document property except the ones you choose to keep do not exist in the output. Check the footer first.

12) The Only Redaction That Works: Build the File You Meant to Send

Everything above is a way of finding what is in a file. None of it is a way of being sure, because being sure means enumerating an open-ended list of hiding places, and you will eventually miss one on a Friday afternoon.

The reliable move is the opposite of removal. Do not take a working file and subtract from it. Make an empty file and add to it.

1. New workbook.
2. From the working file, select only the range you intend to send.
3. Copy. In the new workbook, Paste Special ▸ Values, then Paste Special ▸ Formats.
4. Check the sheet count is 1, the used range ends where you expect (Ctrl+End),
   and there are no names in Name Manager.
5. Save as a new file with a name you would be happy to read aloud.

The Paste Special ▸ Values step is doing more work than it looks. It leaves behind every formula, and with them every reference to a sheet you did not copy, every defined name, every external link, and every trace of how the number was arrived at. What arrives is the number.

Four properties follow, and each one deletes a section of this article:

• There are no hidden sheets, because there is one sheet, and you made it.
• There is no pivot cache and no Data Model, because nothing was loaded to either.
• There are no document properties worth reading, because the file is minutes old.
• There are no formulas, so there is nothing pointing at anything you did not send.

The objection is always that the recipient wants a live file they can work with. Sometimes they genuinely do, and then this section does not apply and sections 1 to 11 do. Very often they want the numbers, and a tender schedule is a list of prices, and Kestrel's recipient wanted a list of prices. Kestrel sent them the model that produced it.

🎯 Scenario: Make the rebuild a named step in whatever checklist governs files leaving your team — a bid submission, a board pack, a data request. "Send a rebuilt file" is a rule somebody can follow at 18:30 on a deadline. "Check for hidden content" is a rule that depends on remembering eleven things.

13) Where This Bites Outside a Tender

The tender is the example because the loss is a single number. The pattern is much broader, and three cases come up constantly:

The board pack with the salary column hidden. A headcount sheet, column G hidden, circulated to eleven people including two of the people in column G. Nothing about the mechanism differs from Kestrel's; only the consequence does, and it is worse, because pay data circulating internally is both a confidence problem and, depending on where you are, a regulatory one.

The data request with other people's rows filtered out. A regulator, a customer or a journalist asks for the records relating to them. The file is filtered to their rows and sent. Every other record is in it. Where the records are personal data, the filter is not a redaction under any data-protection regime worth the name, and the disclosure is not made less real by having been invisible on screen.

The template with last quarter's numbers hidden in it. Somebody builds a reusable template from a finished workbook by hiding the sheets that held the previous client's figures. The template is then used for twenty clients over two years, and it carries the first one's data into every single one of them. This is the version with the longest tail, because nobody is looking any more.

🎯 Scenario: The question that catches all three is not "did I hide it?" but "if the recipient unhid everything, what would they see?" That question has an answer you can actually check in fifteen seconds, and the other one does not.

14) Six One-Cell Checks Before Anything Leaves

Paste these into empty cells on a copy, read them, then delete them.

1. How many rows can the recipient see that I cannot? =COUNTA($A$2:$A$5000)-SUBTOTAL(103,$A$2:$A$5000). COUNTA counts everything; SUBTOTAL with 103 counts only what is displayed. Anything other than zero is rows that are in the file and not on your screen.
2. Does the visible total match the real one? =ROUND(SUM(F2:F5000)-SUBTOTAL(109,F2:F5000),2). Zero means nothing numeric is hidden in that column. Run it on every money column.
3. Does this workbook have more sheets than it is showing me? =SHEETS() returns the count including hidden and very hidden sheets. Compare it with the tabs you can count.
4. Is anything formatted to look empty? =COUNTA(D2:D216)-COUNTIF(D2:D216,"") counts cells that hold something. Run it on a column that looks blank, and follow up with =LEN(D3) on any cell that surprises you.
5. Does anything point outside this file? =SUMPRODUCT(--ISNUMBER(SEARCH("[",FORMULATEXT($A$1:$Z$500)))) counts formulas containing a [, which is how an external workbook reference is written. Then check Data ▸ Edit Links for the list.
6. Where does the used range actually end? Press Ctrl + End, and put =CELL("address") in a cell afterwards to record it. If it ends far below or far to the right of what you can see, go and look at what is out there.

Two more that are not formulas and matter as much: =SHEETS() has a companion in the tab bar itself — right-click any tab and see whether Unhide is greyed out or live — and Ctrl + Shift + ~ on a full-sheet selection exposes every number format at once.

🎯 Scenario: Put checks 1, 2 and 3 into three cells of a small template and keep it. Pasting three formulas into a file before you send it is a habit that fits in the gap between finishing and attaching, which is the only place a check like this ever survives.

15) Twelve Traps

1. Hiding is a display attribute in the file, not a removal. hidden="1" sits next to the value it is hiding, and the value is unchanged.
2. Sheet protection and workbook-structure protection are not encryption. They store a hash, they stop a dialog, and anything that is not Excel ignores them completely.
3. Save As CSV writes hidden rows and columns out in full, because a CSV has no way to represent a hidden anything.
4. A pivot table carries a copy of its source data inside the file. Deleting the source sheet does not remove it, and Show Details on a double-click writes it back out onto a new sheet.
5. Copying a range with rows hidden by hand or collapsed in an outline brings those rows with it. Only Alt + ; — Go To Special ▸ Visible cells only — makes a copy that is genuinely what you looked at.
6. The Document Inspector deletes hidden rows and columns rather than revealing them, so formulas break and totals move. Inspect a copy, and check the totals afterwards.
7. The Document Inspector does not touch the pivot cache, the Data Model or a query's M code. A clean report is not a clean file.
8. Very hidden is not secret, it is a property in the same window anyone can open with Alt + F11, and the sheet name is listed in docProps/app.xml regardless.
9. The ;;; number format and white font hide a value from a glance and from nothing else — not from the formula bar, not from a formula, not from CSV, not from the XML.
10. Headers and footers are invisible in Normal view and printed on every page. &[Path] has leaked more folder structures than any other single setting in this application.
11. Manually hidden rows do not move when you sort, so a sort performed over hidden rows silently detaches them from the records they belong to.
12. Ctrl + End is the fastest truth in Excel. It goes to the end of the used range whatever is hidden between here and there, and a corner a long way past your last visible row means there is something out there.

Nobody at Kestrel was careless. Forty minutes is more care than most files get on the way out, the person doing it followed a checklist that had been used on thirty tenders, and every step on that checklist did exactly what it said: the columns were hidden, the sheet was hidden, the rows were filtered, the password was set.

What went wrong is that all four of those steps are instructions to the part of Excel that draws the screen, and the thing they sent was not the screen. It was the file. The file had every number in it the whole time, sitting in plain text a rename away, and the forty minutes bought a display state that a stranger undid in twelve seconds without meaning anything by it.

So the discipline is two habits and one rule. Before anything leaves, unhide everything and look — corner box, Unhide Rows, Unhide Columns, clear the filters, right-click a tab — because that is precisely what the recipient will do, and you would rather be first. Read Ctrl + End and believe it over your eyes. And when what they need is numbers rather than a model, do not subtract from the working file: open a new one, paste the values, and send a file whose hiding places you never built.