Peltier Tech Blog

Excel Charting Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog

 

Main menu:

 
Peltier Tech
Chart Utilities

PTS Waterfall Chart Utility
PTS Cluster-Stack Column Chart Utility
PTS Box and Whisker Chart Utility
PTS Marimekko Chart Utility
PTS Dot Plot Utility
PTS Cascade Chart Utility

Subscribe

Site search

Subscribe

Site search


Recent Posts

Recently Commented

Popular Posts

Archive


 

Categories


 

Buy me a coffee

If this topic or the whole blog has been helpful, support further development by treating me to a cup of coffee.


Privacy Policy

Creative Commons License
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

Update Regular Chart when Pivot Table Updates

by Jon Peltier
Wednesday, July 16th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

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

 

Related Posts:

Bookmark and share this entry:
  • Twitter
  • Digg
  • Facebook
  • LinkedIn
  • del.icio.us
  • Technorati
  • StumbleUpon
  • Google Bookmarks
  • Reddit
  • MySpace
  • Slashdot
  • Yahoo! Buzz
  • Tumblr

Learn how to create Excel dashboards.

Write a comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Read the PTS Blog Comment Policy.





Subscribe without commenting

PTS Waterfall Chart Utility PTS Cluster-Stack Column Chart Utility PTS Box and Whisker Chart Utility PTS Marimekko Chart Utility PTS Dot Plot Utility PTS Cascade Chart Utility

Create Excel dashboards quickly with Plug-N-Play reports.