Excel VBA Pivot Charts: How to Create and Refresh Them

Yes—VBA can refresh, filter, and manage Excel PivotCharts, and you can create one by selecting a PivotTable and choosing PivotTable Analyze > PivotChart. A PivotChart is linked to a PivotTable, so VBA generally automates the underlying PivotTable and its data refresh rather than treating the chart like an ordinary chart.

What an Excel PivotChart Is

A PivotChart is a chart connected to a PivotTable. Its plotted categories and values come from the PivotTable’s fields and summarized data. For example, a sales PivotTable could put Region in Rows, Month in Columns, and Revenue in Values; its PivotChart could show revenue by month with a separate series for each region. Changing the PivotTable’s field arrangement changes the chart’s view too.

This relationship distinguishes a PivotChart from a regular chart. A regular chart usually refers to a fixed worksheet range, such as A1:B12. A PivotChart uses the PivotTable’s structure and interactive field controls. It is useful when people need to explore grouped or filtered summaries, not just view a static picture of the data.

In Excel’s object model, a PivotChart is still represented by a Chart object, but its PivotLayout is associated with a PivotTable. That distinction matters in VBA: chart formatting methods may apply, while the chart’s categories and series are governed by the PivotTable. A chart built from a copied range of PivotTable cells is not necessarily a PivotChart; it may be an ordinary chart with a fixed range.

Create a PivotChart Before Automating It

For Microsoft Excel for Microsoft 365 on Windows, start with a PivotTable built from a clean source range or an Excel table. Click inside the PivotTable, then select PivotTable Analyze > PivotChart. Choose a chart type and select OK. Excel creates a PivotChart linked to the selected PivotTable. You can also start from Insert > PivotChart and follow the prompts to choose the data and report layout.

Keep the data source well structured: one header row, one record per row, and consistent column types. Blank headers, merged cells, and subtotal rows inside the source data can make PivotTable creation or later refreshes confusing. If new records will be added regularly, formatting the source as an Excel table helps the PivotTable’s source expand when the table grows, although you may still need to refresh the PivotTable.

On Mac, the ribbon labels and placement can vary by Excel version, but use the PivotTable’s contextual Analyze tab and its PivotChart command where available. Excel for the web can display many workbook charts and PivotTables, but it cannot run VBA macros. Use desktop Excel to create or run VBA automation.

Refresh PivotTables and Their Charts with VBA

The most common automation task is refreshing a PivotTable after its source data changes. A PivotChart linked to that PivotTable follows the updated layout and values when the PivotTable refreshes; it usually does not require a separate chart-refresh command. The following procedure refreshes every PivotTable in the workbook:

Sub RefreshAllPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Refresh each PivotTable; linked PivotCharts follow their tables.
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

To add this code in Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the procedure, and run it with the cursor inside the procedure or from Excel’s Macro dialog. On Mac, use Option+F11 (some keyboards require Fn as well) to open the editor; the exact shortcut can depend on the keyboard and macOS settings. You can also open the editor from the Developer tab. Enable the Developer tab through File > Options > Customize Ribbon on Windows; on Mac, use Excel > Settings or Preferences > Ribbon & Toolbar.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, and a refresh may change displayed report results or take time. If a workbook contains many PivotTables that share a cache, refreshing each one may repeat work. For a simple workbook-wide refresh, ThisWorkbook.RefreshAll is another option; it requests refreshes for workbook connections and related data objects as well as PivotTables, so it can do more than the loop above. Some external or background queries can finish after the VBA procedure returns.

Control a PivotChart Through Its PivotTable

For most interactive changes, target the PivotTable rather than trying to assign chart series ranges. In Excel’s VBA object model, the chart’s PivotLayout exposes its associated PivotTable, while the PivotTable exposes its fields, filters, and refresh methods. This association is why an ordinary chart method such as changing a series formula is not usually the right way to change which PivotChart categories appear.

For example, a report could refresh its PivotTable and then use a PivotField’s orientation or filter settings to arrange the report. The exact code depends on the field names, whether a field is in the row, column, page, or data area, and whether the field is a regular or OLAP-based field. Test field changes on a copy: moving a field can alter the report layout and therefore the chart’s series, axes, and legend.

When automating an existing chart, first confirm it really is a PivotChart and identify the PivotTable it belongs to. A workbook can contain both chart types, and code that assumes every ChartObject has a PivotLayout can fail on a normal chart. Also, names such as a worksheet name, PivotTable name, and chart object name are separate identifiers; check each one in the workbook rather than assuming they match.

Refresh Timing and Data Sources

