Dynamic Charts

Update Regular Chart when Pivot Table Updates

Wednesday, July 16, 2008 by Jon Peltier 21 Comments

Wednesday, July 16, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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

Posted: Wednesday, July 16th, 2008 under Dynamic Charts.
Tags: Pivot Tables, VBA.
Comments: 21

Microsoft MVP Logo

Dynamic Chart Source Data

Thursday, July 3, 2008 by Jon Peltier 129 Comments

Thursday, July 3, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Dynamic Chart Using Defined Name for Source Data

A dynamic chart can be constructed using dynamic ranges that change in size as data is added or removed. This technique is very powerful: you can define flexible ranges limited only by your ability to write an appropriate Refers To formula.

One limitation of this approach is that it can only account for dynamic numbers of points in a series, but not dynamic numbers of series in a chart. Using VBA you can work around this limitation.

Let’s start with a simple data range as shown below. Following best practices, the data is arranged with series in columns, the category labels (X values) are in the first column, the series names are in the first row, and the top left cell of the range is blank. Using the Define Names dialog, we can define a name using these parameters:

Name: ChtSourceData
Refers To: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)

When we click Add and then click in the Refers To box, the range defined by the Refers To formula is highlighted by the marching ants border.

When creating a chart, or editing its source data, we can enter a dynamic range name in the Data Range box.

Excel accepts this definition of the chart data, but it converts the dynamic range name to a static range address. This means that our chart does not update as the dynamic range changes in size.

Here is our chart created with the dynamic range ChtSourceData. When the plot area or chart area of the chart is selected, the source data is highlighted.

Add some data to enlarge the dynamic range. Open the Define Names dialog, select ChtSourceData, and click in the Refers To box: The highlighted range shows that the dynamic range has enlarged to include all the added data.

The chart has not changed, but with a simple VBA procedure we can change the chart’s source data range to reflect the new dimensions of our defined range:

Sub UpdateChartSourceData()
  With ActiveSheet
    .ChartObjects(1).Chart.SetSourceData _
        Source:=.Range("ChtSourceData"), _
        PlotBy:=xlColumns
  End With
End Sub

This procedure should be placed into a regular code module, as described in How To Use Someone Else’s Macro.

Here is the updated chart:

To make this work more dynamically, you can include the SetSourceData command in a Worksheet_Change event. Right click on the sheet tab, choose View Code, and the VB Editor will appear with a code module representing the active sheet. Put this procedure into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Me.Range("ChtSourceData")) Is Nothing Then
    Me.ChartObjects(1).Chart.SetSourceData _
        Source:=Me.Range("ChtSourceData"), _
        PlotBy:=xlColumns
  End If
End Sub

This procedure executes whenever there is a change to a cell on the worksheet (not a calculation, but a change in an entered value or formula). The If checks whether the change occurred within the dynamic range ChtSourceData; if so, it resets the chart’s source data range. It’s automagic.

Better Way: Dynamic Chart Using Table for Source Data

There’s an easier way to make your whole chart dynamic, adding or removing points or series in the chart as rows and columns are removed or added in the source data range. This approach uses an Excel Table; it does not require brain bending Name definitions, and it doesn’t require any VBA coding. All it requires is that the chart uses a simple contiguous rectangular range.

The first step is to select the data range, and press Ctrl+T. A dialog pops up: make sure the indicated range is correct, and the Excel guesses correctly about headers, and click OK.

Create Table Dialog

The range is formatted as a Table, and has the tell-tale filter dropdown buttons in the top row.

Since the chart uses the entire table for its data, meaning X and Y values use all columns of the table, series names use the header row of the table, and the series points use all rows, then if the size of the table changes by adding or removing rows or columns, the chart will continue to use the entire table, and will add or remove series and points to the chart.

This is just one example of the power and flexibility of Excel’s Tables.

Posted: Thursday, July 3rd, 2008 under Dynamic Charts.
Tags: .
Comments: 129

Microsoft MVP Logo

Physics Lesson

Sunday, June 15, 2008 by Jon Peltier 7 Comments

