Of all the ways to misunderstand a PivotTable, this one costs the most, and it deserves its own reflex.
Why doesn't a pivot table update, and how do you refresh it?
A PivotTable doesn't read your source live. When you create it, Excel takes a snapshot of the source into a hidden pivot cache. Every drag, sort, filter, and grouping after that reads from the cache and never touches the cells on your data sheet. The consequence is stark. Edit a value in the source and the pivot doesn't change.
Say you fix a fat-fingered ₹90,000 that should have been ₹9,000. You glance at the pivot and see the old total, and if you don't know about the cache, you ship it. There's no error and no warning, just a confident wrong number. It's the bare VLOOKUP disaster from the lookups lessons all over again.
The cure is one word: Refresh. After any change to the source, refresh the cache so the snapshot re-reads the data:
- For the active pivot, PivotTable Analyze ▸ Refresh, or
Alt+F5. - For every pivot and query in the workbook, Data ▸ Refresh All, or
Ctrl+Alt+F5.
Build the habit into your hands. Touch the source, then Refresh before you read. You can also set PivotTable Analyze ▸ Options ▸ Data ▸ "Refresh data when opening the file" so a stale cache can't survive a reopen.
Advertisement
Why doesn't a pivot table pick up new rows?
The second half of the trap is that a fixed range never grows. Even a diligent Refresh will miss data if the source range itself is frozen. Create a pivot on A1:D13 (thirteen rows), then append row 14 and Refresh. Row 14 is ignored, because the source is literally the range A1:D13, and refreshing re-reads that range and nothing beneath it. The number stays too low forever, and nothing on screen tells you so.
The fix is why you'll keep hearing about Tables. Make the source a Table (Ctrl+T) before you build the pivot. A Table auto-expands as you add rows, so the pivot's source is the Table's name, Sales, which always spans all current rows. A Refresh then picks up every appended transaction automatically, with no "Change Data Source" chore. The manual alternative is PivotTable Analyze ▸ Change Data Source and re-dragging the range after every append, which is the tedious, forgettable step a Table eliminates.
What keeps a pivot table always current?
Put the two halves together and the rule is simple and absolute: Table source + Refresh reflex = a pivot that is always current. Neglect either and you've built a summary that lies with total confidence. A pivot's entire job is to be the number people trust, so for a pivot that's the worst failure there is.