Pivot tables and SUMIFS formulas fail quietly on messy data: they return zeros, split one category into two, or skip rows you meant to include. Ten quick checks, each with a one-line test, catch almost everything before you build anything on top of the sheet.
What are the ten checks?
- One header row, with a unique name in every column and nothing above it.
- No blank rows or blank columns inside the data.
- No merged cells anywhere in the table.
- Real dates, not text that looks like dates.
- Numeric amounts, not text with commas, letters or currency symbols.
- One sign convention: spending always positive or always negative.
- Trimmed text, with no leading, trailing or doubled spaces.
- Consistent category names, spelled identically each time.
- No totals, subtotals or notes sitting inside the data range.
- No duplicate rows, and a grand total that matches the source.
Do the checks in order. Early problems, such as merged cells or a stray second header, hide later ones, so a clean pass at step three saves rework at step eight. Keep a note of which checks you ran and on what date, since the same file will return next month with new rows.
How do I test each one quickly?
| Check | Quick test | Fix |
|---|---|---|
| Real dates | =COUNTA(A2:A500)-COUNT(A2:A500) should be 0 | DATEVALUE, or Text to Columns |
| Numeric amounts | =COUNTA(D2:D500)-COUNT(D2:D500) should be 0 | SUBSTITUTE commas, then VALUE |
| Trimmed text | =SUMPRODUCT(--(LEN(B2:B500)<>LEN(TRIM(B2:B500)))) | Helper column with TRIM |
| Blank rows | =COUNTBLANK(A2:A500) | Sort or filter blanks, delete rows |
| Duplicates | =COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2,$D$2:$D$500,D2)>1 | Look, then delete true repeats |
The first two tests count cells that are filled but not numbers. A result of 0 is a pass. In the blank-row test, remember that the range must stop where your data stops, or it counts empty space below.
Why do dates and amounts need so much attention?
Because a pivot or a SUMIFS treats text as text. A date entered as 03.04.2026 or 3rd April may sit in the cell as characters, and no month grouping will ever pick it up. Left-aligned entries are the usual giveaway. An amount like 1,234.50 Dr has the same issue. Fix both before anything else; they account for most pivots that show a lot of zeros.
How do I keep category names consistent?
A pivot table treats Food and Food followed by a space as two different items, and so it does for Groceries and Grocery. The most reliable fix is prevention: add a drop-down list to the category column through Data, Data validation, List, pointing at a single master list of categories. Then check by sorting the pivot's row labels: any odd entry stands out.
Before running the tests, clear any filters and unhide all rows and columns. Hidden rows still count in formulas, and a filtered view can make a pivot's total look wrong when it is only hiding part of the data. Select the whole sheet, right-click a row number and choose Unhide, then do the same for columns. Text numbers can often be converted in one step: select the column, choose Data, then Text to Columns, and press Finish.
What do I do when the totals disagree?
Add up the amounts on the sheet and compare with the figure on the bank statement or the source file. If they differ, find the gap in halves: total the first half of the rows, then the second, and compare each with the source. Usually one blank row, one text amount or one pasted subtotal row is to blame.
Before you start the pivot
- Save a copy of the raw file.
- Run the five tests above.
- Fix, retest and write the date of the cleanup in a note.
When the monthly clean-up becomes a chore, SpendixAI lets you review statement rows before saving and keep your own categories, so the data starts out tidy and the pivot work stays optional.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

