Auto Refresh Pivot Tables in Excel: Options That Work

In Excel for Microsoft 365 on Windows, a PivotTable does not normally update itself the moment its source data changes. Refresh it with PivotTable Analyze > Refresh, use Data > Refresh All for multiple connections, or enable Refresh data when opening the file in PivotTable Options. For refreshes immediately after edits, you need a suitable automation method, such as VBA.

What “auto refresh” means in Excel

People use “auto refresh” to mean several different things. It may mean updating a PivotTable when new rows are added, refreshing it whenever the workbook opens, or refreshing data imported from a database or query on a schedule. These are separate behaviors, and Excel handles them differently.

A PivotTable displays a summary built from a source range, an Excel Table, an external connection, or the workbook Data Model. It keeps a PivotCache—an internal representation of the source data—so it can calculate and display the summary efficiently. Editing source cells does not necessarily rebuild that cache. As a result, the PivotTable can continue showing its previous summary until you refresh it.

Refreshing is not the same as recalculating formulas. Pressing F9 recalculates formulas, but it does not reliably refresh a PivotTable’s source data. Likewise, changing a PivotTable’s layout, such as moving a field from Rows to Columns, changes the report design rather than updating the underlying source records.

Refresh a PivotTable manually

For a one-off update, click inside the PivotTable and select PivotTable Analyze > Refresh. You can also right-click a cell in the PivotTable and select Refresh. These commands update the selected PivotTable from its current source.

If the workbook has several PivotTables or external data connections, use Data > Refresh All to update them together. This is convenient for a report with multiple summaries, but it can take longer because it may also refresh queries and connections that are not directly related to the PivotTable you are viewing. If a refresh appears to be in progress, wait for connected data to finish loading before relying on the displayed totals.

On Excel for Mac, the ribbon layout and tab names can differ slightly, but the PivotTable’s contextual Analyze tab and the Data tab provide the equivalent refresh commands. Excel for the web has refresh options for supported PivotTables and connections, but it does not run VBA macros.

Refresh automatically when the workbook opens

For a report that should be current when someone opens it, enable the PivotTable’s opening refresh setting. In Windows Excel, right-click inside the PivotTable, choose PivotTable Options, open the Data tab, and select Refresh data when opening the file. Click OK and save the workbook.

This setting is useful for recurring reports whose source data changes between sessions. It does not refresh the PivotTable every time someone edits a source cell; it refreshes when the workbook is opened. A user may also need to allow external connections or sign in to a data source before the latest data can load. If the workbook is already open while the source changes, the opening option alone will not update the visible report.

For an external query or connection, also review its connection properties. Some connections offer their own refresh-on-open or periodic refresh settings, and those settings are distinct from the PivotTable option. Refreshing a PivotTable based on a query cannot show newer records until the query itself has retrieved them.

Make new source rows easier to include

A common cause of missing records is a fixed source range. For example, a PivotTable built from A1:D500 will not necessarily include a new record entered on row 501. Refreshing can update values within the source range, but it cannot include rows that the source definition excludes.

For data maintained in the workbook, convert the source list into an Excel Table before building the PivotTable. Select a cell in the list and choose Insert > Table, or press Ctrl+T on Windows. Confirm that the range includes the headers. When records are added directly below the table, the table usually expands, giving the PivotTable a source that can include the additional rows on refresh.

Using a Table solves the source-range problem, not the refresh problem. The PivotTable still needs to be refreshed after the table’s contents change. For a dependable routine, append records to the Table and then refresh the PivotTable, or use a refresh-on-open setting if updates only need to appear at the start of each session.

Refresh after edits with VBA

If you need a PivotTable to refresh after a user edits its source data, a worksheet change event can trigger a refresh. This approach is most appropriate for a small, local workbook where edits happen occasionally. In the example below, the source is on a worksheet named Data, the edited cells are in columns A through F, and the PivotTable is named PivotTable1 on a worksheet named Report.

In Windows Excel, press Alt+F11 to open the Visual Basic Editor, find the Data sheet under Microsoft Excel Objects, and paste the code into that sheet’s code window—not a standard module. On Mac, the Visual Basic Editor can be opened with Option+F11 (some keyboards require Fn as well); menu access may also be available through Tools > Macro > Visual Basic Editor.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchedRange As Range
    Dim pivotSheet As Worksheet

    On Error GoTo CleanUp
    Set watchedRange = Me.Range("A2:F10000")

    If Intersect(Target, watchedRange) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    Set pivotSheet = ThisWorkbook.Worksheets("Report")
    pivotSheet.PivotTables("PivotTable1").PivotCache.Refresh

