Dynamic Chart Source Data

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

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.

Bookmark and share this entry:

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

Comments

Comment from sam
Time: Friday, July 4, 2008, 12:56 pm

Hi JP,

Dont use Offset – Its Volatile

Use ChrtArea = Index($1:$65536,Counta($A:$A)+1,Counta($1:$1)+1)

Assuming Data Stars from Cell A1

sam

Comment from Jon Peltier
Time: Friday, July 4, 2008, 2:43 pm

Sam – Thanks for the suggestion. Actually your formula links to the bottom right of the range, so this is the appropriate form of the Refers To formula:

=$A$1:INDEX($1:$65536,1+COUNTA($A:$A),1+COUNTA($1:$1))

or, since Excel inserts the sheet name:

=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,1+COUNTA(Sheet1!$A:$A),1+COUNTA(Sheet1!$1:$1))

It’s a good point about volatility of functions. Since I usually have small worksheets, I usually don’t worry about the calculation impact of my range definitions.

Comment from Doug
Time: Saturday, September 27, 2008, 2:31 pm

This is a great tip. But I need help on one more step. I am setting up the dynamic update in the worksheet code. Your code works for 1 chart but I need to update 2 charts on the same page. How would I modify the VBA code to support 2 different charts on the worksheet?

Comment from Jon Peltier
Time: Saturday, September 27, 2008, 8:53 pm

Doug -

You need to define a range for each chart, and you should name each chart. To name a chart, hold Shift while selecting the cart, and type a distinctive name in the Name Box. Something like DynoChart1 and DynoChart2. If you have questions about this, see my tutorial Chart Names. Use ranges named DynoRange1 and DynoRange2. Then in the code:

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

Comment from Nick
Time: Friday, October 3, 2008, 4:48 pm

Jon,

Cam across your website in need of excel guidance.
This is great and I have been playing with this and learning.

However, when I execute the code, it resets the labeling of the series in the chart.
Thinking about it, I could define a name for each series and then add an update into the macro to update the series using the definition but I think there must be a quicker way of doing this especially when there are several series.

Thanks,
Nick

Comment from Jon Peltier
Time: Friday, October 3, 2008, 4:56 pm

Nick -

In my example, the series names are in the first row of the range “ChtSourceData”. Keep the top left cell blank, put the series names in the rest of the top row of this range, and the X values or category labels in the rest of the first row.

Comment from CIBER
Time: Wednesday, November 19, 2008, 4:21 am

I have a senario in which i have to change the source data of the graph by using some validation inputs,
Eg : i have a set of data in rows which is used to create the graph , now i have to change the source data with refference to the a particular row.
+ values to select the rows after refference row & -ve for rows before refference row .

say if i give +3 , -5 , then the source data become 5+3+1(reffernce row ) = 9 rows totally shd be used to create the graph

All this has to be done dyanamically how can it be done.

Comment from Jon Peltier
Time: Wednesday, November 19, 2008, 5:58 am

CIBER -

I generally use Forms menu controls for this, but you can use validation just as well.

When using worksheet formulas, usually OFFSET and INDEX are used to define the position and size of a range. In VBA one uses .Offset and .Resize for the same purpose. Your arguments for these functions have to include references to the cells with the validation or control link values.

For example, referring to the Name defined at the top of this article:

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

If cell D1 contains the value that indicates my row offset for the start of the data, I would insert it into this formula:

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

Comment from Ardalan
Time: Friday, November 6, 2009, 12:02 pm

Hi Jon,
Thanx for your great ideas.
I have a problem with making a X-Y dynamic chart. Actually i have around 30 X & 30 Y values in collumns tight together (X1 Y1, X2 Y2, etc) that can be shown in X-Y graph. What should I do that graph can be updated automatically without using source data/addseries one by one.

Comment from Jon Peltier
Time: Friday, November 6, 2009, 1:38 pm

Ardelan –

