To refresh a pivot table’s source data in Excel, right-click the pivot table and select Refresh, or press Alt+F5 (Windows) or Ctrl+Option+R (Mac). This updates the pivot table with any changes made to the existing source range. If you added or removed rows or columns from the source, you must also update the pivot table’s source range via PivotTable Analyze > Change Data Source.
Why Pivot Tables Don’t Auto-Update
Pivot tables are not live connections to your data—they are snapshots. When you create a pivot table, Excel stores a cache of the source data. Any changes you make to the underlying cells (editing values, adding rows, deleting columns) are not reflected until you refresh. This design improves performance because pivot tables can handle large datasets without recalculating every time you type. However, it also means that if you forget to refresh, your pivot table shows stale numbers, which can lead to incorrect decisions.
How to Refresh a Pivot Table (Step-by-Step)
There are several ways to refresh, depending on how many pivot tables you have and your preference for speed.
- Refresh a single pivot table: Right-click anywhere inside the pivot table and choose Refresh. Alternatively, select the pivot table and go to PivotTable Analyze > Refresh (on the Ribbon).
- Refresh all pivot tables in the workbook: Press Ctrl+Alt+F5 (Windows) or Cmd+Option+F5 (Mac). This refreshes every pivot table and data connection in the file.
- Use the keyboard shortcut: Alt+F5 (Windows) refreshes the selected pivot table; on Mac, it’s Ctrl+Option+R.
- Refresh on file open: Go to File > Options > Data (or Excel > Preferences > Data & General on Mac) and check Refresh data when opening the file. This ensures your pivot tables are up to date every time you open the workbook.
When You Must Change the Source Range
Refreshing only updates the data within the existing source range. If you added rows below the original range or new columns to the right, those are not included unless you adjust the source. Here’s how:
- Click anywhere in the pivot table to activate the PivotTable Analyze tab.
- Click Change Data Source (in the Data group).
- In the dialog, select the new range (e.g.,
=Sheet1!$A$1:$D$500) or use the range selector. - Click OK. The pivot table refreshes automatically with the new range.
If your data grows regularly, consider converting your source data to an Excel Table (Ctrl+T) and then creating the pivot table from that table. When you add rows to the table, the pivot table’s source automatically expands on refresh—no manual range changes needed.
Common Problems and Misconceptions
Problem: “I refreshed but my new rows aren’t showing.” This almost always means the source range wasn’t updated. Check Change Data Source to see if the range includes the new rows.
Problem: “My pivot table shows #REF! errors.” This happens when the source range points to a sheet or range that was deleted. Go to Change Data Source and re-select a valid range.
Misconception: “Refreshing will overwrite my formatting.” Refreshing preserves most formatting, but if you have custom conditional formatting or number formats, they may not apply to new fields you add later. That’s a separate issue from refreshing.
Misconception: “I need to refresh every time I filter.” Filtering and refreshing are independent. Filtering only changes what’s displayed, not the underlying data. You only refresh when the source data changes.
Refreshing vs. Rebuilding a Pivot Table
Refreshing is faster and keeps all your layout, filters, and formatting. Rebuilding (creating a new pivot table from scratch) resets everything and is rarely needed unless you want to change the data model or the source fundamentally. For example, if you switch from a range to an external data connection, you might rebuild. But for routine updates, always refresh first.
Using Pivot Tables with External Data Sources
If your pivot table uses data from an external source (e.g., SQL Server, Access, or a text file), refreshing may take longer and might require credentials. You can set up automatic refresh intervals via Data > Queries & Connections (for Power Query) or by right-clicking the pivot table and choosing Connection Properties to set a refresh timer. Be aware that external data refreshes can fail if the source is unavailable or the connection is broken.
Alternatives to Manual Refreshing
If you find yourself refreshing constantly, consider these options:
- Use Excel Tables as the source—they auto-expand, so you only need to refresh (not change the range).
- Use Power Query to import and transform data. You can set the query to refresh on file open, and pivot tables built on the query will update with it.
- Use a macro to refresh on a schedule or when a cell changes. For example, a simple VBA macro that runs when you activate a worksheet:
Private Sub Worksheet_Activate()
' Refresh all pivot tables when the sheet is activated
Dim pt As PivotTable
For Each pt In Me.PivotTables
pt.RefreshTable
Next pt
End Sub
Note: Save a copy of your workbook before adding any macro, as macros cannot be undone with Ctrl+Z.
Frequently Asked Questions
Why is my pivot table not picking up new rows? Because the source range is static. Update it via Change Data Source, or better, convert your data to an Excel Table so the range expands automatically.
What’s the difference between Refresh and Refresh All? Refresh updates only the selected pivot table. Refresh All (Ctrl+Alt+F5) updates all pivot tables, charts, and data connections in the workbook.
Does refreshing a pivot table affect my original data? No. Refreshing only recalculates the pivot table’s cache from the source. It never changes the source data itself.
Can I set a pivot table to refresh automatically every few minutes? Yes, for external data connections. Right-click the pivot table, choose Connection Properties, and under the Usage tab, check Refresh every and set minutes. This does not work for data that lives in the same worksheet.
Why does my pivot table show old values even after I press Ctrl+S? Saving a file does not refresh pivot tables. You must explicitly refresh. Use the shortcut or set the workbook to refresh on open.
How do I refresh a pivot table in Excel for the web? In Excel for the web, right-click the pivot table and choose Refresh. The option is available, but automatic refresh on open is not. You’ll need to refresh manually each time.
Related
- Excel HR Templates for Attendance: A Practical Guide
- Excel: How to Split Information in a Cell
- How to Rotate a Table in Excel: 3 Easy Ways
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Line Chart With Error Bars: How to Add ThemTo add error bars to a line chart in Excel for Microsoft 365 on Windows, select the chart,…
- Excel Pivot Table Calculated Field Percentage: How-ToTo show a percentage in an Excel pivot table using a calculated field, you add a calculated field…
- Fill Area in an Excel Chart: A Practical Step-by-StepTo fill an area in an Excel chart, select the data series, right-click it, choose Format Data Series,…
- Excel Pivot Table Filter After a Date: Steps and FixesTo show PivotTable records later than a chosen date, open the date field’s dropdown, choose Date Filters >…
- Excel Slicer Link to Different Pivot Tables: How It WorksYes. In Excel for Microsoft 365 on Windows, one slicer can filter multiple PivotTables when those PivotTables share…
- How to Create a 3-D Pie Chart in Excel Step by StepTo create a 3-D pie chart in Excel for Microsoft 365 on Windows, select a category column and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.