Blog · How-to guides · Finance and FP&A teams
The five checks a commercial analyst runs on a workbook before its totals go into a deck: the SUM range covers every row, hidden and filtered rows are counted or excluded on purpose, no number is typed over a formula, the pivot matches the sheet, and the total matches the source system. Each with the symptom it catches and the one-minute test.
A workbook total is a number somebody typed, a formula somebody wrote, or a pivot somebody refreshed, and it has been right often enough that nobody checks it. This guide is the five checks, the symptom each catches, and the test for each that takes a minute.
| # | Check | Symptom it catches | Test |
|---|---|---|---|
| 1 | SUM range ends at the last row | Total stops growing when rows are appended | Compare range end to the last data row |
| 2 | Hidden and filtered rows handled on purpose | Regional view shows the company total | SUBTOTAL(109) beside SUM; they differ when filtered |
| 3 | Every total-column cell is a formula | One row typed over, total wrong by that row | Show formulas; scan for constants |
| 4 | Pivot total equals sheet total | Pivot cached, sheet updated | Refresh; compare |
| 5 | Sheet total equals the source system | Filtered or truncated export | Compare to the finance screen |
A total of =SUM(C2:C480) on a sheet with 512 rows is thirty-two rows short. The symptom is a total that looks like last month's, because it is. The test: the range's last row against the last row with data. Better: a structured table, whose total row grows with it.
SUM counts what it cannot see. SUBTOTAL(109, …) counts only visible rows. Put both under the column. When they differ, the sheet is filtered or has hidden rows, and the report has to say which total it means.
Somebody adjusted one row by typing the number they wanted. The formula is gone, the total moves by the difference, and next month's refresh does not touch it. Show formulas, scan the column for cells without an equals sign, or use a conditional format that highlights constants.
A pivot table is a snapshot. The sheet was updated; the pivot was not refreshed. The pivot's grand total and the sheet's column total should agree after refresh; if they do not after refresh, the pivot's source range has the problem from check one.
sheet total = source system total, same period, same filter
The only check that tests the file against the world rather than against itself. If the source shows $4.18m for March and the sheet shows $3.96m, the export was filtered, truncated or missed a day, and no amount of formula auditing finds that.
| Check | Result | Finding |
|---|---|---|
| 1 | Range to row 480; data to row 512 | 32 rows, $141,000, missing |
| 2 | SUM and SUBTOTAL differ | Sheet filtered to North; total is company |
| 3 | Row 207 is a constant | Overwrite of $18,000, no formula |
| 4 | Pivot differs by $141,000 | Same cause as check 1 |
| 5 | Sheet $3.96m; source $4.18m | Export missed the last day |
Five checks, four findings, one workbook that had been the source of the board's revenue figure for three months.
Only check five. The reconciliation fails and nobody knows why. Checks one to four say why.
Only checks one to four. The workbook is internally consistent and the export was wrong.
Checks done once. Every check is re-run on every refresh, or automated.
Constants tolerated. One is an adjustment; ten is a second data source nobody wrote down.
An upload that goes through validation gets checks one to five run on it as a report, before anything is computed. Covirage does this on every file. The Excel analysis solution describes the setup, and the export preparation guide covers what to do before the file reaches the workbook.
The SUM range. Rows are appended below the range and the total stops growing. It is invisible because the total is still a plausible number, just last month's.
SUM counts filtered-out rows; SUBTOTAL with function 109 does not. A sheet filtered to one region with a SUM at the bottom shows the whole company's total under a regional heading.
Then the file has no anchor, and the report should say the total is unreconciled. A number without a source figure to check against is a claim about the spreadsheet, not about the business.