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.
Color | Range |
Blue | DataBodyRange |
Green | ColumnRange |
Purple | RowRange |
Yellow | DataLabelRange |
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:
=SUM(A1:J20)
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
Update
A follow-up post shows how to Create and Update a Chart Using Only Part of a Pivot Table’s Data.