If your Excel pivot table is not updating when you refresh, the most common cause is that new rows or columns were added outside the original data range, and the pivot table’s source is still set to the old range. To fix it immediately, select any cell in the pivot table, go to PivotTable Analyze > Change Data Source, and update the range to include the new data, then refresh.
Why Pivot Tables Don’t Always Refresh Automatically
Pivot tables are not live views of your data—they are snapshots. When you refresh, Excel re-reads the source range and recalculates the pivot cache. However, the pivot cache only looks at the exact range you specified when you created the pivot table. If you add rows below the range or columns to the right, Excel doesn’t know they exist unless you explicitly expand the source range. This is by design: it prevents accidental inclusion of stray data, but it means you must manage the source range manually or use a dynamic range.
How to Refresh a Pivot Table (and Force It)
Refreshing is straightforward, but there are several ways to do it, and some are more thorough than others.
- Single refresh: Right-click inside the pivot table and choose Refresh. Or go to PivotTable Analyze > Refresh (on Windows) or Data > Refresh (on Mac).
- Refresh all: Go to Data > Refresh All (Windows) or Data > Refresh All (Mac). This refreshes every pivot table and query in the workbook.
- Keyboard shortcut: Press Alt+F5 (Windows) to refresh the current pivot table, or Ctrl+Alt+F5 to refresh all. On Mac, use Command+R for refresh all.
- Force a full recalculation: If the pivot table uses calculated fields or items, or if data changed but the refresh seems incomplete, go to PivotTable Analyze > Options > Data tab and check “Refresh data when opening the file.” You can also manually press Ctrl+Alt+F5 to refresh all and recalculate.
Common Reasons Refresh Doesn’t Work
Beyond a static source range, several other issues can make a pivot table appear stuck.
- Data source is a named range that isn’t dynamic: If you used a named range like
Datathat points to a fixed range (e.g.,=Sheet1!$A$1:$D$100), new rows beyond row 100 won’t be included. Convert it to a dynamic named range using an Excel Table or the OFFSET function. - New columns added: If you added a new column to the right of the source data, the pivot table won’t see it until you change the source range to include that column.
- Data is in a different sheet or workbook: If the source is external, ensure the connection is still valid. For Power Pivot or external data, refresh may require a connection refresh.
- Filter or slicer is hiding new data: Sometimes the data is there, but a slicer or filter is excluding it. Check the filter dropdowns and slicer selections.
- Manual calculation mode: If your workbook is set to manual calculation, refreshing the pivot table may not recalculate formulas that feed the source data. Go to Formulas > Calculation Options and set to Automatic.
How to Make a Pivot Table Automatically Include New Data
The best long-term fix is to convert your source data to an Excel Table (Ctrl+T). Excel Tables are dynamic by nature—when you add a row or column, the table expands automatically, and any pivot table based on that table will pick up the new data on refresh without you changing the source range.
- Select any cell in your data range.
- Press Ctrl+T (Windows) or Control+T (Mac) to create a table. Ensure “My table has headers” is checked if applicable.
- Click OK. The table gets a default name like Table1.
- Now create your pivot table from this table: select any cell in the table, go to Insert > PivotTable, and the range will show as the table name (e.g.,
Table1).
If you already have a pivot table based on a normal range, change its source to the table name: select the pivot table, go to PivotTable Analyze > Change Data Source, and enter the table name (e.g., Table1) in the range box.
Using a Dynamic Named Range as an Alternative
If you prefer not to use an Excel Table, you can define a dynamic named range using the OFFSET function. This is useful when your data has blank rows or you want to control the range precisely.
Assume your data is in columns A through D, with headers in row 1, and data starting in row 2. Go to Formulas > Name Manager > New. Name it DynamicData. In “Refers to,” enter:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
This formula counts non-empty cells in column A to determine the number of rows, and non-empty cells in row 1 to determine the number of columns. It assumes no blank rows in column A and no blank columns in row 1. Then, when you create or change the pivot table source, use DynamicData as the range. Refresh will now pick up new rows and columns.
Refreshing Pivot Tables with VBA (When You Need Automation)
If you want to refresh all pivot tables automatically when a workbook opens, or on a button click, you can use a simple VBA macro. Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.
Sub RefreshAllPivots()
Dim ws As Worksheet
Dim pt As PivotTable
' Loop through every worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Loop through every pivot table on that sheet
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
To run this, press Alt+F11 to open the VBA editor, insert a new module, paste the code, and run it. Alternatively, you can assign this macro to a button on the sheet.
Frequently Asked Questions
Why does my pivot table not show new rows even after refresh?
This almost always means the source range is static. Check the source range in PivotTable Analyze > Change Data Source. If it shows a fixed range like $A$1:$D$100 and you added row 101, you need to either update the range to $A$1:$D$101 or, better, convert the data to an Excel Table. After converting, refresh again.
Can I make a pivot table refresh automatically when data changes?
Yes, you can set the pivot table to refresh when the file opens: right-click the pivot table, choose PivotTable Options, go to the Data tab, and check “Refresh data when opening the file.” For real-time updates while working, you would need a macro that refreshes on worksheet change events, but that can slow down your workbook.
Why does my pivot table show old data even though I refreshed?
If the source data is in another workbook and that workbook is closed, Excel may be using the cached data. Open the source workbook or ensure the connection is set to refresh from the source. Also check if the pivot table is based on Power Pivot or an OLAP cube—those may require a different refresh method.
How do I refresh multiple pivot tables at once?
Go to Data > Refresh All (or press Ctrl+Alt+F5). This refreshes all pivot tables, queries, and data connections in the workbook. If you only want to refresh pivot tables on a specific sheet, use the VBA macro shown above.
Does changing the source range to a table break anything?
No, but if your pivot table has calculated fields or items, they will continue to work as long as the field names remain the same. Also, if you have slicers connected, they will still work. Just ensure the table headers match the original column headers.
Preventing Refresh Problems in the Future
To avoid these issues, adopt these habits:
- Always use Excel Tables for source data.
- Keep the source data on a separate sheet from the pivot table to avoid accidental overlap.
- Use the “Refresh data when opening the file” option for reports that others will use.
- If you add columns frequently, consider using a dynamic named range or a table.
- Periodically check the pivot table source range after major data edits.
By understanding how the pivot cache works and using dynamic sources, you can eliminate most refresh issues and keep your reports accurate.
Related
- Excel Eye Center: Services, Costs & What to Expect
- Excel Employee Rotation Schedule Template Guide
- Excel Employee Hourly Schedule Template: How to Build One
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Thermometer Charts: Built-In or DIY?Excel does not have a dedicated Thermometer Chart type in Microsoft 365 for Windows. You can make the…
- How to Edit the Chart Area in ExcelTo edit a chart area in Excel, select the chart, open the contextual Format tab, choose Chart Area…
- Gantt Chart Template: Word or Excel for Your Project?Choose an Excel Gantt chart template if your schedule will change, needs date-based bars, or must track progress;…
- Excel Gantt Chart Setup: Build a Project TimelineTo set up a Gantt chart in Excel, list each task with a start date and finish date,…
- Google Sheets Gantt Chart Formatting GuideTo format a Gantt chart in Google Sheets, arrange tasks in rows, put each task’s start and end…
- Excel Pivot Table: Filter Dates by YearTo filter an Excel PivotTable by year, group its date field: right-click any date in the PivotTable, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.