Pivot Table Not Refreshing? Eight Causes, and the Fix for Each
Almost every refresh problem comes down to one of eight things, and they look identical from the outside — the number did not change. The first job is working out which of the eight you have.
Short answer
First, work out which failure you have
| What you see | Most likely cause | Where to look |
|---|---|---|
| The totals are unchanged after refreshing | The source has not changed, or the pivot table reads a different cache | Cause 1 and Cause 5 |
| New rows are missing from the report | The source range or Table does not include them | Cause 1 and Cause 2 |
| Refresh errors or asks for a file | The source workbook or sheet is gone | Cause 3 and Cause 8 |
| Some rows are missing and the total looks plausible | A filter is applied somewhere | Cause 4 |
| The row appears but the amount is counted, not summed | The value column contains text | Cause 6 |
| A new month is missing while older ones are fine | The date grouping was cached | Cause 7 |
Do the deliberate-change test first
Cause 1: the source range is fixed
When a pivot table is built on a plain range, that range is recorded at the moment of creation. Rows added below it are invisible — the pivot table refreshes, succeeds, and shows the same old figures.
| Symptom | Fix it for now | Fix it permanently |
|---|---|---|
| Rows added below the last row of the range | PivotTable Analyze → Change Data Source → extend the range | Convert the source to a Table with Ctrl+T and rebuild the pivot table on it |
| A column of new data added to the right | Same — Change Data Source | Same |
| The range was correct last month and is wrong this month because somebody deleted rows | Change Data Source back | A Table cannot shrink when rows are deleted, so it handles this automatically |
Ctrl+T is the fix for most refresh problems
Cause 2: the Table exists but does not cover the rows
A Table expands automatically, but only when you type in the row immediately below it. If somebody inserted a blank row, or pasted data three rows further down, the pasted rows are outside the Table and the pivot table will not include them.
Find the real end of the Table
Compare against the real end of the data
Resize the Table
Find out why it happened
Cause 3: the source is gone
| What happened | What Excel does | What to do |
|---|---|---|
| The source sheet was deleted | The pivot table keeps its cached copy and shows old figures; refreshing errors | Undo the deletion, or repoint the pivot table at a live source |
| The source sheet was renamed | Usually still works if the range reference survives; a named-source reference breaks | Change Data Source |
| The source is another workbook that moved | The refresh raises a file-not-found message | Update the connection path, or bring the source into the same workbook |
| The source is on a network path and you are off the network | Refresh fails or hangs | Neither is fixable in the moment — copy the source locally to work on it |
The dangerous one in that table is the first. A pivot table whose source has been deleted keeps displaying the last figures it had, with no visible warning, and anybody who opens the workbook sees a tidy report built on data that no longer exists.
Cause 4: something is filtering rows out
| Filter | Where it hides | Effect |
|---|---|---|
| A row label filter on the pivot table itself | The filter icon on the row field | Categories excluded from the report even though they are in the source |
| A report filter selection | The drop-down in the Filters area above the pivot table | The whole report is limited to one selection, and the state is easy to miss |
| An AutoFilter applied to the source range | The filter arrows on the source sheet's header row | Rows hidden on the source may be excluded from the pivot table |
| A slicer connected to this pivot table | A slicer panel somewhere else on the sheet, or on another sheet | The selection is set once and stays set for everybody who opens the file after you |
The slicer case is worth calling out separately because it produces the most confident wrong answers. Somebody set a slicer to one branch six months ago. Every report since has been for that branch, and nothing on the reporting sheet says so unless the slicer is on it.
Cause 5: two pivot tables, two caches
Pivot tables built from the same source in the same session usually share a cache, and refreshing one updates both. Pivot tables built at different times, on different ranges, or from different sheets each have their own. Refreshing one leaves the others showing the data as of whenever they were last refreshed.
| Do this | What it refreshes |
|---|---|
| Right-click a pivot table → Refresh | That pivot table, and any others sharing its cache |
| Data tab → Refresh All | Every query and every pivot table in the workbook |
| Alt+F5 | The pivot tables on the active sheet |
Make Refresh All the habit
Cause 6: the refresh works and the number is still wrong
A pivot table can refresh perfectly and still show the wrong answer, because the fault is in the data rather than in the refresh.
| What the report shows | The underlying cause | The check |
|---|---|---|
| Count where you expect Sum | At least one cell in the value column is text | Go To Special → Constants on the column and look for left-aligned or blank-looking cells |
| A total that is slightly lower than expected | Text values were excluded when the field was set to Sum | Compare the pivot total against a SUM of the source column — the gap is the text values |
| A category that should be one appears as three | The same value written three ways | Build a distinct-value list on the category column |
| A month appears twice | Some dates are real dates and some are text | Add a column with ISNUMBER on the date field and filter for FALSE |
The second row is the one that costs money. Once you change a value field from Count to Sum, the text cells drop out of the total silently — the number becomes believable and stays wrong. Find and fix the text values, and let the total move. The defensible habit is the one-liner comparison: put a SUM of the source column beside the pivot table's grand total and confirm they agree. If they do not, the pivot table is not summarising everything, and the difference is exactly what it is missing.
Cause 7: the date grouping is stale
Grouping dates into months, quarters and years records the group definitions at the moment you grouped. A month that did not exist in the data then may not appear in the grouping now, even after a refresh.
- 1Right-click any date in the row or column labels and choose Group.
- 2Check whether the month you expect is in the list of available groups.
- 3If it is not, ungroup the field, refresh, and group it again.
- 4Better: add a Month column to the source as text —
TEXT(A2,"mmm-yy")or a financial-period column — and group by that instead. - 5Better still for Indian reporting: add a Financial Year column, because Excel's grouping knows nothing about an April-to-March year.
Grouping on a column you control also removes the whole class of problem in cause 6 where some dates are text and some are not — the month column is text on purpose and behaves consistently.
Cause 8: the source is external and unreachable
- A connection path that no longer resolves — a file moved, a folder renamed, a drive letter that only exists on one machine.
- Credentials for a database or a SharePoint list that have expired or that the current user does not hold.
- A workbook locked by somebody else having the source file open. This is the most common cause of refresh failures that make no sense at the time.
- Privacy levels blocking a query that combines two sources.
The general lesson is that a workbook which depends on external files is only fully functional on a machine that can see those files. If the report is going to be read rather than refreshed on somebody else's computer, it should be saved with the figures as values on a copy that is clearly marked as a snapshot.
Making it not happen again
| Habit | What it prevents |
|---|---|
| Source data in a Table, not a range | Causes 1 and 2 |
| Refresh All rather than a right-click refresh | Cause 5 |
| A check row beside the pivot table comparing its total to a SUM of the source | Cause 6, and any other silent omission |
| A month or period column added to the source rather than using Excel's date grouping | Cause 7 |
| Delete nothing on a source sheet once a pivot table is built on it | Cause 3 |
When the refresh is not the real problem
If the report exists in a fixed format and the data behind it arrives as twelve files that somebody combines by hand each month, the refresh is working as designed — the problem is upstream of it. The answer is to stop rebuilding the source every period: combining multiple Excel files and Power Query between them cover that, and it turns the monthly rebuild into one refresh.
And if what the pivot table is summarising has to be reconciled before it means anything — a payable balance, a GST position, a vendor statement — then the accuracy of the report is decided before the pivot table is involved. Rest of the chain: the pivot table guide for the layout side, the reconciliation guide for the accuracy side, and Piloteq Automate for the matching step in between.
If you would rather not build this by hand
When the report is stuck because the data is not ready
A pivot table can only refresh from data that has been assembled and agreed. Piloteq Automate handles the step before that — matching two sets of records, applying the same rules each period and explaining every difference — so what reaches the pivot table is settled.
- ✓Same match rules applied every period
- ✓Tolerance, group and duplicate rules
- ✓Every exception carries a reason
- ✓Comparisons produced the same way each time
Frequently asked questions
Why is my pivot table not picking up new rows?+
The source range was fixed when the pivot table was created and the new rows fall outside it. Convert the source to an Excel Table with Ctrl+T and rebuild the pivot table on that table; a table expands automatically as rows are added, so the pivot table cannot fall behind again. Alternatively use Change Data Source to extend the range — which fixes it this time and not next time.
Why does Refresh appear to do nothing?+
If the underlying data has not changed, refreshing genuinely produces the same numbers, so nothing looks different. Before assuming a fault, change one value in the source deliberately and refresh again. If the number moves, the refresh is working and the problem is something else — most often that the rows you expect are outside the source range, or that the figures were correct all along.
Why is the Refresh button greyed out?+
Usually one of three things: the sheet containing the pivot table is protected, the workbook is open in a mode that restricts changes, or the pivot table is on a sheet whose protection blocks refresh. Unprotect the sheet — the protection setting on the Review tab has a specific option that allows using pivot tables but not refreshing them.
Why does the pivot table show old data after I refreshed?+
Check whether the pivot table is reading from a different cache than you think. Several pivot tables in one workbook each have their own cache unless they were built from the same source in the same session; refreshing one does not update the others. Refresh All on the Data tab updates every pivot table and query in the workbook, which is the safer habit.
Why is a new month missing from my grouped dates?+
Grouping a date field records the group definitions at the time you grouped it. A new month can be present in the source and absent from the group structure. Right-click the date field in the row or column labels, choose Group, and check the range — or ungroup, refresh, and regroup. This is easier to avoid by grouping on a month column you added to the source rather than on Excel's automatic date grouping.
Does a pivot table refresh automatically when the file is opened?+
Only if you have enabled it: PivotTable Options → Data → Refresh data when opening the file. Even then, it only works if the source is reachable at that moment. A workbook whose source is on a network path, or is another workbook that has moved, will show cached figures or an error — and a recipient who does not have the source will see whatever was cached when the file was last saved.