PTS Blog

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.

	A	B	C	D
1	Main Category	Category	Sub Category	Defects
2	Mechanical	Mechanical	Gear	11
3	Mechanical	Mechanical	Bearing	8
4	Mechanical	Mechanical	Motor	3
5	Electrical	Electrical	Switch	19
6	Electrical	Electrical	Plug	12
7	Electrical	Electrical	Cord	11
8	Electrical	Electrical	Fuse	3
9	Electrical	Electrical	Bulb	2
10	Hydraulic	Hydraulic	Pump	4
11	Hydraulic	Hydraulic	Leak	3
12	Hydraulic	Hydraulic	Seals	1

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 = nSeries To cht.SeriesCollection.Count + 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:

	A	B	C	D
1	Main Category	Category	Sub Category	Defects
2	Mechanical	Mechanical	Gear	11
3	Mechanical	Mechanical	Bearing	8
4	Mechanical	Mechanical	Motor	3
5	Electrical	Electrical	Switch	19
6	Electrical	Electrical	Plug	12
7	Electrical	Electrical	Cord	11
8	Electrical	Electrical	Fuse	3
9	Electrical	Electrical	Bulb	2
10	Hydraulic	Hydraulic	Pump	4
11	Hydraulic	Hydraulic	Leak	3
12	Hydraulic	Hydraulic	Seals	1
13	Environmental	Environmental	Emissions	5
14	Environmental	Environmental	Noise	6
15	Environmental	Environmental	Thermal	2

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