Sunday, June 15, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A high school physics teacher was asking questions in the Excel programming newsgroup about his VBA procedure that accepted input values (time, position, velocity, and acceleration) from the workbook, calculated values of position and velocity vs. elapsed time, and charted the results. I answered a couple of his VBA questions, but I suggested that he change his approach to use worksheet formulas. I decided that it would be a good blog topic, so here goes.

Excel simulation of 1-D motion

[Read more…] about Physics Lesson

Posted: Sunday, June 15th, 2008 under Dynamic Charts.
Tags: Dynamic Charts, Educational, Interactive Charts, Physics.
Comments: 7

Microsoft MVP Logo

Dynamic Charts

Wednesday, May 14, 2008 by Jon Peltier 132 Comments

Wednesday, May 14, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Oh no, not another article about Dynamic Charts! There must be dozens of articles on the interweb about dynamic charts, some of them even useful. I didn’t even write the first Dynamic Charts article on my web site; that honor goes to Debra Dalgleish‘s Dynamic Column Chart. Debra enjoyed writing that tutorial so much, she went on to build an extensive library of tips on her Contextures web site, then go on to author several books on Pivot Tables:

Excel Pivot Tables Recipe Book: A Problem-Solution Approach
Excel 2007 PivotTables Recipes: A Problem-Solution Approach
Beginning PivotTables in Excel 2007: From Novice to Professional

Debra’s first example showed how to make a dynamic chart that added more data to the chart as it was added to the worksheet. You can define names to work with any cut of the data you want, if you’re clever enough writing formulas. But a chart that automatically expands with its data range is a good topic, so I’ll repeat it using the data below:

Vertically Oriented Data for Dynamic Line Chart

The data is a simple table of page views by month for my web site. The categories and values are arranged in columns; Excel will plot either by rows or by columns, but arranging your data by columns is probably a good habit to get into. Databases return data with this orientation, and Excel has longer columns and shorter rows. A chart series can contain up to 32,000 points, but if your series are aligned in rows, you are limited by the 256 columns in a worksheet (except for Excel 2007, of course). This example uses data in columns; if your data is aligned in rows, follow along, and there will be a special section at the bottom to help you apply the technique to your data.

Here is a simple line chart of this data:

Dynamic Line Chart

Defining Names

To make this chart dynamic, for all modern versions of Excel (i.e., 97 and later), you need to define some dynamic ranges that describe the dynamic data. Excel calls these Names.

On the Insert menu, choose Names > Define, or on the Excel 2007 Formulas tab, choose Define Name, or in either version press CTRL+F3. The Excel 97-2003 Define Names dialog is shown below; the Excel 2007 version of the dialog is much the same.

Define Names Dialog

I have entered StaticCats (for “static categories”) for the Name being defined. To populate the Refers To box you can enter a constant, select a range with the mouse, or construct a formula. I’ve used an OFFSET formula, because that is a handy way to define a dynamic range. The formula is

=OFFSET($A$1,1,1,4,1)

In the Offset function, there are up to five arguments: the range which defines the anchor for the range, the row offset from the anchor to the defined range, the column offset from the anchor to the defined range, the height in rows of the defined range, and the width in columns of the defined range. When you have added the name, you can click in the Refers To box, and the defined range will be highlighted by the “marching ants” border, as shown above. The marching antss are highlighting the range B2:B4, which starts one row down and one column right of the reference $A$1, and which is four rows tall and one column wide.

That’s how Offset works, but that example isn’t dynamic. We’ll use the following Refers To formula to define a dynamic name, VertValues:

=OFFSET($B$1,1,0,COUNT($B:$B),1)

Instead of hard-coding a value for the number of rows in the range, I use COUNT($B:$B), which counts the numerical values in column B. Note: to avoid counting errors, make sure there are no blank rows in the data range, and no stray numerical data in column B below this table, or enter a limited range within which to count, as in COUNT($B$1:$B$25).

Let’s define a dynamic name for category labels, VertCats. There are two ways to do this. One way is to write a parallel OFFSET function that counts appropriate entries in the categories column:

=OFFSET($A$1,1,0,COUNTA($A:$A),1)

Here I’ve used COUNTA which counts all non-blank cells. There was no header label in cell A1. If there had been, I would have to use COUNTA($A:$A)-1 for the number of rows.

An easier and more consistent definition for VertCats makes use of the fact that we already know how long VertValues is, and offsets from that range:

