The Pivot Table Mistake That Makes New Data Disappear (And the 30-Second Fix)
In this Excel Pivot Table Tutorial, you add a week of new rows to a tracking sheet, hit Refresh on the pivot table sat next to it, and the new rows simply aren’t there. Refresh All doesn’t help. Closing and reopening the file doesn’t help. The data is definitely in the sheet, but the pivot table acts like it isn’t.
This is one of the most common breakages we see corrected in Excel training sessions, and it isn’t really a pivot table bug. It’s a range problem.
Why it happens
When you build a pivot table from a plain cell range (say A1:F200), Excel locks that exact address in as the source. Refresh recalculates the pivot from that same address every time; it does not go and check whether your data now runs to row 400. Anything below row 200 is invisible to the pivot, refresh or no refresh, because the pivot was never told the data grew.
How to catch it in the act
Click anywhere in the pivot table, go to PivotTable Analyze, and select Change Data Source. Look at the range Excel shows you. If it’s a fixed address like Sheet1!$A$1:$F$200 and your data now runs past row 200, that mismatch is the entire bug, sitting right there on screen.
Excel Pivot Table Tutorial – The fix that stops it happening again
Select your source data and press Ctrl+T to convert it into an Excel Table before you build (or rebuild) the pivot from it. A Table has a name (Table1, SalesLog, whatever you set) instead of a fixed address, and that name automatically expands as you add rows directly underneath it. Build the pivot from the Table name rather than a cell range, and every future Refresh picks up new rows without anyone touching the data source again.
If you already have a pivot built on a plain range and don’t want to rebuild it, go back into Change Data Source, delete the fixed address, and either type the Table name (once you’ve converted the source to a Table) or, at minimum, retype the range generously beyond your current row count as a stop-gap. It is a stop-gap, not a fix — the moment your data outgrows the new upper limit, the same problem returns, so the Table conversion is worth doing properly.
Three checks worth running on your own workbooks
1. Right-click the pivot, open PivotTable Options, and check the Data tab. If the source name starts with a sheet name and a cell address rather than a Table name, it is exposed to this bug.
2. Add one test row well below your current last row of data, hit Refresh, and confirm it actually appears in the pivot. If it doesn’t, you’ve found a live version of this problem before it costs anyone a wrong number.
3. If several pivot tables were built off the same source (common when one gets copy-pasted to make another), check each one individually — copy-pasting a pivot table does not guarantee they all point at the same, correctly-scoped source.
The reason this one matters more than most spreadsheet quirks is that it fails silently. A formula error throws #REF! or #VALUE! and gets noticed immediately. A pivot table quietly built on a stale range just under-reports, and it looks completely normal until someone cross-checks the totals against the source data by hand — usually after a decision has already been made on the incomplete numbers. If your reporting relies on lookups against the same kind of static range rather than a Table, the article on doing a Vlookup in Excel on this site (https://officeskills.org/lookup-information-worksheet-using-vlookup/) is worth a look too, since range references are exactly what to check there as well.
Author Bio: Sam writes training content for Future Savvy, a UK provider of instructor-led Excel, Power BI and SQL courses for business teams (https://www.futuresavvy.co.uk) — this is a fix we walk delegates through in nearly every intermediate Excel session
