Update Regular Chart when Pivot Table Updates

A reader named Julie has a regular chart that is linked to a pivot table. When the pivot table is pivoted or updated, it may have different numbers of rows and columns, and the chart must be updated manually. Julie asked whether a procedure could be developed to update the chart automatically.

I’ve done this in a number of projects, so I thought I’d illustrate it with a simple example. Let’s start with this dummy data in a list (I’m using Excel 2003).

Pivot Table Source Data

Insert a pivot table using this data. Drag the Item field to the Rows area, the Category field to the Columns area, and the Value field to the Data area. Simple enough.

Pivot Table

Now create a regular chart from this pivot table, using series in columns, one series per item in the Category field, Category entries (alpha and beta) as series names, and the item names as category (X) axis labels.

Regular Chart from Pivot Data

In order to determine which ranges to use for the chart’s source data, it helps to understand what parts of a pivot table are designated as certain ranges. This color coded pivot table illustrates the RowRange, the ColumnRange, the DataBodyRange, and the DataLabelRange. Several of these will be used in the procedure below.

Definitions of Ranges in a Pivot Table

I have set up the macro to put series in columns unless the pivot table DataBodyRange has only one row, in which case there is only one series, and it is plotted by row.

Here is the VBA procedure. Implement it using How To: Use Someone Else’s Macro.

Sub UpdateChartFromPivotTable()
  Dim rValues As Range
  Dim rCats As Range
  Dim rTitles As Range
  Dim cht As Chart
  Dim pt As PivotTable
  Dim chttype As XlChartType
  Dim iSrsIx As Long
  Dim iSrsCt As Long
  Dim iCols As Long
  Dim iRows As Long

  Set cht = ActiveSheet.ChartObjects(1).Chart
  Set pt = ActiveSheet.PivotTables(1)

  Set rValues = pt.DataBodyRange
  On Error Resume Next
  iRows = pt.RowRange.Rows.Count
  iCols = pt.ColumnRange.Columns.Count
  On Error GoTo 0

  If iRows > 0 Then
    Set rCats = Intersect(rValues.EntireRow, pt.RowRange.EntireColumn)
    If iCols > 0 Then
      Set rTitles = Intersect(rValues.EntireColumn, pt.ColumnRange.EntireRow)
      Set rTitles = rTitles.Offset(1).Resize(rTitles.Rows.Count - 1)
    End If
    iSrsCt = rValues.Columns.Count
  ElseIf iCols > 0 Then
    Set rCats = Intersect(rValues.EntireColumn, pt.ColumnRange.EntireRow)
    Set rCats = rCats.Offset(1).Resize(rCats.Rows.Count - 1)
    iSrsCt = rValues.Rows.Count
  Else
    MsgBox "Pivot Table has no fields in Rows area or Columns area.  ", _
        vbCritical + vbOKOnly
    GoTo ExitSub
  End If

  If cht.SeriesCollection.Count > iSrsCt Then
    For iSrsIx = cht.SeriesCollection.Count To iSrsCt + 1 Step -1
      With cht.SeriesCollection(iSrsIx)
        chttype = .ChartType
        .ChartType = xlColumnClustered
        .Delete
      End With
    Next
  ElseIf cht.SeriesCollection.Count < iSrsCt Then
    For iSrsIx = cht.SeriesCollection.Count + 1 To iSrsCt
      cht.SeriesCollection.NewSeries
    Next
  End If

  For iSrsIx = 1 To cht.SeriesCollection.Count
    With cht.SeriesCollection(iSrsIx)
      chttype = .ChartType
      .ChartType = xlColumnClustered
      .XValues = rCats
      If iRows > 0 Then
        .Values = rValues.Columns(iSrsIx)
        If iCols > 0 Then
          .Name = rTitles.Columns(iSrsIx)
        Else
          .Name = "Data"
        End If
      Else
        .Values = rValues.Rows(iSrsIx)
        .Name = "Data"
      End If
      .ChartType = chttype
    End With
  Next

ExitSub:

End Sub

Whenever the pivot table changes you can run this procedure to update the chart. To make it run automatically when the pivot table updates, you could put this event procedure into the code module behind the sheet (to open the sheet’s code module, right click the sheet tab, click View Code).

Private Sub Worksheet_Calculate()
  Application.EnableEvents = False

  UpdateChartFromPivotTable

  Application.EnableEvents = True
End Sub

