The spreadsheet audit: seven formulas that quietly produce the wrong number

Every office spreadsheet looks finished long before it is correct. A total that is off by a little is worse than an empty cell, because nobody questions it — it gets copied into a report, a quote, or a tax filing and keeps travelling.

This is not a walk-through of seven functions in teaching order. It is an audit you run on a file you already own: one question per step, each with a yes-or-no test you can finish before your coffee goes cold. If a step fails, the fix sits in the same step.

Step one: are the numbers actually numbers?

Put =COUNT(range) beside the column and compare the result with the number of rows you filled in. COUNT counts numeric cells only, so if you typed ten amounts and the answer is eight, two of them are text and every total built on that column is short by whatever those two cells hold.

The visual tell is alignment: a real number hugs the right edge of its cell, text hugs the left. The usual culprit is a value pasted out of a report with a leading space, or an amount typed into a column that was formatted as text before the typing started. =VALUE(TRIM(B2)) cleans one cell; Find and Replace removes the stray spaces across the column in one pass. If a cell shows the formula instead of a result, its format is Text — set it to General and re-enter with F2, Enter.

Step two: does the range end where the data ends?

Click the formula, then click into the formula bar so the range highlights on the sheet. Read the last row of the highlight against the last row you actually filled. Three faults account for almost every bad total: a range that stops one row above the newest entry, a range that swallows the subtotal row of the block above (money counted twice), and a SUM that includes its own cell — a circular reference, which the app refuses to calculate rather than guessing.

Starting the range on the header row is harmless, because text is ignored. Ending it a row early is not.

Step three: is a filter making the total lie?

SUM and AVERAGE ignore filters and include hidden rows. Filter the sheet to one month and the "month total" still covers the whole year, which is the quietest wrong answer in the whole file.

=SUBTOTAL(9,B2:B200) follows the filter. =SUBTOTAL(109,B2:B200) follows the filter and also skips rows hidden by hand. For an average, the same function with 1 instead of 9. A two-second sanity check: select a range and read the status bar — Sum, Average and Count there obey the same rules as the functions.

Step four: did the condition mean what you think?

=IF(condition, value if true, value if false) takes three arguments and two commas, no exceptions. Text answers need double quotes: =IF(C2>=5,"Pass","Fail") works, while a quoted number compares as text and returns nonsense. Combine conditions with AND (every one must hold) or OR (any one is enough).

Nesting gives you more than two outcomes, and order decides the answer because the first true test wins and the rest are never evaluated:

=IF(C2>=8,"Good",IF(C2>=6.5,"Fair",IF(C2>=5,"Pass","Fail")))

Written the other way round, the mildest test swallows every good result before the strict one is reached. Stop at three or four levels; past that, IFS reads better than a wall of parentheses.

Then test the boundary itself, not a comfortable middle value — that is where every error lives. And for deadline flags, =IF(D2<TODAY(),"Overdue","On time") only behaves if D2 holds a real date, since a date pasted as text compares like a word.

Step five: do the category totals reconcile?

SUMIF takes the criteria range first, the criterion second and the sum range third. The two ranges must be the same size and shape, and they are matched by position rather than by row number — which is why =SUMIF(D2:D200,"Hanoi",E5:E203) still returns a number, a wrong one that survives for months.

Criteria match the whole cell, so "Hanoi" will not count "Hanoi branch"; that needs "Hanoi*". The asterisk stands for any run of characters, the question mark for exactly one.

Now reconcile. Add up every COUNTIF result plus =COUNTBLANK(D2:D200) and the total must equal the number of rows in the category column. If it comes out short, a spelling variant is hiding in the data and it will split a category in the report you are about to send. For several conditions, COUNTIFS and SUMIFS exist — and they are not symmetrical: SUMIF wants the criteria range first, SUMIFS wants the sum range first.

Step six: is the lookup key identical on both sides?

VLOOKUP only searches to the right, so the key has to sit in the first column of the block you hand it. The column number counts inside that block, not from the sheet edge. The fourth argument should always be FALSE, for an exact match — a missing fourth argument defaults to TRUE, the most dangerous default in any spreadsheet, and it will return a plausible neighbour instead of an error.

#N/A means the keys genuinely differ. Confirm with =LEN on both sides: a trailing space or a doubled space changes the length. A number stored as text on one side and as a number on the other is the second cause; make both sides the same type with VALUE or TEXT. When the key sits to the right of the value you want, VLOOKUP cannot help — =INDEX(A2:A100,MATCH(A2,D2:D100,0)) looks in either direction. Lock the table with dollar signs before you copy the formula down, or row one works and everything below it breaks.

Step seven: are you hiding the error you need to see?

A report that goes to a manager should not print #N/A, #DIV/0! or #VALUE!. =IFERROR(your formula, what to show instead) does that in one wrapper — and also swallows #REF! when somebody deletes the column your formula depended on. That is the trade: a clean-looking blank where a real problem used to be.

IFNA is the safer wrapper for lookups, because it only replaces the not-found case and lets structural damage still show. Pick the replacement on purpose, too: empty text prints cleanly, a zero is easier to sum but drags totals down, and a short word such as "Check" is the honest choice in a file other people will edit.

The whole audit as a checklist

The shape that puts all seven to work

A monthly expense tracker is the smallest file that needs every one of them. Header row across columns A to E: Date, Category, Note, Amount, Method. Data runs from row 2 to row 201, a fixed block with room for the rest of the year.

The summary block sits to the right, one row per category, with the budget beside it:

Build it in that order and each piece is testable on its own: enter ten rows and confirm COUNT agrees, add the two totals, paste the SUMIF and copy it down, then bring the budget across with the lookup and let the nested IF write the status. Last step is the reconciliation from step five — the category totals must add up to the month total, and any row where =COUNTIF($H$2:$H$8,B2) returns zero is a typo or a blank category.

What the audit cannot fix

A formula is only as honest as the data typed into it. If the amounts were never verified against receipts, no function will rescue the number. Two structural habits also matter more than any single formula: keep merged cells out of data blocks, because sorting and filtering see only the first cell of a merged range, and avoid deleting rows that other sheets point at — clear their contents instead, so the lookups keep a target.

Everything here behaves the same way in Excel, Google Sheets, LibreOffice Calc and WPS Office, so the audit travels with you between files and between employers.

Run the seven questions before you send the file. It takes less time than answering the email that follows a wrong total.

Originally published at tanit365.com.