Netherfield Components pays a branch bonus at £150,000 of net revenue in the month. March's pack put Kirkgate at £148,920, and the bonus was withheld on £1,080. Kirkgate had finished the month at £176,430.
Nothing errored. The pivot refreshed every morning, the grand total moved on the 1 April refresh, and four branch totals looked exactly like four branch totals.
A pivot reads the range it was built on, not the sheet it sits in. This one was built on the 25th, when the export stopped at row 520, and six days of orders went in underneath it afterwards.
| Measure | What the pivot read | What the sheet held |
|---|---|---|
| Last row | 520 | 612 |
| Orders | 519 | 611 |
| Last order date | 25/03/2026 | 31/03/2026 |
| Net revenue | £462,720 | £516,500 |
| Kirkgate | £148,920 | £176,430 |
Three things kept it standing: the refresh moved the total, the grand total added up, and the one branch anybody spot-checked was the one that reconciled.
Scope. Worksheet pivot tables in Excel for Microsoft 365, 2021 and 2019, on Windows and Mac. The ribbon names are the Windows ones: PivotTable Analyze → Change Data Source.
A pivot fed by Power Query or the Data Model goes stale the same way for a different reason, and section 11 covers it.
Distribution is only the example. Any pivot over a sheet that grows — timesheets, tickets, donations, meter reads — fails as quietly as this one did.
01The Month the Pivot Stopped Reading
Four Branches, One Range That Stopped on the 25th
March's reconciliation for Netherfield Components, in A2:G9. Column B is how many of each branch's orders sat inside the pivot's source range, Orders!$A$1:$F$520, and column C is how many the orders sheet actually holds. Columns D and E are the branch totals the pack reported and the totals SUMIFS returns from the source. Nothing on this sheet errored and nothing was filtered: the pivot refreshed every morning and read the same 519 rows every time, because a pivot's source is a range typed into a dialog on the day it was built, and the sheet had grown 92 rows past it.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
The sheet above is March's reconciliation, one row per branch: what the pivot reported, what the orders sheet holds, and the gap between the two.
Saltaire reconciles to the penny. Every order it took landed before the 25th, so every row it has was inside the range the pivot was built on.
Kirkgate, Thornbury and Wetherby lost the orders they took in the last six days of the month. Ninety-two rows between them, £53,780 of net revenue, and no error anywhere on the sheet.
Scenario: Open the workbook your last pack came out of, put =SUM(Orders[Net]) beside the pivot's grand total, and subtract one from the other in a third cell. Anything but nought is a conversation worth having before the pack goes out.
02Refresh Re-reads the Range, Not the Sheet
Refresh does one thing. It reads the source range again and rebuilds the cache from what it finds there — not from beneath the range, beside it, or anywhere else on the sheet.
The source is written down once, on the day you build the pivot. Orders!$A$1:$F$520 was true on 25 March and stayed in the file long after it had stopped being true.
The 1 April refresh did move the grand total, by £310, because two credit notes had been applied to orders inside the range. That movement is what sold the number to everyone who saw it.
Refresh is maintenance, not a reconciliation. It answers one question — has anything in these cells changed — and it answers nothing else.
Scenario: Refresh your own pivot, then type a row of real data two rows beneath the last row of the source and refresh again. If the grand total does not move, the range stops above what you just typed.
Try it in the grid03Change Data Source: Where the Range Really Lives
Click any cell in the pivot, then PivotTable Analyze → Change Data Source. The dialog opens with the current range written out in full: Orders!$A$1:$F$520.
That line is the whole contract between the pivot and the data. Nothing else in the workbook states it, and nothing warns you when the sheet outgrows it.
The path is the same on Mac; in Excel 2016 it reads PivotTable Tools → Analyze → Change Data Source. Pivots built by somebody who has since left are where the surprises live.
Read the last row number in the box, then count the sheet. =COUNTA(Orders!A:A) returned 612 against the dialog's 520, which is the whole bug in two numbers.
Scenario: Open Change Data Source on a pivot you did not build, write the range down, and press Escape rather than OK. Then put =COUNTA(A:A) at the top of the source sheet and compare the two numbers.
04Make the Source a Table and It Grows Itself
Select a cell in the data and press Ctrl T. Excel turns the range into a Table and gives it a name, and that name is what the pivot should point at.
A Table's reference is its name, and the name covers whatever rows the Table currently holds. Paste 92 rows at the bottom and they are inside the Table, so the next refresh reads them.
CtrlTon the source data, with My table has headers ticked.- Table Design → Table Name, and call it
Orders. - PivotTable Analyze → Change Data Source, and type
Orders. - Refresh.
Two things still catch people out. A Table absorbs rows typed or pasted directly beneath its last row, and a Total Row sits in the way of anything pasted under it.
Paste into the row immediately below the last one. Leave a blank row between the Table and the new data and the Table has not grown: those rows are on the sheet, outside it, exactly as before.
Scenario: Convert your source with Ctrl T, point the pivot at the Table name in Change Data Source, then paste a block of new rows directly under the last one and refresh. They should appear without you opening another dialog.
05The Whole-Column Source and What It Costs
Orders!$A:$F never runs short, which is why it is the fix most people reach for first. It is also why so many pivots carry a (blank) item in every field.
Every empty row inside the source becomes a (blank) row in the pivot and a (blank) entry in every filter and slicer. Date grouping refuses to run at all when the column holds blanks or text.
| Source | Grows | Blanks | Grouping dates |
|---|---|---|---|
Orders!$A$1:$F$520 | no | none | works |
Orders!$A:$F | yes | (blank) everywhere | refuses |
Orders | yes | none | works |
Count them before you choose. =SUMPRODUCT(--(Orders[Branch]="")) returns the number of (blank) rows a whole-column source would hand the report.
Use a whole-column source when nothing else is available, such as a sheet somebody else owns and pastes into. Otherwise a Table costs one keystroke and brings no blanks with it.
06Two Cells That Catch a Short Source
Two numbers decide whether a pivot has read everything: how many rows it saw, and how recent those rows are.
Row count first. Drag the order reference into Values and set it to Count, then put the sheet's own count beside the pivot:
=COUNTA(Orders[Order ID])
519 in the pivot against 611 on the sheet is not a rounding difference. It is 92 orders, and =FILTER(Orders[Order ID],Orders[Date]>DATE(2026,3,25)) lists every one of them by reference.
Then the date, which is faster still, because it needs nothing added to the pivot:
=IF(MAX(Orders[Date])<EOMONTH(TODAY(),-1),"SHORT","covers the month")
March's source maxed at 25/03/2026 on a pack reporting a month that ran to the 31st. A source that stops six days early is visible in one cell, before anybody reads a figure.
Scenario: Put those two cells above your own pivot and label them. Refresh, then compare the count against the pivot's own row count, and the date against the last day you are reporting on.
Try it in the grid07The Grand Total Agrees With Itself, Not With You
A pivot's grand total is the sum of what it read. It is always internally consistent, which is why a short source produces a report that passes every check made inside it.
The only check worth making comes from outside the pivot, in a cell of your own:
=SUMIFS(Orders[Net],Orders[Branch],"Kirkgate")
£176,430 against the pivot's £148,920. The £27,510 between them is six days of orders, and it is also the difference between a bonus paid and a bonus withheld.
Line all four branches up at once. Put the source figure beside the pivot's with =XLOOKUP(A2,$I$2:$I$5,$J$2:$J$5), wrap it in IFERROR for a branch that exists on one side only, and read the differences down the column.
A difference concentrated in the newest rows is a short range. A difference spread evenly across every branch is usually a filter instead, which section 9 covers.
Scenario: Build that column beside your pivot: the SUMIFS figure from the source, the pivot's own figure, and =ROUND(B2-C2,2) between them. Read that column rather than reading the pack.
08Where New Rows Land: Beneath, Inside, Over
A fixed range fails differently depending on how the new rows arrive, and only one of the three ways is loud enough to notice.
| How the rows arrive | What the pivot does |
|---|---|
| Pasted beneath the range | ignores them, silently |
| Inserted inside the range | reads them, and the range grows |
| Pasted over the last rows | reads them, and loses what was there |
Rows inserted inside the range are picked up, because Excel adjusts the stored reference the way it adjusts any other. That is why the same workbook can behave for months on end.
The failure needs an append, which is exactly what a monthly export is. February's rows went in on an insert, in the middle of the sheet. March's went on the end.
Scenario: On a copy of the workbook, insert a row inside the source and refresh, then paste one beneath the last row and refresh again. Open Change Data Source after each and see which of the two moved the range.
Try it in the grid09Filters and Slicers Drop Rows the Pivot Did Read
A short source is not the only way rows go missing. A filter takes them out after the pivot has read them, and the report looks every bit as complete.
Check three things before you blame the range: the filter row above the pivot, the slicers wired to it, and the field filters on the row and column labels.
A slicer holds a list of item names, not a rule. Tick four branches today and a fifth branch opening next month is not on that list, so its orders arrive in the source and are shown nowhere.
One cell tells you which of the two you have. =COUNTIFS(Orders[Branch],"Kirkgate") against the pivot's own count of Kirkgate rows: equal means the rows were read and then filtered out.
Scenario: Clear every slicer on the sheet, drop every field filter, and note the grand total before and after. If it moves, the pack was filtered rather than short, and the source range was never the problem.
Try it in the grid10Old Items, and Two Pivots With Two Caches
Delete a branch from the source and its name can sit in the field list for months. Excel retains old items per field, and they turn up in filters, in slicers and as (blank) rows.
PivotTable Options → Data → Number of items to retain per field → None, then refresh. The list is rebuilt from what the source actually holds today.
The cache is the second surprise. Two pivots built from the same range in the same session share one cache, so refreshing either one refreshes both.
Two pivots built separately hold two caches. One sheet of the pack can be current and the sheet beside it a week old, so copy a pivot to make a second view rather than building it again from scratch.
11Power Query and the Data Model Refresh in an Order
A pivot over a query or the Data Model has another layer between it and the data, and each layer refreshes on its own.
Ctrl Alt F5, or Data → Refresh All, refreshes the queries, the model and the pivots in an order that usually works. On a chain of dependent queries it occasionally wants doing twice.
Opening the file refreshes nothing by default. Queries & Connections → right-click → Properties → Refresh data when opening the file, and the same box sits in PivotTable Options → Data.
A query has a short source of its own. A From Table/Range query reads the Table it was pointed at, so a range that never became a Table is this same bug, one layer further down.
12Five Checks Before the Pack Goes Out
- Does the pivot read every row?
=COUNTA(Orders[Order ID])against the pivot's own count of rows. March's returned 611 against 519. - How recent is the source?
=MAX(Orders[Date])against the last day of the period. March's returned the 25th. - Does the money reconcile?
=LET(sheet,SUM(Orders[Net]),pivot,F3,ROUND(sheet-pivot,2)), where F3 is the grand total. Nought, or the pivot is reading something you are not. - Is anything filtered? Clear the slicers and the filter row, then check whether the grand total moves.
- Are all the groups there?
=COUNTA(UNIQUE(Orders[Branch]))against the rows in the pivot. One short means a slicer, a filter or a range.
13Ten Traps
- A pivot stores a range, not your sheet. Refresh re-reads that range and nothing else.
- A refresh that moves the total proves nothing. It means something inside the range changed.
- Appended rows are the silent case. Inserted rows are picked up; rows pasted beneath are not.
- A Table is the fix. Its name covers whatever rows it holds today.
- A Table only absorbs rows directly beneath it. A blank row, or a Total Row, leaves them outside.
- Whole-column sources bring
(blank)with them. And date grouping then refuses to run. - The grand total is self-consistent. Only a figure from outside the pivot can contradict it.
- A slicer is a list of names. An item that did not exist when you ticked it is not on the list.
- Old items linger in the field list. Retain per field → None, then refresh.
- Two pivots can hold two caches. Copy a pivot rather than building a second one.
14Mini Exercises
- Break it on purpose. Build a pivot on
A1:F20, paste five rows at row 21, refresh, and watch the totals stay exactly where they were. - Read the contract. Open Change Data Source on that pivot and compare the range in the box against the last row of your data.
- Convert and re-point. Press
CtrlTon the source, name it, point the pivot at the name, paste five more rows and refresh. - Date it. Put
=MAX(Orders[Date])above the pivot, then delete the last week of rows. That cell moves before any total does. - Filter instead. Restore the rows, tick three branches of four in a slicer, and reconcile with
SUMIFSuntil you can tell a filter from a short range by the shape of the difference.
What to Take Away
Nothing in this workbook was broken. The pivot was correct arithmetic over the rows it had, the refresh ran every morning, and the orders sheet held every order Netherfield took in March.
What nobody had written down is that a pivot's source is a range typed into a dialog on the day it was built. The sheet grew by 92 rows. The range did not.
Two cells say so before a pack leaves the building: the row count against the pivot's own, and the last date in the source against the last day of the month.
Two cells, against £53,780 missing from a board pack and a branch told it had missed its bonus by £1,080 in a month it cleared by £26,430.