How to Refresh a Pivot Table in Excel

Charts & pivot tables · Updated November 22, 2026

To refresh a pivot table in Excel, right-click anywhere inside the pivot table and choose Refresh from the context menu. Alternatively, select any cell in the pivot table, go to the PivotTable Analyze tab (or Analyze on Mac), and click Refresh. If you have multiple pivot tables, use Refresh All on the Data tab to update every pivot table and query in the workbook at once.

Why Pivot Tables Don’t Update Automatically

Pivot tables are not live links to your data. When you create a pivot table, Excel takes a snapshot of the source data and stores it in the pivot cache—a hidden copy that the pivot table reads from. When you change values in the source cells, add new rows, or delete data, the pivot table still shows the old snapshot until you refresh it. This design keeps pivot tables fast, especially with large datasets, because Excel doesn’t recalculate everything on every keystroke. However, it also means you must manually trigger a refresh (or set the workbook to refresh on open) to see current results.

How to Refresh a Single Pivot Table

The quickest ways to refresh one pivot table:

  • Right-click any cell inside the pivot table and select Refresh.
  • Select a cell in the pivot table, then go to PivotTable Analyze > Refresh (the button is in the Data group).
  • Press Alt+F5 (Windows) or Option+Cmd+R (Mac) after selecting a cell in the pivot table.

If the pivot table is based on a data model (Power Pivot), refreshing may also recalculate measures and relationships. For a standard pivot table from a worksheet range or an Excel table, the refresh simply pulls the latest values.

How to Refresh All Pivot Tables at Once

When your workbook has several pivot tables, refreshing each one individually is tedious. Use Refresh All:

  1. Go to the Data tab on the ribbon.
  2. Click Refresh All (the button with two circular arrows).
  3. Excel refreshes every pivot table, query, and data connection in the workbook.

You can also use the keyboard shortcut Ctrl+Alt+F5 (Windows) or Cmd+Option+R (Mac) to refresh all. If you only want to refresh pivot tables and not external queries, you can use the dropdown arrow next to Refresh All and choose Refresh All PivotTables (this option appears in some versions).

Refreshing When You Add or Remove Rows

Refreshing updates values, but it does not automatically pick up new rows or columns that you add to the source data. If you add rows below the original range, you must first change the pivot table’s source range to include them. Here’s how:

  1. Select any cell in the pivot table.
  2. Go to PivotTable Analyze > Change Data Source.
  3. In the dialog, update the range or table name to include the new rows (e.g., change Sheet1!$A$1:$D$100 to Sheet1!$A$1:$D$150).
  4. Click OK, then refresh the pivot table.

A better long-term solution is to convert your source range into an Excel Table (select the data and press Ctrl+T). Then, when you add rows below the table, Excel automatically extends the table range, and refreshing the pivot table picks up the new data without manually changing the source.

Automating Refresh on File Open

To ensure your pivot tables are current every time you open the workbook, set the workbook to refresh automatically:

  1. Go to File > Options (Windows) or Excel > Preferences (Mac).
  2. Select Data (Windows) or Data & Files (Mac).
  3. Check Refresh data when opening the file.

This setting applies to all data connections and pivot tables in the workbook. Note that if your source data is in another workbook, you may also need to enable background refresh for that connection.

Common Problems and Fixes

Pivot Table Doesn’t Show New Data After Refresh

If you refreshed but still don’t see new rows, the source range likely doesn’t include them. Check the source range (as described above) and consider using an Excel Table. Also, if you filtered the pivot table, new items might be hidden by the filter—clear the filter or select Select All in the field dropdown.

Refresh Button Is Grayed Out

This happens when the pivot table is based on external data that is not connected, or when the workbook is in a compatibility mode. Ensure you have a valid data connection, or try Data > Queries & Connections to verify the connection status.

Refresh Changes Number Formatting

Refreshing can reset some formatting if the source data has inconsistent formats. To preserve formatting, right-click the pivot table, choose PivotTable Options, and on the Layout & Format tab, check Preserve cell formatting on update.

Refresh Is Slow on Large Data

If your pivot table is based on hundreds of thousands of rows, refresh can take time. Consider using a data model (Power Pivot) or converting to a dynamic array formula with PIVOTBY (available in Excel 365) for faster calculations.

Refreshing Pivot Tables in Excel for the Web and Mac

In Excel for the web, you can refresh a pivot table by selecting it and clicking the Refresh button on the PivotTable Analyze tab. The web version does not support the right-click refresh menu in all browsers, so use the ribbon button. On Mac, the ribbon tab is labeled Analyze (not PivotTable Analyze), and the keyboard shortcut is Option+Cmd+R. The Refresh All button is on the Data tab in both versions.

Frequently Asked Questions

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

Because the pivot table’s source range is fixed. If you added rows outside the original range, the pivot table doesn’t know they exist. Either update the source range manually or convert the data to an Excel Table (Ctrl+T) so the range expands automatically. After that, refresh and the new rows appear.

Can I refresh a pivot table with a keyboard shortcut?

Yes. Select any cell in the pivot table and press Alt+F5 on Windows or Option+Cmd+R on Mac to refresh just that pivot table. To refresh all pivot tables and connections, press Ctrl+Alt+F5 (Windows) or Cmd+Option+R (Mac).

Does refreshing a pivot table change my formulas or formatting?

Refreshing updates the values and may reorder rows or columns, but it does not change your formulas outside the pivot table. Formatting applied to the pivot table can be lost if you haven’t enabled Preserve cell formatting on update in PivotTable Options. To keep custom styles, reapply them after refreshing or set the option.

How do I refresh a pivot table that is connected to an external database?

If your pivot table uses an external data connection (e.g., SQL Server), the refresh process also re-queries the database. You can refresh manually as usual, or set the connection to refresh on open. Go to Data > Queries & Connections, right-click the connection, and choose Properties to set refresh options.

What is the difference between Refresh and Refresh All?

Refresh updates only the selected pivot table. Refresh All updates every pivot table, data connection, and query in the workbook. Use Refresh All when you have multiple pivot tables based on the same or different data sources to save time.

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.