This procedure runs every time the worksheet calculates, whether or not the pivot table is involved. To redraw the chart only when mandated by pivot table updates, in Excel 2003 and later you can use this event procedure instead:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Application.EnableEvents = False

  If Target.Name = Me.PivotTables(1).Name Then
    UpdateChartFromPivotTable
  End If

  Application.EnableEvents = True
End Sub

You could further modify the code to update different charts which are based on different pivot tables. Modify the indexes in these lines, instead of using “1”.

  Set cht = ActiveSheet.ChartObjects(1).Chart
  Set pt = ActiveSheet.PivotTables(1)

I have run through some permutations of the pivot table and chart:

Pivot Table Regular Chart from Pivot Data

Pivot Table Regular Chart from Pivot Data

Pivot Table Regular Chart from Pivot Data

Pivot Table Regular Chart from Pivot Data

Pivot Table

Regular Chart from Pivot Data

Pivot Table

Regular Chart from Pivot Data

 

 

Peltier Tech Charts for Excel

Comments

  1. I have a series of graphs on different worksheets. Each graph is based on a different pivot table. I’m trying to make it so that each graph will automatically be updated if any changes are made to the specific pivot table to which it is tied. For example, the pivot table contains data for all 50 states over ten years. I’d like to see the graph update if someone selects one or multiple states in the pivot table without repeating that exercise by clicking on the graph itself.

    I tried using the first macro and then the one below so that only the graph on the same page as the pivot table gets updated, but what is happening is that ALL graphs are getting updated using the same single pivot table data. This is obviously not what I want to happen.

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Application.EnableEvents = False

    If Target.Name = Me.PivotTables(1).Name Then
    UpdateChartFromPivotTable
    End If

    Application.EnableEvents = True
    End Sub

    Unfortunately, I didn’t understand what you meant with this statement, but this would appear to fix the problem I’m having now: “You could further modify the code to update different charts which are based on different pivot tables. Modify the indexes in these lines, instead of using “1″.” To what do the indexes refer?

    Thanks for your help!

    Bryan

  2. Bryan –

    Right after the sentence you cited, there are two lines of code:

    Set cht = ActiveSheet.ChartObjects(1).Chart
    Set pt = ActiveSheet.PivotTables(1)

    the indexes are the “(1)” in these commands, one for index of the chart object, the other for the index of the pivot table. It might be more robust to name the chart and the pivot table.

  3. Thanks for answering so quickly! Unfortunately, I still have some questions due to my ignorance of macros and VBA:

    1. Those two lines of code are already included in the big macro at the top of this post, so does adding them again to the bottom of the macro fix things?

    2. I ran a compile on the following code and received an error message saying it was an invalid use of the Me keyword.

    3. I’m afraid I still don’t understand what indexes reference. What does “the index of the chart reference” and “the index of the pivot table” mean?

    4. Is my problem that the pivot tables are all based on the exact same source data?

    5. I hate to ask, but can you put the macro together as it should appear to only update the specific charts associated with a specific pivot table?

    Thanks again!

    Bryan

  4. Hello,
    I would like to know how to create multiple different charts from one pivot table.
    thanks
    Maria

  5. Thanks Jon, but my question was about plotting different charts( not including all the data from the pivot table) .what I am thinking is using your script ‘making regular charts from pivot tablse’ but instead of using DataBodyRange, select the rows I want to plot….Do you know how to do it?
    Thanks
    Cheers,
    Maria

  6. Maria –

    You asked how to make multiple charts from a pivot table. I assumed you meant multiple charts that did not include the entire pivot table. The article I cited shows a manual procedure (there is no script for this) to make one chart from pivot table without necessarily using all of the pivot table data. You manually select the range for each series in the chart. You would repeat this for each of the multiple charts you wanted.

    If you wanted to look at a known subset of the DataBodyRange, you could use

    pt.DataBodyRange.Rows(i)
    and
    pt.DataBodyRange.Columns(j)

    for the ith row and jth column.

    You may need to build more intelligence into the code, so you select for example the row corresponding to “item c” in case it might not be the 3rd row.

  7. Yes you are right but the problem is that I want to select several columns in order to plot them all together in the same graph. I have tried replacing this line:

    Set rValues = pt.DataBodyRange

    for these ones
    Set xValues = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_UL_POWER”).DataRange
    Set sValues = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_DL_POWER”).DataRange
    Set rValues = Union(xValues, sValues)
    but it is not working…
    any idea?
    thanks,
    Maria

  8. Maria –

    Oh, I think I get what you want. The problem is that taking the union of some of the Y values may result in a discontiguous range, and you can’t just use row i of a discontiguous set of rows. You need to talk about the range (the union), the areas in the range, the rows in the area. It’s messy, whereas just using the whole DataBodyRange is one area, so it’s neater.

    You have to specify data for each series at a time, X values, Y values, and series name. You may as well just do it explicitly instead of in a loop.

    Maybe something like this:

    With cht.SeriesCollection(1)
      .Name = "AVG_UL_POWER"
      .Values = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_UL_POWER”).DataRange
    End With
    With cht.SeriesCollection(2)
      .Name = "AVG_DL_POWER"
      .Values = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_DL_POWER”).DataRange
    End With

    Of course, you could always use the filtering in a pivot field to show only the items you want plotted, and it reverts to the example in this article.

  9. Thanks Jon,
    I have this procedure now… I am trying to plot two charts from the same pivot table.
    I am getting an error when trying to populate serie 1 for chart 2,which is in line

    .Values = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_MS_DISTANCE”).DataRange

    Do you understand why?
    Thanks again,
    Maria

    Sub updatePivotChart()
    Dim rCategories As Range
    Dim rValues As Range
    Dim pt As PivotTable
    Dim cht As Chart
    Dim pht As Chart

    ‘ Define the Pivot Table

    Set pt = ActiveSheet.PivotTables(“PivotTable1”)
    ‘ Define the Ranges
    With pt.RowRange
    Set rCategories = .Offset(1).Resize(.Rows.Count – 1)
    End With
    ‘ Define chart 1 and 2
    Set cht = ActiveSheet.ChartObjects(“Chart”).Chart
    Set pht = ActiveSheet.ChartObjects(“Chart 1”).Chart

    ‘ Populate Each Series for chart 1
    With cht.SeriesCollection(1)
    .Name = “Sum of AVG_DL_POWER”
    .Values = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_DL_POWER”).DataRange
    .XValues = rCategories
    .Border.LineStyle = xlNone
    chttype = .ChartType
    .ChartType = xlColumnClustered
    End With
    With cht.SeriesCollection(2)
    .Name = “Sum of AVG_UL_POWER”
    .Values = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_UL_POWER”).DataRange
    .XValues = rCategories
    .Border.LineStyle = xlNone
    chttype = .ChartType
    .ChartType = xlColumnClustered
    End With

    ‘ Populate Each Series for chart 2
    With pht.SeriesCollection(1)
    .Name = “Sum of AVG_MS_DISTANCE”
    .Values = pt.PivotFields(“Values”).PivotItems(“Sum of AVG_MS_DISTANCE”).DataRange
    .XValues = rCategories
    .Border.LineStyle = xlNone
    chttype = .ChartType
    .ChartType = xlColumnClustered
    End With
    With pht.SeriesCollection(2)
    .Name = “Sum of INTER_IN_HO_FL”
    .Values = pt.PivotFields(“Values”).PivotItems(“Sum of INTER_IN_HO_FL”).DataRange
    .XValues = rCategories
    .Border.LineStyle = xlNone
    chttype = .ChartType
    .ChartType = xline
    End With

    End Sub

  10. It worked for the first chart?
    What’s the error (not error number but error description)?
    What’s the chart type?
    Did the series have any data points before running the code?
    Is “Sum of AVG_MS_DISTANCE” spelled correctly?
    Is “Sum of AVG_MS_DISTANCE” a visible pivot item?

  11. Thanks Jon. The error was because I had not added the series in the chart so now it is working!

    The problem with this procedure is that you need to define the chart manually instead of creating it directly with the procedure so now I am trying to do this:

    ‘ Populate Each Series for chart 1
    With ActiveSheet.ChartObjects.Add _
    (Left:=100, Width:=375, Top:=75, Height:=225)
    .Chart.SetSourceData Source:=Sheets(“Sheet2”).Range(“22B:26B”)
    .Chart.ChartType = xlLine
    End With

    The issue now is if I can choose the data dor the chart as I did in the previous procedure( with pt.PivotFields(“Values”).PivotItems(“Sum of AVG_DL_POWER”).DataRange ).
    My try is..

    ‘ Populate Each Series for chart 1
    With ActiveSheet.ChartObjects.Add _
    (Left:=100, Width:=375, Top:=75, Height:=225)
    .Chart.SetSourceData Source:=pt.PivotFields(“Values”).PivotItems(“Sum of AVG_DL_POWER”).DataRange
    .Chart.ChartType = xlLine
    End With

    But It is not working…
    thanks!
    Maria

  12. Maria –

    If SetSourceData includes any pivot table data, the chart will automatically convert into a pivot chart based on that pivot table.

    You have to make sure a blank cell surrounded by blank cells is selected before you use ChartObjects.Add, which will produce a blank chart with no data series. Then you have to add each series:

    With ActiveSheet.ChartObjects.Add(left, top, width, height).Chart
      With .SeriesCollection.NewSeries ' first series
        .Values = whatever
        .XValues = whatever
        .Name = whatever
        .ChartType = xlLine
      End With
      With .SeriesCollection.NewSeries ' subsequent series
        .Values = whatever2
        .XValues = whatever2
        .Name = whatever2
        .ChartType = xlLine
      End With
    End With

    Also, Range(“22B:26B”) is incorrect. If you want that particular range it is referenced as Range(“B22:B26”). But again, you probably do not want to use SetSourceData.

  13. Thanks again!

  14. It is me again! Is is posible to create pointers in VBA?
    I have a config table with all the parameters in sheet2 of the xlms.
    Series Graph Title Legend GRAPH_LOCATION X-Axis
    TCH_ASS_FR TCH_Assignment xlLegendPositionBottom DATETIME
    TCH_ASS_SUC TCH_Assignment xlLegendPositionBottom DATETIME
    TCH_ASS_ATT TCH_Assignment xlLegendPositionBottom DATETIME

    And in the previous macro , I would like to automatically create charts when inserting data in the config table. For that, I am trying to get the name from the column ‘Series’ and what is more difficult, get the data from the column of the pivot table which belongs to that name ( In this case ” $Sheet2!A2″= TCH_ASS_FR )

    .Name = ” $Sheet2!A2″
    .Values = pt.PivotFields(“Values”).PivotItems(” $Sheet2!A2″).DataRange

    Any idea?
    Thanks again!
    Maria

  15. Maria –

    Like this?

    Dim sName As String
    sName = ThisWorkbook.Worksheets(“Sheet2”).Range(“A2”).Value

    .Name = sName
    .Values = pt.PivotFields(“Values”).PivotItems(sName).DataRange

  16. Yes, exactly! THank you!
    Maria

  17. Hi again, Good morning!

    Let’s see if I can get some help this time…I have a pivot table with a filter field called CELL_NAME ..I am doing several charts of each CELL_NAME.
    I have the charts appearing in a different sheet(sheet 3) but I want them in a diferent sheet.The problem is that I need the filter field to be in the same sheet as the charts, as it is more user friendly.. so I would need to ‘duplicate’ the filter for CELL_NAME to appear also in the same sheet as the charts.
    I have tried with getpivotdata() but no luck and also I am not sure what is that function for..Also, I have thought that maybe it is posible to create a new pivot table and link both somehow…Although I would prefer not having two pivot tables….
    any idea?
    thanks in advance!!
    Maria

  18. First thanks for the intelligent and helpful advice. I was actually able to create a new VBAProject Module, paste in a Macro and then get it to work, when a PivotChart updates. I have never used Excel Macros before so this was a big step.

    But (of course), my challenge is a bit different . . . I have a PivotTable . . that then populates another Worksheet of my Workbook . .. and on the same Worksheet as the PivotTable, I have a customized (non-Pivot) Line Graph that is based on a fixed series of cells from the other Worksheet.

    The code as described certainly updates the Line Graph, but it updates it using data from the PivotTable directly . . . I want it to use the enhanced values (w. additional data) from the non-Pivot Worksheet. And it does not . . .

    So I want the LineGraph to update from the non-Pivot Worksheet whenever the PivotTable is updated. Does that make sense?

    Thanks for your suggestions.
    Doug

  19. Doug –

    You can use the same event that launches the current macro, but link to the other chart and the other data set.

  20. Alberto Basantes says:

    hi. I have a question.

    I have an slicer linked to two pivot tables that generate two regular graphs. I want this code to work everytime I move the slicer and both pivot tables are refreshed, so that both graphs can be updated at the same time.

    Meanwhile what I managed to do is instead of using –UpdateChartFromPivotTable– i “Call” the Macro that wrote following this tutorial.

    But i dont know if this is the best way to do this.

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0