Auto Refresh Pivot Charts in Excel: Setup and Limits

In Excel for Microsoft 365 on Windows, a PivotChart refreshes when its associated PivotTable refreshes; the chart does not have a separate refresh control. Excel can refresh PivotTables when a workbook opens, but it has no built-in setting to refresh one immediately after every edit to its source data. For that behavior, use a VBA event macro or refresh manually.

How PivotChart Refreshing Works

A PivotChart is linked to a PivotTable, which summarizes records from a source range, Excel Table, data connection, or Data Model. The PivotTable stores and displays a summarized view of that source. When you refresh the PivotTable, Excel updates the summary and the PivotChart reflects the resulting categories, values, and filters.

This is why changing a number in the source worksheet does not necessarily change the chart right away. The cell may show the new value while the PivotTable is still showing its last refreshed summary. Refreshing updates the PivotTable’s view of the source; it does not mean that Excel has been continuously recalculating the chart from every source edit.

Refresh also does not always mean “find every possible new record.” The PivotTable must be connected to a source that includes the records you want. If its source is a fixed range that ends at row 500, adding a record in row 501 will not make that row part of the source automatically. Source design is therefore as important as the refresh command.

Refresh a PivotChart Manually

To update one PivotChart, refresh its associated PivotTable. Click a cell in that PivotTable, then select PivotTable Analyze > Refresh. You can also right-click a cell in the PivotTable and choose Refresh. The PivotChart updates with the refreshed summary; you do not need to select the chart itself.

To refresh multiple PivotTables and connected data in the workbook, select Data > Refresh All. This is useful when a report contains several charts, queries, or external connections. A refresh can take time if a source is large or remote, so wait for the refresh to finish before treating the dashboard as current. If only one PivotTable needs updating, refreshing that table can avoid waiting for unrelated sources.

On Excel for Mac, PivotTable refresh is also available, though ribbon layout and dialog placement can differ from Windows. Excel for the web has more limited PivotTable and connection capabilities than the desktop app, and what can be refreshed depends on the workbook’s source. If a refresh command or connection option is unavailable in the web version, open the file in desktop Excel.

Set a PivotTable to Refresh When the Workbook Opens

For a report that should update when someone opens it, turn on the PivotTable’s refresh-on-open option. In Windows desktop Excel, click inside the PivotTable and choose PivotTable Analyze > Options to open PivotTable Options. Select the Data tab, select Refresh data when opening the file, and choose OK. Depending on the ribbon layout, you can also reach PivotTable Options by right-clicking inside the table.

This setting is useful when a workbook is opened regularly and its source is available at that time. It is not continuous refresh: changes made while the workbook remains open will not automatically update the PivotTable just because this box is selected. The refresh also depends on access to the source. A disconnected network location, credentials prompt, or unavailable external system can prevent the data from updating as expected.

Workbooks can contain multiple PivotTables. Check the option for each table that needs the behavior; do not assume that setting it for one table changes every PivotTable in the file. When several PivotTables share a cache, a refresh may also update other tables using that cache, but the refresh-on-open option should still be checked on the relevant tables rather than assumed.

Make the Source Expand with New Rows

For data entered directly in a worksheet, convert the source range into an Excel Table before building or updating the PivotTable. Select a cell in the data and choose Insert > Table, confirm the range and whether it has headers, and select OK. Give the table a useful name under Table Design. When new records are added directly below the table, the table expands, so those rows can be included when its PivotTable is refreshed.

An Excel Table does not itself refresh a PivotTable after every edit. It solves a different problem: it provides a source that can expand as records are appended, instead of leaving the PivotTable tied to a range with an old last row. You still need a manual refresh, refresh on open, or an automation method to update the summary.

Before refreshing, check that new data follows the same structure as existing records. Use a single header row with unique, nonblank field names, and keep each column’s values consistent in type where possible. For example, a date column mixing real Excel dates with text that merely looks like dates can lead to confusing grouping or filtering. A clean source makes the chart’s refreshed result more predictable.

Automate Refresh After Source Edits with VBA

If a PivotChart must update as soon as a person edits its source worksheet, a worksheet-change event macro can refresh the associated PivotTable. The example below assumes the source data is on the worksheet containing the code, changes in columns A through F should trigger a refresh, and the destination sheet and PivotTable are named Summary and SalesPivot. Replace those names and columns to match the workbook.