A refresh can only show data that the PivotTable’s source can access. If new records are outside a fixed source range, refresh may complete successfully while leaving those records out. An Excel table as the source reduces that risk for appended rows. If the source is an external connection or the Data Model, credentials, network availability, connection settings, or query completion can affect the result.

When a macro refreshes data, consider what users should see if refresh fails. A procedure that silently continues can leave a report showing old values. For production workbooks, add error handling and a clear status message appropriate to the organization’s process; do not suppress every error without recording it. If a connection refreshes asynchronously, allow for completion before code reads the updated PivotTable values or saves a final report.

Refresh behavior can also depend on PivotTable cache settings and whether multiple PivotTables share a cache. A cache stores PivotTable source data and can be reused by compatible reports. Shared caches may reduce duplicate storage, but they also mean reports can be connected in ways that are not obvious from the charts alone. Before changing a source or cache, check whether other PivotTables depend on it.

Common Problems and Practical Fixes

The chart does not include new rows: Check whether the PivotTable source includes them. Convert the source to an Excel table or update the source range, then refresh.
The macro says a method failed: Verify the sheet, PivotTable, and field names; check that the code is targeting a PivotTable rather than a regular chart or range.
The chart looks different after refresh: PivotTable expansion, changed field selections, or refreshed categories can alter the plotted layout. Review field placement and chart formatting after representative refreshes.
A refresh seems to do nothing: Confirm that the source data itself changed and that the PivotTable is connected to the expected range, table, or query. A successful refresh does not repair an incorrect source definition.
The macro cannot run: The workbook may be saved as .xlsx, which does not preserve VBA. Save a macro-enabled workbook as .xlsm, and check that macros are permitted by the organization’s security policy.
Some chart controls or formatting are unavailable: PivotCharts have PivotTable-specific behavior and are not identical to regular charts. If a required chart design depends on arbitrary, independently selected cell ranges, use a regular chart based on a helper range instead.

PivotChart or Regular Chart?

Choose a PivotChart when the chart should respond to PivotTable field buttons, report filters, slicers, or changes in the PivotTable layout. It is a good fit for an interactive summary such as sales by product, month, and region. It keeps the chart tied to the same summarized view that users see in the PivotTable.

Choose a regular chart when you need precise control over a fixed set of cells, custom calculations outside the PivotTable, or a chart design that depends on combining unrelated ranges. A common approach is to use formulas or a separate summary range to prepare chart-ready values, then create a regular chart from that range. This adds maintenance work but separates the chart from PivotTable layout rules.

Neither option is universally better. Consider how often the data changes, whether viewers need to filter interactively, whether the chart must show unsummarized or specially calculated values, and how much formatting stability matters. If the chart must be both highly customized and interactive, test a small example before building a large VBA process around it.

Macro Security and Workbook Compatibility

VBA macros can modify workbook contents and interact with data sources, so only run code from sources you trust. Do not lower macro security globally just to make one workbook run. Follow your organization’s approved process, such as using a trusted location or digitally signed macros where required. A workbook containing VBA should be saved in a macro-enabled format such as .xlsm; saving as .xlsx removes the VBA project.

VBA is supported in desktop Excel for Windows and Mac, but macro execution is not supported in Excel for the web. Users opening a macro-enabled workbook in a browser may be able to work with its data, but they need desktop Excel to run its macros. Differences in connections, add-ins, and platform-specific code can also affect cross-platform workbooks, so test on the Excel versions your readers actually use.

Frequently Asked Questions

Can VBA create a PivotChart from scratch?
Yes, VBA can automate PivotTable and chart creation, but creating a genuine PivotChart requires establishing the PivotTable relationship, not merely drawing a regular chart over cells. The exact code depends on the workbook’s source and desired layout. For a straightforward report, create the PivotTable and PivotChart in Excel first, then automate refreshes and report changes.

Does a PivotChart refresh automatically when its PivotTable refreshes?
Yes. A PivotChart reflects the PivotTable it is linked to, so refreshing that table updates the chart’s represented data and layout. If the chart remains unchanged, check the PivotTable’s source, refresh status, and field arrangement.

Can I use VBA to filter a PivotChart?
Yes. The chart’s view is controlled through its associated PivotTable, including field filters and report filters. The correct VBA depends on the field type and the filter you need; confirm the field name and test the change on a copy.

Why does my PivotChart not show added data?
The source range may not include the new rows, or the PivotTable may not have been refreshed. Use an Excel table as the source where practical, confirm the PivotTable’s data source, and then refresh it.

Can Excel for the web run a PivotChart VBA macro?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; the browser version may still display the workbook’s chart and data.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

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

Spotted an error in this guide? Let us know.