In Using Pivot Table Data for a Chart with a Dual Category Axis, I showed how to make a Chart with a Dual Category Axis using a pivot table to properly arrange the source data. I generally prefer using a regular chart, because pivot charts are pretty inflexible when it comes to formatting. Unfortunately, a regular chart doesn’t know when the pivot table has been resized by a refresh. In Dynamic Chart using Pivot Table and Range Names I showed how to use dynamic ranges to allow a regular chart to update when the pivot table changes. However, that technique only accommodates a change in the number of points in a series, not the number of series in the chart. To allow for changing height and width of a pivot table, you either need to revert to a pivot chart, or use a VBA procedure, which I describe below.
This example begins with the same data range, pivot table, and chart as the previous ones.
I’ve highlighted some sections of the pivot table above to correlate them with pivot table ranges in the VBA object model.
The VBA procedure is not too complicated. It should be run whenever the pivot table has been refreshed. It can be called from a button, or from a Worksheet_Calculate event procedure. Whatever works best in a particular application. Paste this code into a regular code module:
Sub UpdateChartFromPivot() Dim rCategories As Range Dim rValues As Range Dim rSeriesNames As Range Dim pt As PivotTable Dim cht As Chart Dim iSeries As Long Dim nSeries As Long ' Define the Pivot Table 'Set pt = ActiveSheet.PivotTables(1) Set pt = ActiveSheet.PivotTables("PT_ChartSource") ' Define the Ranges Set rValues = pt.DataBodyRange With pt.RowRange Set rCategories = .Offset(1).Resize(.Rows.Count - 1) End With Set rSeriesNames = pt.ColumnRange.Rows(2) ' Define the Chart 'Set cht = ActiveSheet.ChartObjects(1).Chart Set cht = ActiveSheet.ChartObjects("chtPivotData").Chart ' How Many Series? nSeries = rSeriesNames.Columns.Count ' Remove or Add Series until Chart Has Correct Number Select Case cht.SeriesCollection.Count - nSeries Case Is > 0 ' too many: remove excess series For iSeries = cht.SeriesCollection.Count To nSeries + 1 Step -1 cht.SeriesCollection(iSeries).Delete Next Case Is < 0 ' too few: add sufficient series For iSeries = cht.SeriesCollection.Count + 1 To nSeries cht.SeriesCollection.NewSeries Next Case Else ' just right End Select ' Populate Each Series For iSeries = 1 To nSeries With cht.SeriesCollection(iSeries) .Name = rSeriesNames.Columns(iSeries) .Values = rValues.Columns(iSeries) .XValues = rCategories .Border.LineStyle = xlNone End With Next End Sub
Let’s add some rows to the pivot table source data:
Now update the pivot table:
And finally, run the VBA procedure to update the chart:
The VBA procedure can be run from the Macros dialog (press Alt+F8 or navigate the menu/ribbon), from a button you’ve placed on the sheet (see Assign a Macro to a Button or Shape and Assign a Macro to an ActiveX Control), from a menu item (see Assign a Macro to a Toolbar or Menu), or from a worksheet event procedure.
In Excel 2003 (and maybe 2002) you can use the Worksheet_PivotTableUpdate event procedure. Select the sheet with the pivot table and chart, right click on the sheet tab, and choose View Code. This opens the code module for the active worksheet. From the left dropdown in the module window, choose Worksheet, and from the right dropdown, choose PivotTableUpdate. The shell for the event procedure is entered into the module for you. Insert the procedure name as shown below, and you’re ready to go:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) UpdateChartFromPivot End Sub
If you’re using an older version of Excel, there is no PivotTableUpdate event. What I do in this case is place a formula in a cell outside the pivot table (say, M1) with a formula like:
where the range A1:J20 includes the pivot table. This formula ensures that a calculation occurs when the pivot table refreshes. Then I use the Worksheet_Calculate event procedure to kick off the chart update procedure. As above, select the sheet with the pivot table and chart, right click on the sheet tab, and choose View Code. This opens the code module for the active worksheet. From the left dropdown in the module window, choose Worksheet, and from the right dropdown, choose Calculate. The shell for the event procedure is entered into the module for you. Insert the procedure name as shown below, and you’re ready to go:
Private Sub Worksheet_Calculate() UpdateChartFromPivot End Sub
Fourth in a series
- Chart with a Dual Category Axis
- Using Pivot Table Data for a Chart with a Dual Category Axis
- Dynamic Chart using Pivot Table and Range Names
- Dynamic Chart using Pivot Table and VBA
A follow-up post shows how to Create and Update a Chart Using Only Part of a Pivot Table’s Data.