How is the source data changing? Is the range changing size?

You might be able to use the utility in How to Edit Series Formulas to adjust the last row, especially if all data ends at the same row.

Comment from Ardalan
Time: Saturday, November 7, 2009, 12:50 am

Jon,
The ranges are like this:
X 1 = A1:A500 Y 1 = B1:B500
X 2 = C1:C500 Y 2 = D1:D500
.
.
X 30 = AD1:AD500 Y 30 = AE1:AE500
These data come from some calculations of other data. I have put a formula if there is no data it replies #N/A for my graph. For each X & Y graph should draw a line. I want this adding happens automatically, ie.; if I have 3 set of XY data , the chart only draw 3 lines & in case I add more to these 3, say 2, Graph should automatically add another 2 series & shows 5 lines and in case I delete one, graph reduces one.

Comment from Jon Peltier
Time: Saturday, November 7, 2009, 11:36 am

Ardalan -

I see. You may have one to 30 series, and you want the existing data sets to automatically be charted. Any data sets which are removed, you want the series also removed. This could ba accomplished through a dedicated VBA procedure.

I assumed one worksheet, ChartData, which had potentially plottable data in A1:P20, and which also had one embedded chart, the one to be upddated. This procedure clears all series from the chart, checks each pair of columns in the data range, and if both columns contain plottable data (e.g., not errors), a series is added using these two columns.

Sub DynamicChartRange()
  Const sADDRESS As String = "A1:P20"
  Dim rTotal As Range
  Dim cDynamic As Chart
  Dim iSrs As Long

  Set rTotal = Worksheets("ChartData").Range(sADDRESS)
  Set cDynamic = Worksheets("ChartData").ChartObjects(1).Chart

  ' clear the chart
  Do While cDynamic.SeriesCollection.Count > 0
    cDynamic.Legend.LegendEntries(1).LegendKey.Delete
  Loop

  ' add series from total range
  For iSrs = 1 To rTotal.Columns.Count / 2
    ' check columns for data
    If WorksheetFunction.Count(rTotal.Columns(iSrs * 2 - 1)) > 0 And _
        WorksheetFunction.Count(rTotal.Columns(iSrs * 2)) > 0 Then
      ' we have data to plot
      With cDynamic.SeriesCollection.NewSeries
        .Values = rTotal.Columns(iSrs * 2)
        .XValues = rTotal.Columns(iSrs * 2 - 1)
      End With
    End If
  Next

End Sub

Comment from Sam R
Time: Thursday, January 14, 2010, 4:48 am

Hi Jon,

Thanks for this tip. I am working with graphs on one excel sheet that will have to update according to data located in another sheet.

Is there anyway to modify the macro “Private Sub Worksheet_Change(ByVal Target As Range)” to take this into account?

I have tried to add sheet names to:
-Me.Range(“ChtSourceData”) –> Me.Sheets(“SheetName”).Range(“ChtSourceData”)
or
-Me.ChartObjects(1).Chart –> Me.Sheets(“SheetName”).ChartObjects(1).Chart

But got an error message every time. The reason I don’t want to simply put “=” links from one sheet to another to have all data on the same sheet is that the excel file I am working with is already quite slow due to other calculations.

Comment from Jon Peltier
Time: Thursday, January 14, 2010, 10:58 am

“Me” refers to the sheet that the code is attached to. Since the code fires when the data changes, it should go on the code module for the sheet with the chart data.

Me.Range(“ChtSourceData”) is still correct. To access the chart you need
ThisWorkbook.Sheets(“SheetName”).ChartObjects(1).Chart

Note that the Worksheet_Change event will not detect a change if the formulas update to new values but the formulas themselves are unchanged. You can use the Worksheet_Calculate event, but you have to be careful that this doesn’t really bog things down.

Link formulas shouldn’t bog down the workbook very much.

Comment from Fabio
Time: Saturday, April 17, 2010, 11:32 pm

