You add six rows to the source data, click Refresh, and the PivotTable still shows last week's total. Or a deleted product stays in the filter list. Or the grand total updates but the calculated field does not. None of these mean the PivotTable is broken — they point to a specific layer: source range, cache, connection or calculation order.
Quick answer: convert the source range to an Excel Table (Ctrl + T) so new rows are included automatically, then use PivotTable Analyze → Refresh (Alt + F5). If the total is still stale, check Change Data Source, the pivot cache, and whether the source formulas have recalculated before the refresh.
Match the Symptom to the Fix
| What you see | Likely cause | First fix |
|---|---|---|
| New source rows are missing | The source is a fixed range, not an Excel Table | Ctrl + T, then Change Data Source to the table name |
| Nothing changes after Refresh | Wrong source, wrong cache or a manual refresh rule | Check Change Data Source and Refresh All |
| Deleted items still appear in a filter | The item is retained in the pivot cache | Refresh, then use Field Settings → Layout & Print |
| Totals update but a calculated field does not | Calculated fields use cached source values | Refresh source formulas first, then refresh the pivot |
| Only one PivotTable updates | The tables use different caches | Point them at the same source, or refresh each cache |
| Refresh is greyed out or errors | Broken external connection or protected sheet | Data → Queries & Connections; Review → Unprotect |
1. Replace the Fixed Source Range With a Table
This is the cause in most student workbooks. A PivotTable built from Sheet1!$A$1:$D$200 will never see row 201, no matter how many times you refresh. The source must grow with the data.
- Select one cell in the source data and press Ctrl + T.
- Confirm My table has headers and click OK.
- Give the table a clear name in Table Design → Table Name, for example
tblSales. - Select the PivotTable, open PivotTable Analyze → Change Data Source, and enter
tblSales. - Add a test row at the bottom of the table and refresh. The new row should appear in the pivot.
A Table also copies formulas down automatically and keeps formatting consistent, which removes two other common sources of stale pivots.
2. Refresh the Right Layer
Excel has three refresh actions and they are not interchangeable:
- Refresh (Alt + F5) reloads the active PivotTable from its cache.
- Refresh All (Ctrl + Alt + F5) refreshes every PivotTable and data connection in the workbook.
- Refresh on a Power Query connection re-runs the query before the pivot reloads.
If the source data lives in another workbook that is closed, Excel may use the last saved copy. Open the source workbook, refresh, then refresh the pivot.
To make refresh automatic, right-click the PivotTable → PivotTable Options → Data → tick Refresh data when opening the file. This is useful for dashboards, but it can slow a large workbook, so use it deliberately.
3. Check the Pivot Cache, Not Just the Pivot
A PivotTable stores a copy of the source values in a cache. Two pivots created separately can hold two caches, so refreshing one leaves the other stale. Select each PivotTable and compare Change Data Source; if the ranges differ, point them at the same Table.
To force a clean rebuild, copy the sheet, delete the PivotTable, and insert a new one from the Table. This is often faster than hunting a corrupted cache. If the workbook has many linked pivots, use PivotTable Analyze → Options → Data → Refresh data when opening the file on each one.
4. Fix Filters That Hide New Items
A field filter can be set to a fixed list, so a new region or product is excluded even after refresh. Open the filter drop-down and choose Clear Filter, or use Field Settings → Layout & Print and confirm Show items with no data if the rubric expects a complete list.
For report filters, check PivotTable Analyze → Filter → Connections. If one slicer is connected to three pivots, it should update all three; if it does not, the connections are incomplete.
5. Refresh Source Formulas Before the Pivot
If a source column contains formulas, the PivotTable caches the values from the last calculation. A calculated field inside the pivot is different: it uses the cached source fields and recalculates when the pivot refreshes. The order matters.
- Press F9 or Ctrl + Alt + F9 to recalculate the workbook.
- Confirm the source column shows the new values.
- Refresh the PivotTable.
If the source formula itself is stuck, see the formula not calculating guide before blaming the pivot.
6. Fix Grouped Dates and Numbers
Grouped date fields keep the original grouping until you refresh. If a new month is missing, refresh the pivot, then right-click the date field → Group and extend the range. Text dates cannot be grouped at all, so convert them first with the date sorting and conversion guide.
For numeric groups, check the Starting at and Ending at values. A group ending at 100 will never show a new value of 120.
7. Refresh External Data and Power Query Connections
When the source is a CSV, database or web query, the PivotTable depends on the connection. Go to Data → Queries & Connections, right-click the connection and choose Refresh. Then refresh the pivot.
Check the connection properties: Enable background refresh can make a pivot appear to finish before the data arrives, and Prompt for file name can block an unattended refresh. For a submission, test the workbook on a clean machine and confirm the connection path is not pointing at your Downloads folder.
8. Clear Retained Items and Rebuild a Corrupt Cache
Deleted products can remain in a filter because the cache retains them. Refresh first. If they persist, open PivotTable Options → Data and check Number of items to retain per field; setting it to None stops old items being kept, at the cost of a slower refresh.
If Refresh produces an error or the file behaves unpredictably, rebuild: insert a new worksheet, create a fresh PivotTable from the Table, and delete the old one. Then reapply number formats, sorting and slicer connections. A clean rebuild is a legitimate fix, not a failure.
A Refresh Checklist You Can Reuse
- Source is an Excel Table with a clear name.
- Every PivotTable points at that Table, not a fixed range.
- Source formulas recalculate before the pivot refreshes.
- Filters are cleared or set to include new items.
- Date and number groups cover the new range.
- External connections point to a path the grader can open.
- Refresh All is tested after reopening the file.
Before You Submit
Add a row to the source, refresh, and check that the total changes by the expected amount. Then delete that row, refresh again, and confirm the total returns. Open the workbook on another machine if possible — a pivot that only refreshes on your laptop will lose marks in a lab or on a grader.
If the PivotTable is part of a dashboard, see the dashboard assignment guide and the Excel dashboard help page. For a PivotTable-specific assignment, use the PivotTable assignment help page. Clean the source first with the free Data Cleaner, then audit the formulas with the Workbook Health Check.
