Refreshing Pivot Table Data: How to Update

Charts & pivot tables · Updated November 22, 2026

To refresh a pivot table in Excel, right-click anywhere inside the pivot table and choose Refresh, or select the pivot table and press Alt+F5 (Windows) or Command+Option+F5 (Mac). If your source data has new rows or columns, you also need to change the pivot table’s source range—refreshing alone won’t pick up new data outside the original range.

Why Pivot Tables Don’t Update Automatically

Pivot tables are snapshots of your source data at the moment you create or last refresh them. They don’t recalculate like regular formulas. When you change a value in the source range, the pivot table still shows the old number until you explicitly refresh it. This design keeps pivot tables fast when working with large datasets, but it means you must remember to refresh after editing your data.

Excel does have an option to refresh pivot tables when the workbook opens (File > Options > Data > Refresh data when opening file), but that only runs at startup, not every time you type a new value. For real-time updates, you’d need to use Power Query or a dynamic array formula, not a classic pivot table.

How to Refresh a Single Pivot Table

  1. Click anywhere inside the pivot table to activate the PivotTable Tools on the ribbon.
  2. Go to PivotTable Analyze (or Options in older Excel versions).
  3. Click the Refresh button in the Data group.

Alternatively, right-click the pivot table and select Refresh. The keyboard shortcut Alt+F5 works on Windows; on Mac it’s Command+Option+F5. If you have multiple pivot tables based on the same data, use Alt+Ctrl+F5 (Windows) or Command+Option+Shift+F5 (Mac) to refresh all pivot tables in the workbook at once.

Refreshing All Pivot Tables in a Workbook

When you have several pivot tables, refreshing each one manually is tedious. To refresh all at once:

  1. Go to Data tab on the ribbon.
  2. Click the Refresh All button (or press Ctrl+Alt+F5 on Windows, Command+Option+Shift+F5 on Mac).
  3. Excel refreshes every pivot table and any connected data queries in the workbook.

This is especially useful when your pivot tables pull from the same external data source, like a SQL database or a text file.

Changing the Source Data Range

Refreshing only updates values within the existing source range. If you added rows or columns to your original data, the pivot table won’t see them until you expand the range. Here’s how:

  1. Click anywhere in the pivot table.
  2. Go to PivotTable Analyze > Change Data Source.
  3. In the dialog, update the range to include the new rows/columns (e.g., change A1:D100 to A1:D150).
  4. Click OK, then refresh the pivot table.

A better long-term solution is to convert your source data into an Excel Table (Ctrl+T). When you add rows to a table, the pivot table’s source automatically expands on the next refresh—no manual range updates needed.

Refreshing Pivot Tables with External Data

If your pivot table uses data from an external source (like a database, a web query, or another workbook), refreshing works differently. You’ll see a Refresh option that re-runs the query to pull the latest data. For Power Pivot or Power Query connections, go to Data > Queries & Connections to manage the refresh. You can also set a refresh schedule for Power Query (Data > Queries & Connections > right-click query > Properties > Refresh every X minutes).

Note that refreshing an external data source may take longer than refreshing a local range, and it might require credentials if the source is password-protected.

Common Problems and Misconceptions

“I refreshed but my new column isn’t showing.” That’s because the source range didn’t include the new column. Update the source range or use a table.

“My calculated fields aren’t updating.” Calculated fields and calculated items update when you refresh, but if you changed the underlying data in a way that affects the calculation, refresh should fix it. If not, check that the formula refers to the correct fields.

“Refresh is greyed out.” This happens when the pivot table is based on data that’s not currently connected (e.g., a named range that was deleted). Re-establish the source or recreate the pivot table.

“Why does my pivot table show old data after I save and reopen?” If you didn’t refresh before saving, the saved file contains the old snapshot. Enable the “Refresh data when opening file” option to avoid this.

Comparing Refresh vs. Recreate

Refreshing is fast and preserves your layout, filters, and formatting. Recreating a pivot table from scratch is slower but can fix issues like corrupted cache or a completely changed data structure. Use refresh for routine updates; recreate only when the source layout changes dramatically (e.g., you moved columns around) or when the pivot table behaves unexpectedly.

For example, if you add a new column to your source data and want to use it as a filter, you must update the source range and then refresh. If you simply recreate the pivot table, you’ll have to reapply all your customizations.

Frequently Asked Questions

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, or go to the Data tab and click Refresh All. The keyboard shortcuts differ: there’s no Alt+F5 in the browser; you must use the menu.

Why does my pivot table not pick up new rows even after refreshing?

Because the pivot table’s source range is fixed. If you added rows beyond the original range, you must update the source range (PivotTable Analyze > Change Data Source) or convert the data to an Excel Table so the range expands automatically.

What is the difference between Refresh and Refresh All?

Refresh updates only the selected pivot table. Refresh All updates every pivot table and data connection in the workbook. Use Refresh All when you have multiple pivot tables that share the same source.

Can I make a pivot table refresh automatically when I change data?

No, pivot tables don’t auto-refresh on cell changes. You can set the workbook to refresh on open, or use VBA to refresh on a timer (but that requires a macro). For real-time updates, consider using Power Query with a refresh schedule or a dynamic array formula like SUMIFS instead of a pivot table.

Does refreshing a pivot table affect my formatting?

Refreshing preserves most formatting like number formats, column widths, and styles. However, if you have conditional formatting that applies to specific cells, it may need to be re-applied after a refresh if the layout changes. Also, if you’ve manually resized rows, those sizes may reset.

How do I refresh a pivot table in Google Sheets?

In Google Sheets, pivot tables update automatically when the source data changes—there’s no manual refresh button. If you have a pivot table created from a range, it recalculates on edit. For external data, you may need to reload the data connection.


Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.