CleanUp:
    Application.EnableEvents = True
End Sub

Change the sheet names, PivotTable name, and watched range to match the workbook. The code refreshes after a direct edit or paste in the watched cells. It does not detect a changed result caused solely by a formula recalculation, and it will not run if macros are disabled. Frequent edits can trigger repeated refreshes and slow a workbook; for a large dataset, a button or a manual refresh may be a better choice.

Before running a macro, save a copy of the workbook. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) to retain the VBA. Macro actions cannot be undone with Ctrl+Z, and users should enable macros only for workbooks they trust.

Queries, connections, and the Data Model

When a PivotTable uses Power Query, a database, or another external source, a PivotTable refresh and a source-data refresh may be different steps. A refresh may first retrieve records through the connection and then update the PivotTable cache. If the connection is slow, requires credentials, or is unavailable, the PivotTable may continue to show older data or display a refresh error.

Data > Refresh All is often the simplest way to update a report that combines queries and PivotTables. For a more controlled workflow, inspect Data > Queries & Connections and the connection’s properties. Refresh timing and background loading can affect when results are ready. A PivotTable using the Data Model may also have a different refresh path from one based on a worksheet range, so check the source and connection rather than assuming every report responds to the same setting.

A scheduled refresh is not the same as a live feed. Excel connection properties may provide a refresh interval for supported connections, but availability depends on the connection type and workbook environment. A schedule cannot guarantee that a database is reachable or that credentials remain valid.

Common problems and practical checks

New rows are missing: Check whether the PivotTable source includes those rows. Converting the source list to an Excel Table can make later additions easier to include.
Changes do not appear: Use Refresh or Refresh All, and confirm you refreshed the correct PivotTable or connection.
The refresh command is unavailable or fails: Check workbook permissions, connection status, credentials, and whether the file is opened in a mode that permits editing.
Old category names remain in a filter: A PivotTable may retain deleted items in its cache. In PivotTable Options, look for the Data setting for items to retain per field and choose None where appropriate; then refresh.
A macro does nothing: Confirm that macros are enabled, the event code is in the source worksheet’s code module, and the worksheet, range, and PivotTable names match exactly.
The displayed result seems unchanged: Check the source records and filters, and verify that the value field is summarized as intended. Refreshing cannot correct an incorrect source, filter, or aggregation choice.

Choose a refresh method that fits the report

The best choice depends on how current the report must be and how its source is maintained. For an occasional update, manual Refresh is simple and avoids unnecessary work. For a report that should be current at the start of each session, enable refresh on open and verify any connection settings. For users who add rows to a local source, combine an Excel Table with a deliberate refresh step. Use event-driven VBA only when immediate updating justifies the maintenance and macro requirements.

For example, a monthly sales workbook may receive new rows in a table throughout the week. A user can add the records and select Data > Refresh All before sending the report. If recipients only need current totals when opening the file, refresh on open may be sufficient. If the workbook contains thousands of rows or several database queries, refreshing after every keystroke is likely inefficient; a refresh button or scheduled connection refresh is usually more practical.

Frequently Asked Questions

Can an Excel PivotTable refresh automatically whenever its source changes?
Not through a standard PivotTable setting for ordinary worksheet edits. Use manual refresh, refresh on open, or VBA to respond to edits. A VBA event will not detect every possible change, such as a formula result changing without a direct edit.

Does refresh on open update the PivotTable while the workbook is open?
No. It refreshes when the workbook is opened. To update an already-open workbook, use Refresh or Refresh All, or set up an appropriate automation method.

Why does my PivotTable not include rows I just added?
The source may be a fixed range that ends before the new rows. Expand the source or use an Excel Table as the source, then refresh the PivotTable.

What is the difference between Refresh and Refresh All?
Refresh updates the selected PivotTable. Refresh All updates multiple workbook connections and related data, which may take longer but is useful when a report depends on several sources.

Will pressing F9 refresh a PivotTable?
F9 recalculates formulas; it is not the standard command for refreshing PivotTable data. Use the PivotTable’s Refresh command or Data > Refresh All instead.

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.