=OFFSET(VertValues,0,-1)

The -1 signifies that VertCats is one column left of VertValues. I can omit the length and width arguments, and VertCats will have the same dimensions as VertValues.

Working with Names

The built-in name management tools are not very powerful, though they’ve improved in Excel 2007. If you plan to use names in Excel, do yourself a favor and run, don’t walk, to Jan Karel Pieterse’s web site and download the Name Manager. This flexible and powerful tool makes working with names effortless.

NOTE: Excel 2007 Charts and Names of Dynamic Ranges

One strange detail recently came to my attention. Dynamic names can be used in Excel 2007 charts as well as in Excel 2003 and earlier charts. If you give the dynamic range a name beginning with the string “Chart”, in Excel 2007 you cannot edit the series formula to accept this name, although you can enter the name using the Select Source Data dialog. I’ve always used a shorthand like “cht”, so I didn’t notice it. But a colleague of mine, another Excel MVP, pointed it out, and more recently I saw it mentioned on another blog.

Making the Chart Dynamic

How do we get the names into the chart? I find the easiest way is through the series formula, but you can also use the Series Data dialog.

The chart above has a simple series formula:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5,1)

A series formula has four arguments (five for a bubble chart), which are series name, category labels or X values, Y values, and plot order. In most cases, the X and Y values are range addresses. These addresses can be replaced by the appropriate names:

=SERIES(Sheet1!$B$1,Sheet1!VertCats,Sheet1!VertValues,1)

The chart is unchanged, because the names happened to refer to the cell addresses. One interesting note, since the names were defined with a worksheet scope, is that the series formula will change to show the workbook name, not the worksheet name, in the reference to the name:

=SERIES(Sheet1!$B$1,Book1.xls!VertCats,Book1.xls!VertValues,1)

The difference between workbook-scoped and worksheet-scoped names will be covered in another blog post.

The other way to include the names into the source data is via the Source Data dialog. In Classic Excel, go to Chart menu > Source Data > Series tab; in Excel 2007 go to Chart Tools > Design tab > Select Data; in any version, right click on the chart, and choose Source Data > Series tab or Select Data. Select the series, and you will see the X and Y data ranges in the edit boxes.

Replace the cell references in these addresses by the names you’ve defined. Be sure to include the sheet name, or Excel will not know where to find the range.

When the data for May is added, the names automatically update, and so does the chart. You can see that the highlighted ranges in the sheet have expanded to include May (these colored highlights appear when a chart series is selected).

Dynamic Chart

Series in Rows

“But my data’s in rows! What do I do?” (remember Lisa Lupner and Todd DiLaMuca?)

Since my data’s in months, I’m not going to run out of columns for 20 more years. Suppose my data looks like this:

Horizontally Aligned Data for Dynamic Line Chart

The names use OFFSET as above, with the row and column treatments reversed:

HorizCats
=OFFSET(Sheet2!$A$1,0,1,1,COUNTA(Sheet2!$1:$1))

HorizValues
=OFFSET(Sheet2!$A$2,0,1,1,COUNT(Sheet2!$2:$2))

Enter these names into the series formula:

=SERIES(Sheet1!$A$2,Sheet1!HorizCats,Sheet1!HorizValues,1)

And the chart works the same as above when May’s stats are added.

Further Information

As I said earlier, there are about three bazillion other pages on dynamic charts, and here is where to find more examples on my web site. The Dynamic Chart Links page contains links to other sites.

Dynamic and Interactive Charts (overview)
Dynamic Column Chart
Another Dynamic Chart
Chart the Last 12 Months
Dynamic Charting By Dates (TechTrax Article)
Charting Dynamic Normalized Ranges
Dynamic Chart Links

Posted: Wednesday, May 14th, 2008 under Dynamic Charts.
Tags: dynamic range, names.
Comments: 132

Microsoft MVP Logo

Dynamic Chart using Pivot Table and VBA

Friday, April 25, 2008 by Jon Peltier 73 Comments

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.

Regular Dual Cat Axis Chart from Pivot Table Data

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:

Updated 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.

Posted: Friday, April 25th, 2008 under Dynamic Charts.
Tags: Pivot Tables, VBA.
Comments: 73

Microsoft MVP Logo