Save a copy of the workbook first. Open the Visual Basic Editor, locate the worksheet object that holds the source data, and put the code in that worksheet’s code module, not in a standard module.
Save the file as an Excel Macro-Enabled Workbook (.xlsm). Macros must be enabled for the event to run.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchedRange As Range
    Dim pivotTable As PivotTable

    On Error GoTo HandleError
    Set watchedRange = Me.Range("A:F")
    If Intersect(Target, watchedRange) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    Set pivotTable = ThisWorkbook.Worksheets("Summary").PivotTables("SalesPivot")
    pivotTable.RefreshTable

CleanExit:
    Application.EnableEvents = True
    Exit Sub

HandleError:
    MsgBox "Pivot refresh failed: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

The macro runs after a user changes or pastes values in columns A through F on the worksheet containing the code. Because it refreshes after each qualifying edit, it can feel slow when people enter records one cell at a time or when the PivotTable is large. Consider limiting the watched range or using a button to refresh on demand if immediate updates are not essential. The event applies to worksheet edits; a formula recalculating because another cell changed does not normally trigger Worksheet_Change.

VBA also introduces trade-offs: recipients may have macros disabled, and refreshing on every edit can interrupt data entry. Save a backup before running or changing a macro; macro actions cannot be undone with Ctrl+Z. On Mac, the Visual Basic Editor and ribbon layout differ, and macro availability depends on the Excel environment and security settings.

Common Refresh Problems

New records are missing: Check the PivotTable source range. If it is a fixed range, extend it or use an Excel Table as the source, then refresh.
The chart still looks unchanged: Inspect the PivotTable as well as the chart. The refreshed summary may genuinely be the same, or a report filter, slicer, or date filter may be excluding the new records.
Refresh All does not finish: A connection may need a network link, credentials, or permission. Check the connection’s status and any prompts, and distinguish a failed source refresh from a chart display issue.
New categories do not appear: Confirm the category field has a valid header and that the records fall within the PivotTable’s source. Also check whether a filter is hiding the category.
The chart changes shape after refresh: New categories or items can alter the plotted series or axis labels. Review the PivotTable layout and chart fields; refresh updates the summary, but it does not guarantee the visual will retain the same appearance.

One common misconception is that refreshing a chart should recalculate formulas in the source sheet. PivotTable refresh and workbook formula calculation are separate operations. If source values depend on formulas, confirm that Excel has recalculated them and that the PivotTable is reading the intended cells or connection before diagnosing the chart.

Choose the Right Update Method

The best method depends on when the chart needs to be current and how the source changes. Use a manual refresh for occasional updates and refresh on open for a workbook that should be current when a viewer starts a session. Use VBA only when immediate response to worksheet edits is genuinely useful and the workbook can rely on macros.

For a shared reporting process, a deliberate refresh step can be more dependable than refreshing on every edit. For example, a team could paste a batch of sales records into an Excel Table and then select Data > Refresh All once the import is complete. This avoids repeated recalculation during data entry while keeping the PivotChart synchronized at the point the report is prepared.

If the chart does not need PivotTable features such as field buttons, interactive filtering, or grouped summaries, a regular chart based on formulas or a helper range may be an alternative. That approach has its own maintenance needs, including formulas or ranges that accommodate new records. It is not automatically more current; choose it because its data structure and update workflow suit the report, not because every ordinary chart refreshes itself.

Frequently Asked Questions

Can a PivotChart refresh automatically when I change a cell?
Not through a built-in PivotChart setting. A PivotChart follows its PivotTable, and Excel does not natively refresh that table after every source edit. Use a worksheet-change VBA macro for that behavior, or refresh manually.

Does Refresh All update PivotCharts?
Yes. It refreshes PivotTables and eligible workbook connections, and each PivotChart reflects the updated PivotTable. It may take longer than refreshing one table because it can update other workbook data too.

Will refresh on open include rows I added?
Only if the PivotTable’s source includes those rows. An Excel Table is a practical source for appended records; a fixed source range may need to be extended before refresh on open can include them.

Can I refresh a PivotChart without refreshing the PivotTable?
No. The chart is tied to the PivotTable’s summarized data. Refresh the associated PivotTable; the chart then displays the updated result.

Why does my PivotChart not change after refresh?
Check whether the source range includes the edited records, whether the PivotTable itself changed, and whether filters or slicers exclude the values. If the source uses formulas or an external connection, confirm those values or the connection updated too.

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.