Hi Jon, I have a set of predefined charts in one xls sheet with data sources in many different sheets.

The data source sheets get refreshed from a series of Access queries.

I tried the method with naming the range but we I get the refresh I lose that information.

In other words, I would like to genrate xls charts based on new data I export from an access db.

Thanks

Comment from Jon Peltier
Time: Sunday, April 18, 2010, 10:19 am

Fabio -

Are you defining names using dynamic formulas? Does refreshing the queries delete cells, or merely clear unused cells?

Comment from Fabio
Time: Sunday, April 18, 2010, 2:42 pm

Actually Access made me a very nice “present”, it dynamically exports name raanges with the same name as the query and the worksheet, so with a simple vba code my dashboard gets updated automatically! This made my day and I can stop work for this weekend! I am attaching the code below, any suggestion to improve it is more than welcome (I am far from being a programmer).

Sub Auto_Open()
‘
‘ Code by fabio_italiano
‘

‘
‘Select “Charts” Sheet
Sheets(“Charts”).Select
‘Select the graph to update after renaming with Shit+click
ActiveSheet.ChartObjects(“Radar_Chart”).Activate
‘Select Chart Area
ActiveChart.ChartArea.Select
‘Open Data Source and Select the data range, which has the same name of the data sheet
‘Please note: TransferSheet function in Access exports data in a Excel Worksheets with the same query name
‘Automatically recreating a name range with the same name each time a new export occurs
ActiveChart.SetSourceData Source:=Sheets(“SAD_By_MG_MR_Emp”).Range(“SAD_By_MG_MR_Emp”) _
, PlotBy:=xlRows
ActiveWindow.Visible = False
Windows(“WFReports.xls”).Activate
‘Select the top-left cell in the Charts sheet
Range(“A1″).Select

End Sub

Comment from Jon Peltier
Time: Monday, April 19, 2010, 8:28 am

Sub Auto_Open()

  Worksheets("Charts").ChartObjects("Radar_Chart").Chart.SetSourceData _
    Source:=Sheets("SAD_By_MG_MR_Emp").Range("SAD_By_MG_MR_Emp"), _
    PlotBy:=xlRows

End Sub

Comment from Fabio
Time: Monday, April 19, 2010, 5:29 pm

Thank you Jon, I tried your optimized code but I get the following error:

“Object doesn’t support this property or method”

Comment from Jon Peltier
Time: Monday, April 19, 2010, 9:36 pm

Fabio -

My bad, I was trying to do it too quickly. I left out one little keyword. I’ve corrected my code.

Comment from Samantha
Time: Wednesday, June 2, 2010, 10:38 am

Dear Jon

Thank you, I’ve been searching for such a solution for several days, so this is great.

I have tried reproducing exactly your example given here, but I keep getting a compile error.

This is exactly what I have typed, have I made an obvious mistake? I tried re-typing it several times??

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range(“ChtSourceData”)) Is Nothing Then
Me.ChartObjects(“ChtSourceData”).Chart.SetSourceData _
Source:=Me.Range(“ChtSourceData”), _
PlotBy:=xlColumns
End If

End Sub

I’m using excel 2007.
Many thanks

Comment from Jon Peltier
Time: Wednesday, June 2, 2010, 9:10 pm

Samantha -

Make sure the code is in the worksheet’s code module: right click on the sheet tab and choose View Code to ensure that the correct code module is active.

Also make sure the double quotes surrounding the name of the range are normal straight quotes, not curly quotes.

Comment from Samantha
Time: Thursday, June 10, 2010, 11:00 am

Thanks for the advice Jon.
Turns out I had a full stop instead of a comma, always the simple pesky mistakes! Works a dream.
Thanks again!

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.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Main menu:

Peltier Tech
Chart Add-Ins

Excel Dashboards

Subscribe

Site search

Subscribe

Site search

Recent Posts

Recently Commented

Popular Posts

Archive

Categories