A bill of quantities landed on my desk one evening at Huawei, already filled in, already checked by someone else, ready to go out to the client the next morning. I only opened it to add my sign off. But a wrong quantity in a bill of quantities is real money, so I read the totals twice the way I always do, and one of them was short. The formula was not broken. The range was. A SUM that stopped one row above the last site and quietly left it out of the total, and nothing on that sheet looked wrong at all.
Months later I ran into the same lesson far away from Excel. A vendor had sold me a list of 1,031 email addresses marked as verified, and when I put every one of them through real technical checks only 386 came back safe. 620 failed, 25 were provably dead, and one of the dead ones belonged to a large courier company whose domain publicly refuses all mail. The file opened clean and sorted clean and looked completely trustworthy. Data that looks fine and data that is fine are two different things, and the whole gap between them is where the costly mistakes wait.
A blank cell screams at you. A wrong total just sits there looking confident.
Proofreading and auditing are not the same check
When most people check a spreadsheet before sending it, they proofread it. They trust the numbers and look for the obvious things, a typo in a heading, a blank cell, a column too narrow to read. That catches the errors that announce themselves. It does nothing for the errors that keep the exact shape of a correct answer, because a wrong total wears the same font, the same two decimals and the same quiet confidence as a right one.
Auditing is the opposite habit. You distrust the numbers on purpose and you go back to where each one came from. A blank cell is a loud error, someone will spot it in the first meeting and nobody gets hurt. The total that is off by one row, or by a single fixed number buried inside a formula, is a silent error, and it can travel all the way to the client before a person catches it, if a person catches it at all. In the bill of quantities I opened, the report read 728 and the true figure was 800. Off by a single site. On a printed page both numbers look equally final.
That is the mindset the rest of this rests on. Before a sheet leaves your hands, you owe it a few minutes of honest doubt, and the errors worth the most money are exactly the ones that will never raise their hand.
The four that quietly reach the client
After years of Excel audit work at Huawei and cleanup jobs for Upwork clients in my evenings, the same handful of culprits keep turning up. They all share one trait, they look tidy to a human and mean something different to a formula, which is why proofreading walks straight past them.
Merged cells
A merged heading looks clean to a person, but to code the merged range is one real value followed by empty cells, so anything that reads down that column can shift meaning with no warning. If a sheet will ever be read by a program, keep it out of the data area.
Dates stored as text
A date saved as text sits there looking exactly like a real date, then sorts in the wrong order and refuses to add up, and you find out only when a monthly total lands in the wrong month. A formula can create the same problem, which is why numbers pulled out with REGEXEXTRACT add up to zero.
A SUM that stops one row short
The one from my opening. A range that ends above the last row of data, so a new site or a new invoice slides in under the total and never gets counted. It reappears every time rows grow but the formula does not, and I tested which fix holds in which layout in TRIMRANGE, trim references and a Table total row.
Hardcoded numbers inside formulas
A tax rate or a fixed fee typed straight into a formula instead of sitting in its own labeled cell. It works fine until the rate changes, and then every formula that copied it is silently wrong and nobody remembers where the number lives. Fixing most of these is really a cleaning problem, and I go deeper on that in the piece on cleaning messy Excel data.
Trace, reconcile, sample
You do not need to reread every cell to trust a spreadsheet, and honestly you cannot. What you need is a short repeatable pass that attacks a sheet from three angles, so a silent error has to survive all three to reach the client. I run these in order every time before a report goes out.
Trace
Pick your headline number, the one the client actually reads, and follow it back to its inputs. Click into the formula, see the range it pulls, and read that range against the real data underneath it. This is the single move that would have caught my 728. The total looked calm, but the range it pointed at stopped one row above the last site.
Reconcile
Get the same number a second, independent way and make the two meet, which is also the quickest way of checking a total that ChatGPT or Copilot gave you. If your report says the total quantity is 728, the billing system or the source export should agree, and when they disagree the difference is your map straight to the error. This is the same discipline behind comparing quotes in vendor bid leveling, where a total that does not tie back to its line items is the first thing you chase. Some documents hand you that second number for free, which is why checking extracted invoice data against the invoice's own totals catches wrong values without anyone reading the pages.
Sample
Pull a handful of rows at random, not the first ones, and check each by hand against the source. If five random rows are all correct, your confidence in the other thousands goes up honestly, and if one is wrong you have found a pattern, not a one off.
When the check belongs inside the build
A manual audit pass is enough when a report goes out once, or when you own it and run it yourself. But once the same report leaves every week, and the data pours in from someone else's system, doing the trace by hand forever is how a silent error eventually slips through on the one tired evening you skip it. That is the point where the check belongs inside the build, a script that reads the incoming file, refuses to run when the Amount column went missing or the row count dropped, and reconciles the total against the source before it writes a single formatted page. When the source lives in a real database rather than a pile of exports, the same idea moves into a reporting layer, which I wrote about in moving from Access to Power BI reporting.
I am holding back the full checklist on purpose, because the real one is not a generic list, it is tuned to your files, your column names, and the specific ways your source likes to break. That tuned version, the validation rules and the reconciliation baked into a build that fails loudly instead of lying quietly, is the part you cannot copy off the internet. If you keep sending out a report you are not fully sure of, start a project and tell me which number keeps you up, and I will tell you honestly whether you need a five minute habit or a real build. Members get every tool I ship along the way through the membership, current and future, licensed to you forever.
So the short version. A blank cell is loud and a wrong total is silent, and the silent ones are the expensive ones, so before a sheet ships you distrust it on purpose. Trace your headline number back to its inputs, reconcile it against the source a second independent way, and sample a few random rows by hand. That three part pass catches the merged cells, the dates saved as text, the SUM that stopped one row short, and the fixed number hiding inside a formula, before any of them reaches a client.
When the report goes out week after week, the honest move is to build the check into it so it refuses to run on a broken file, and that tuned build is the done for you version of everything here, shaped around your real mess and yours to keep. Members get every product I release along with it. The number that looks fine is not the one to worry about. It is the one you never traced.