Dynamic Chart Source Data

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.

 

Peltier Tech Charts for Excel

Comments

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

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

  3. 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?

  4. 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
  5. 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

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

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

  8. 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)
  9. 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.

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

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

  12. 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
     
  13. 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.

  14. “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.

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

  16. Fabio –

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

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

  18. 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
  19. Thank you Jon, I tried your optimized code but I get the following error:

    “Object doesn’t support this property or method”

  20. Fabio –

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

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

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

  23. 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!

  24. Thank you so much for your website, Jon! I’m a VBA beginner/intermediate level, and the code you provide helps a great deal in understanding how it works.

    I have a set of chart templates, already created and formatted, where the series are not in columns but in rows and the categories are in columns. They do have to be displayed this way. So far, neither the OFFSET nor INDEX formulas work in setting the initial chart range. Is there something different I need to do when charts are set up this way?

    Thanks for any help.

  25. Jacquelne –

    In the code you have to change

    PlotBy:=xlColumns

    to

    PlotBy:=xlRows

    The Offset should work the same way, but the formula as stated assumes the top left cell of the data range is blank and that there’s no other data in the sheet that may get counted.

  26. Hi, this is very useful code, but I have a doubt, I need the graph in a separate sheet that shows only the graph, not the data, and I always get this error “Object doesn’t support this property or method” or “out of range”, can you help me please! thanks in advance!!

  27. Daniel –

    A chart sheet has a different hierarchy in the object model. Change the first block of code to:

    Sub UpdateChartSourceData() 
      Charts("Chart Tab Name Here").SetSourceData _ 
            Source:=.Range("ChtSourceData"), _ 
            PlotBy:=xlColumns 
    End Sub

    and change the second to:

    Private Sub Worksheet_Change(ByVal Target As Range) 
      If Not Intersect(Target, Me.Range("ChtSourceData")) Is Nothing Then 
        Charts("Chart Tab Name Here").SetSourceData _ 
            Source:=Me.Range("ChtSourceData"), _ 
            PlotBy:=xlColumns 
      End If 
    End Sub
  28. Hi Jon!! Thank you very much for this!!! warm regards!

  29. Hi, Jon.

    I need to adapt the solution you provided for Daniel with a dedicated worksheet for data, to an embedded Excel workbook supporting a PowerPoint chart.

    Can you provide an example of how to address this in your 2 macros?

    I am using Office:mac 2011.

    FYI, the data is referenced to an external source. It seems to me that this should be compatible with your auto-update solution. But if I am wrong, please set me straight.

    Thanks for the support. Much.

    Nancy

  30. Nancy –

    Two potential issues:

    1. Mac Excel. Even the versions of Mac Office that contain VBA have an ancient underpowered version, equivalent to the VBA in Excel 97 for Windows. I don’t know what works and doesn’t work.

    2. Code and links in embedded workbooks do not always seem to work as expected, so I almost never work with embedded workbooks. My preference is to keep the Excel parts in a standalone Excel workbook, copy the chart, and paste-link it into PowerPoint.

    The fact that the source data is linked to an external source should have nothing to do with how these routines work. However, if you are relying on the Worksheet_Change event, it will not fire on an update from an external source. Worksheet_Calculate or even Workbook_Open (in the ThisWorkbook module) will be more likely to update reliably. I don’t know whether the embedded workbook has to be activated for any of this to take place.

  31. Hi Jon,

    I’ve been dealing with an error around this code for days now and I cannot figure out what’s wrong!! The code below resides in the Worksheet_Change method in my ‘Charts’ worksheet where the chart ‘Chart_FacilityTrend’ also resides. The named range ‘ChartFacilityTrendValues’ references a range in my ‘Calcs’ worksheet.

    The code: ChartObjects(“Chart_FacilityTrend”).Chart.SetSourceData Source:=Range(“ChartFacilityTrendValues”), PlotBy:=xlColumns

    The error: “Method ‘Range’ of object ‘_Worksheet’ failed”

    Any insight would be greatly appreciated! Thanks!

  32. Joe,

    Try specifying the worksheet for the range in the ‘source:=’ statement.

    ie source:= thisworkbook.worksheets(1).range(…

    That has worked for me when using embedded objects

  33. Thanks, Erick! That was the trick!!

  34. Hi Jon

    I’ve tried updating the code you suggest above to place a chart in a different worksheet to the data and keep getting errors. My current code is

    Sub UpdateChartSourceData4()
    Charts(“CostChart”).SetSourceData _
    Source:=csanalysis1.xls.Worksheets(1).Range(“UnitCostColumn”), _
    PlotBy:=xlColumns
    End Sub

    and I get error 424 object required.

    Before that with just the .Range I got a compile error – Invalid or unqualified reference. It works when chart is in same sheet as data

    Sorry I’m not very familiar with VB but would be grateful for any help

    regards
    Paul

  35. Try

    Source:=Workbooks(“csanalysis1.xls”).Worksheets(1).Range(“UnitCostColumn”)

  36. Hi,
    I am new to this forum. I am struggeling with creating multiple charts from one set of data in Excel using VBA.
    I am able to create my first correct charts by:
    Code:

    Set chtChart = Charts.Add
    With chtChart
    .Name = “ANA_” & Counter1
    .ChartType = xlXYScatterLinesNoMarkers
    .SetSourceData Source:=Range(“=’Sheet1′!$E$3:$E$24”), _
    PlotBy:=xlColumns
    .HasTitle = True
    .ChartTitle.Text = “=ANA_” & Counter1
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = “X-Axis”
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Y-Axis”
    End With
    MsgBox “Pause”
    ActiveChart.SeriesCollection(1).XValues = “=’Sheet1′!$A$4:$A$24”
    ‘Adding new Series

    With ActiveChart.SeriesCollection.NewSeries
    .Name = Sheets(“Sheet1”).Range(“F3”)
    .Values = Sheets(“Sheet1”).Range(“F4:F24”)
    .XValues = Sheets(“Sheet1”).Range(“‘Sheet1′!A4:A24”)
    End With

    Now I would like to replace the hardcoded address-ranges (i.e. “=’Sheet1’!$E$3:$E$24”) by a dynamic address. At the time my current location on Sheet1 is E3.
    For other commands I was able to replace it by:
    Range(Selection, Selection.Offset(lastitem-3, 0))

    This causes an error message.
    Any help would be highly appreciated.

  37. With respect to E3 (the selection), E3:E24 can be defined as

    Selection.Resize(22)

    Play with Excel and VBA. Tile the Excel and VB Editor windows side by side, and open the Immediate Window in the VB Editor. Type queries like

    ? Selection.Address

    in the Immediate Window, and it will tell you the address.

    Type commands like

    Selection.Offset(5).Select
    Selection.Resize(3,5).Select

    in the Immediate Window, and watch how the selection changes.

  38. Hi, Jon.

    thanks for your code, very useful.

    But if I try to delete the data source that the chart using. it will pop up error

    called”Method ‘Range’ of object_Worksheet’ failed”.

    I believe Me.Range() can find the data source.

    As I had to clear the content of the worksheet frequently, could you help me revise your code to overcome this issues.

    Thanks very much.

  39. Yunjing –

    Don’t delete the rows or columns containing the data. This deletes the home cell of the named range (B2 in my example) so that the names range is undefined:

    =OFFSET(Sheet1!#REF!,0,0,COUNTA(Sheet1!#REF!)+1,COUNTA(Sheet1!$2:$2)+1)

    Simply clear the range (select the range and press Delete or Clear Contents).

  40. Hi Jon,

    I understand the code you gave for updating a chart from a dynamic source. Is there a way to move a point on a chart and have it reflect that change in the supporting data table?

    Sal

  41. Sal –

    This used to be possible in Excel 2003 and earlier. You probably know this, and you probably were disappointed when Microsoft removed this capability.

    I’m working on a way to accomplish this, but it’s not yet fully developed. I may write about it sometime in the future.

  42. Thanks Jon for your reply,

    I added “On Error Resume Next”, so it can ignore the error temporarily, and your code can carry on even after clearing the contents of the Worksheet.

    But if I Move the chart in the Worksheet to a single Chartsheet, how to let the data range auto refer to the dynamic range in the previous worksheet.

    Thanks.

  43. Hi Jon,
    Am quite new to charts (and overwhelmed) hence a simple question.
    I have a nicely formatted charts with 6 different series; all against date on the Xaxis.
    Using setSourceData works, however clears off all existing series/formats/headers etc.

    How can I just target changing the ranges ?

    Thanks in advance

  44. Abhi –

    SetSourceData should not be changing any formatting. What else is in your code?

  45. Jon,
    Please ignore.
    I was having problems creating the name, hence resorted to the SetSourceData. Needless to say was trying to over complicate.

    The following worked fine for me
    INDEX(Static!$D:$D,5,1):INDEX(Static!$D:$D,cRows,1)

    Deployed the solution & was very much appreciated too.
    Thanks !

  46. Hi Jon,
    Hope you are doing great. Having issues with using named ranges formulas in excel 2007 charts. for whatever reason when ever i try inserting the named range in the chart i receive a error message around the formula, allthough the named range formula has no error in it, it extracts the data i need.
    i have 15 data points in the area d5:r5, and i want to chart last 12,last 10, last 6, last 5 data points.
    This is the formula i use for last 12 data points
    =INDEX(Sheet1!$D5:$AC5,COUNT(Sheet1!$D5:$AC5)-11):INDEX(Sheet1!$D5:$AC5,COUNT(Sheet1!$D5:$AC5))
    Can you please help, i can send you my file if its of any use

    Thanks a lot,

    Marc

  47. Marc –

    Make sure you’re up to date with Office 2007 Service Packs.

    Make sure none of the ranges have names starting with “chart”.

    Also, you cannot edit names in the formula bar in 2007, only fixed cell references. Big oops on this one. You need to go to the Edit Source Data dialog, and edit each series individually.

  48. hi,
    I am having power point with many graphs in each slides.data of each graph is different and is from excel data.(not an OLE ).excel data for each graph comes from another excel.So when that excel gets updated ,vba should update powerpoint.I donno how to code the data soure of a graph in a slide using VBA in powerpoint.
    kindly help me its urgent…………………..

  49. Hi Jon,

    Whenever i run the code that you gave above

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

    It always ends with an error “Method ‘Range’ of object ‘_Worksheet’ failed

    My graph and data are on the same worksheet called Calculations and the name given for my range is chtRange

  50. Thomas –

    Which line is highlighted with the error?

    Does the code run by itself, or do you have to manually run it?

    Did you put the code in the worksheet’s code module or in a regular module? If you right click on the sheet tab and choose View Code, you should see this procedure.

  51. Victoria Garcia says:

    Hi Jon

    I have a question: I have a spreadsheet with about 30 graphs that I have to update monthly with the next month’s data. I don’t want to change the range on each graph individually (for each of the 30 graphs) – is there a way to have a control date range that I would only change in one place and all the graphs would be updated with this new range?
    This would save me a lot of time.

    Thank you,
    Victoria

  52. Hi Jon,

    The sentence “If Not Intersect(Target, Me.Range(“chtRange”)) Is Nothing Then” is highlighted.

    The error appears whenever i try to change something on the excel worksheet. Regardless of whether it is inside the chtRange that i defined. “=INDEX(Calculations!$J$1,COUNTA(Calculations!$J:$J)+1,COUNTA(Calculations!$J23:$M23))”

    I followed your instructions to right click on the sheet tab and view code and then paste my code onto the open window in VBA

  53. Thomas –

    If you use INDEX, the first argument must contain all possible places you’d be indexing. If either of your COUNTAs are not exactly 1, then you will get an error (#REF!). And even if you expand the first argument to (say) Calculations!$J$1:$M$23, INDEX the way you’re using it will only identify a single cell as your target range.

    This would identify a 2D range:

    =Calculations!$J$1:INDEX(Calculations!$J$1:$M$23,COUNTA(Calculations!$J:$J)+1,COUNTA(Calculations!$J23:$M23))

  54. Victoria –

    You’d need to set up 30 separate clauses in an If-Then or Select Case block. Yikes.

    Or you could see if you could use the little tool I described in How to Edit Series Formulas.

  55. Hi Jon,

    How would it work if I have following Source Data and I would want to add rows only?
    =’1024′!$F$1;’1024′!$F$24:$F$66;’1024′!$J$1;’1024′!$J$24:$J$66

  56. Jon,

    I followed your site to automate the min and max on Y axis. However, the min and max values could change depending on multiple calculation events. So I have a formula in Cell C93 and C94 to pull the min and max using simple excel built functions. The problem lies in the fact that since the values in these cells are formula driven I understand the Target.Value is not the right solution. So I went on a goose chase to find an alternative and now I am back to you. All I want to do is use your code to auto update the min / max scales on the y – axis based on the values in C93 and C94 and that these values are formula driven. By the way, your code works flawlessly if I manually input values in this cell.

    As you can see I tried to go out on the web and found some ifs intersect but nothing worked.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim KeyRange As Range

    With Range(“C93:C94”)
    Set KeyRange = .Cells
    On Error Resume Next
    Set KeyRange = Application.Union(.Cells, .Precedents)
    On Error GoTo 0
    End With

    If Not Application.Intersect(Target, KeyRange) Is Nothing Then

    Select Case Target.Address
    Case “$B$93”
    ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlCategory) _
    .MaximumScale = Target.Value
    Case “$B$94”
    ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlCategory) _
    .MinimumScale = Target.Value
    Case “$B$95”
    ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlCategory) _
    .MajorUnit = Target.Value
    Case “$C$93”
    ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlValue) _
    .MaximumScale = Target.Value
    Case “$C$94”
    ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlValue) _
    .MinimumScale = Target.Value
    Case “$C$95”
    ActiveSheet.ChartObjects(“Chart 4”).Chart.Axes(xlValue) _
    .MajorUnit = Target.Value
    Case Else
    End Select
    End If
    End Sub

    ‘Read more: Link Chart Axis Scale Parameters to Values in Cells http://peltiertech.com/link-excel-chart-axis-scale-to-values-in-cells/
    Your help will be much appreciated.

  57. Niel –

    In this case, you need to use the Worksheet_Calculate event.

    Private Sub Worksheet_Calculate()
      With ActiveSheet.ChartObjects("Chart 4").Chart
        With .Axes(xlCategory)
          ' only change it if it needs changing
          If .MinimumScale <> ActiveSheet.Range("B94").Value Then
            .MinimumScale = ActiveSheet.Range("B94").Value
          End If
          ' etc. for MaximumScale and MajorUnit
        End With
        With .Axes(xlValue)
          ' etc. for Value axis
        End With
      End With
    End Sub
  58. Jon,

    Thanks for getting back to me quickly. The only problem I think I am having is

    1. When I Open and Close or work on another Excel file I get debug errors. I thought the code was private worksheet and is inserted specifically on the sheet where the chart is. So I don’t understand why working on another file or tab gives me a debug error.

    2. I have a similar chart in sheet 2 and which changes based on changes in sheet 1. So i guess i cannot use activesheet in this case the second sheet is not active while i am looking at the first.

  59. Niel –

    1. What are the error messages?

    2. This procedure goes in the worksheet with the data. Use the keyword “Me” for the sheet containing the VBA code. You need to specify the chart:

    Private Sub Worksheet_Calculate()
      With Worksheets("The Chart").ChartObjects("Chart 4").Chart
        With .Axes(xlCategory)
          ' only change it if it needs changing
          If .MinimumScale <> Me.Range("B94").Value Then
            .MinimumScale = Me.Range("B94").Value
          End If
          ' etc. for MaximumScale and MajorUnit
        End With
        With .Axes(xlValue)
          ' etc. for Value axis
        End With
      End With
    End Sub
  60. Mark Sheridan says:

    Hi Jon

    I get a Subscript Out Of Range error when I use this code in my DPD worksheet:

    Sub UpdateChartSourceData4()
    Charts(“Chart 10”).SetSourceData _
    Source:=Workbooks(“1205 WorkQueue Pivots May-12.xlsm”).Worksheets(“DPD”).Range(“ChtSourceData”), _
    PlotBy:=xlColumns
    End Sub

    ChtSourceData =OFFSET(DPD!$M$4,0,0,COUNTA(DPD!$M:$M)+1,COUNTA(DPD!$2:$2)+6)

    Can you help?

    Thanks

    Mark

  61. Mark –

    That means you’re trying to reference an item in a collection that doesn’t exist. Do you have a chart named (“Chart 10”) on the active sheet? Is there a workbook named “1205 WorkQueue Pivots May-12.xlsm”, which has a sheet named “DPD”, and which includes a range named “ChtSourceData”?

  62. Thank you! Thank you. Works like a charm.

  63. Mark Sheridan says:

    Jon

    Yes I have rechecked everything and all names are correct. I have the code in the DPD sheet. I can’t work it out. Very frustrating.

    Mark

  64. Mark –

    Too early to give up.

    Which line is highlighted when you see the error?
    Also, are all of the apparent spaces in the strings real spaces, and not non-breaking spaces?
    Is the chart a standalone chart sheet, or is it embedded in a worksheet? Your syntax is for a standalone chart sheet. For an embedded chart it would be

    Worksheets("name of worksheet").ChartObjects("name of chart object").Chart

    instead of

    Charts("name of chart sheet")

    Sometimes what I do is split out the pieces of the long lines, to see which piece is triggering the error:

    Dim cht As Chart, wb As Workook, ws As Worksheet, rng As Range
    
    Set cht = Charts("Chart 10")
    Set wb = Workbooks(“1205 WorkQueue Pivots May-12.xlsm”)
    Set ws = wb.Worksheets(“DPD”)
    Set rng = ws.Range(“ChtSourceData”)
    
    cht.SetSourceData Source:=rng, PlotBy:=xlColumns
  65. Mark Sheridan says:

    Hi Jon

    Yes, it was the embedded chart as opposed to chart sheet that was the problem (although the error message never highlighted any part of the code).

    Amended code:

    Sub UpdateChartSourceData4()

    Worksheets(“DPD”).ChartObjects(“Chart 10”).Chart.SetSourceData _
    Source:=Workbooks(“Copy of 1205 WorkQueue Pivots May-12.xlsm”).Worksheets(“DPD”).Range(“ChtSourceData”), _
    PlotBy:=xlColumns

    End Sub

    It works now.

    Thanks so much for your help.

    Mark

  66. Hi Jon – please give us (me and the voices in my head) a hand to create dynamic charts from data sitting in non-adjacent columns. The data is generated from a query and placed in the sheet. The chart is located on a seperate sheet.

    Any help will be highly appreciated

    Regards

    Peter

  67. Peter –

    This is dependent on the structure of the data and on what subset of the data you want to plot.

  68. Hello Jon,

    Thank you for your help. I have another problem though. I want my graph to only take into account cell that have a value in them. My source looks like

    Date Value
    Date Value

    Etc, except I prepared the file so that it would react to new lines thanks to formulas in the empty cells underneath those already filled: IF(A1=””,””,A1) so I have twenty lines or so underneath the ones with value in them (A1) that have no value but a formula in them.

    Excel still includes them in the creation of the graph. Can I somehow add a condition to the code (or the source of the graph) so that only the cells with value are taken into account?

  69. Anne –

    There are other ways to deal with expanding data ranges:

    Easy Dynamic Charts Using Lists or Tables
    Dynamic Charts

  70. I am trying to change the data sources based on a particular selection in the drop down. Could you please suggest, how should I do it???

  71. Hi Jon,

    Thanks for your excellent VBA skills, it has helped me alot today in going through dynamic chart plotting, especially the VBA MultiXYChart: http://peltiertech.com/Excel/ChartsHowTo/VBAExcelXYCharts.html#manyxoney

    However, I have an issue here, your code works well when I define a range containing 8 to 9 columns for a chart, but I don’t know how to get it create different chart for different data sets (each containing 8 to 9 columns of data) in the same sheet? For example I can now plot a chart for the range B1:Q15, but I am not able to define multiple ranges such as R1:AW15, AX1:BE15 and so on to plot a chart for each data set. Lets say I want this macro to loop through a range of datasets and plot a chart for each of them, it could be 30 to 40 charts in the same sheet. I need your help.

    Thanks in advance!

  72. Sanjeev –

    This is a tricky problem. If the sets of data are regularly arranged (always the same addresses on every sheet), you could hard code the range addresses, then loop through these. Or perhaps you could list the ranges in a worksheet range, and loop through this range, processing each address as you proceed. Or you could write complicated algorithms to hunt for suitable ranges in the sheet. Not so easy.

  73. Jon,

    Thanks for your reply. Well, eventually I found a solution, thats seems to be a dynamic one and it works. I used stepped function to call the pack of data sets. Like here:

    For j = 2 To 49 Step 16
    range(Cells(1, j), Cells(15, j + 15)).Select
    ‘Jon’s MultiXY_Chart Code
    Next j

    It has datapacks each of 16 columns and there would be as many as 15 to 20 datapacks. Please tell me if you could think of a problem that may occur in future, or any other amelioration would be wonderful.

  74. Hi Jon,

    This is regarding your MultipleXYChart code that plots several series in the same chart!

    I need a solution for a problem which might seem simpler to you, but I’ve no idea where to go! I have plotted several charts in a excel sheet, now I want to give an individual title to each chart, and since each chart has its own particular data range, it also has a column from where it can pull text from a cell to update its title. For e.g. the first chart can pull cell value from column A, second chart can pull from column K and so on.

    How would you solve this evasive problem?

    Thanks alot!
    Sanjeev

  75. Sanjeev –
    Are the cells with the titles in a particular relation with respect to the plotted data? For example, if the data is in column C starting in C2, is the title in cell C1? (FYI, this is the most efficient way to arrange the data, assuming you have control over the worksheet.)
    If not, you’re doing it manually, or at best, semi-manually.

  76. The titles are present in the column before the data columns! For e.g. data is between columns C to J through 15 rows and title is in Column B (through 15 rows but same in all, so we just need to refer to one cell). How do I reference a cell in column B as chart title which has its data in columns C to J?

  77. Sanjeev –
    First of all, if the names are adjacent to the values, the easiest way to assign them is to create the chart with the range that includes the names.
    But some users don’t know that, and charts are created without series names. The code below looks at the series formulas, determines whether the data is in rows (like yours) or in columns, then finds the cell to the left (as in your case) or above the values, and inserts it into the series formula.
    First there are three procedures which you would call to assign series names to (a) the selected series in the active chart, (b) all series in the active chart, or (c) all series in all charts in the active sheet. Then there is an intermediate procedure, then finally the procedure that does the work of parsing the formula and inserting the address of the cell with the name.

    Sub AssignNameToSelectedSeries()
      Dim srs As Series
      If LCase$(TypeName(Selection)) = "series" Then
        Set srs = Selection
        AssignSeriesName srs
      End If
    End Sub
    
    Sub AssignNamesToSeriesInActiveChart()
      If Not ActiveChart Is Nothing Then
        AssignSeriesNamesToChart ActiveChart
      End If
    End Sub
    
    Sub AssignNamesToSeriesInAllCharts()
      Dim chtob As ChartObject
      For Each chtob In ActiveSheet.ChartObjects
        AssignSeriesNamesToChart chtob.Chart
      Next
    End Sub
    
    Sub AssignSeriesNamesToChart(cht As Chart)
      Dim srs As Series
      For Each srs In cht.SeriesCollection
        AssignSeriesName srs
      Next
    End Sub
    
    Sub AssignSeriesName(srs As Series)
      ' assign series names to series in charts
      ' use cell above or to left of series values
      ' for series data by row or by column
      
      ' parse series formula
      Dim sFmla As String, sArguments As String, vArguments As Variant
      sFmla = srs.Formula
      sArguments = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
      vArguments = Split(sArguments, ",")
      
      ' get ranges
      Dim rYValues As Range
      Dim rName As Range
      
      Set rYValues = Range(vArguments(LBound(vArguments) + 2))
      If rYValues.Rows.Count = 1 Then
        ' by row: want cell to left
        Set rName = rYValues.Resize(1, 1).Offset(0, -1)
      ElseIf rYValues.Columns.Count = 1 Then
        ' by column: want cell above
        Set rName = rYValues.Resize(1, 1).Offset(-1, 0)
      Else
        ' dude
      End If
      
      ' get address
      Dim sNameAddress As String
      sNameAddress = rName.Address(True, True, , True)
      
      ' reconstruct & apply series formula
      vArguments(LBound(vArguments)) = sNameAddress
      sFmla = "=series(" & Join(vArguments, ",") & ")"
      srs.Formula = sFmla
    End Sub
  78. Hi Jon

    Referencing your two code snippets in the above article, how would I go about getting the chart to update ‘on open’? I know where I would place the code…but the data is being placed in the worksheet via export…so it’s essentially closed when changes to the sheet occurs.

    If I simply place the following code into the Workbook_Open () code area….

    If Not Intersect(Target, Worksheets(“ClaimTypeOpen”).Range(“ChtSourceData”)) Is Nothing Then
    Worksheets(“ClaimTypeOpen”).ChartObjects(1).Chart.SetSourceData _
    Source:=Workbooks(“InjuryIllness_Template.xlsm”).Worksheets(“ClaimTypeOpen”).Range(“ChtSourceData”), _
    PlotBy:=xlColumns
    End If

    With Worksheets(“ClaimTypeOpen”)
    .ChartObjects(1).Chart.SetSourceData _
    Source:=Workbooks(“InjuryIllness_Template.xlsm”).Worksheets(“ClaimTypeOpen”).Range(“ChtSourceData”), _
    PlotBy:=xlColumns
    End With

    ….of course this does not work. I’d like the chart to update on open, in addition to updating when a change occurs on the active worksheet.

    Thanks for any help

  79. Doesn’t this work:

    Sub Workbook_Open()
      Worksheets("ClaimTypeOpen").ChartObjects(1).Chart.SetSourceData _
      Source:=Worksheets("ClaimTypeOpen").Range("ChtSourceData"), _
      PlotBy:=xlColumns
    End Sub
  80. OMG…feel like a noob now – yes that works thanks!!

    I do have other questions related to this type of dynamic chart updating – should I post them here or can you email me directly (of course any solutions can be posted by you).

  81. If they are related to this post, ask them here. If not, follow the guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.

  82. Hey Jon,

    First off I want to thank you for such a great website and this really awesome bit of code. I’m a total noob so I’m sorry for such a simple question. Also I apologize if this was asked in the comment section previously, I read them all and didn’t see it (or understand a lot of them!).

    My problem is that in my workbook the headers (Series A, Series B, etc…) and the first column (Cat 1, Cat 2, etc…) will be controlled by a formula. For simplicity sake lets say =if(A1=”X”,”Series A”,””). When I do this for the series it works great if all of the headers are supposed to show, but when the formula returns a blank the chart legend will still contain a colored line for that particular Series. If you need a clarification let me know!

  83. The data is still in the chart, so it still appears in the legend.
    I suggest you label the plotted data directly, instead of using a legend. This way, if the data isn’t plotted, there’s no label.
    See http://peltiertech.com/label-last-point-for-excel-2007/

  84. Hey Jon,

    Thanks for the super fast response and link to another really cool macro. However, this doesn’t take care of my problem. The problem I have is that all of the headers, (both CAT and Series) are formulas that will extend quite a bit. For Cat they will extend up to 1000 places and 30 places for Series. This results in a chart that looks awfully compressed. Hope this helps clarify and thanks for the help.

    -Ethan

  85. You have the code in the workbook to change the size of the data range, but you’re not defining the data range precisely. Change this:
    =OFFSET(Sheet1!$C$3,0,0,COUNTA(Sheet1!$C:$C)+1,COUNTA(Sheet1!$3:$3)+1)
    to this:
    =OFFSET(Sheet1!$C$3,0,0,ROWS(C:C)-COUNTIF(C:C,””)+1,COLUMNS(3:3)-COUNTIF(3:3,””)+1)
    Any blank cells or formulas that return “” are subtracted from the number of cells in the row or column, and the result is the number of labels in the row or column.

  86. Wow,

    Thanks a million. It looked like I actually had to lock a few of the references in the equation that you sent, but other then that it works like a charm. This is the updated formula if anyone is curious.

    =OFFSET(Sheet1!$C$3,0,0,ROWS($C:$C)-COUNTIF($C:$C,””)+1,COLUMNS($3:$3)-COUNTIF($3:$3,””)+1)

  87. Hi Jon,

    I would like to thank you for all the tips and techniques you’ve been sharing. They are all very helpful. I have one question, though. I want to create just 1 one graph with a drop-down list, and when I select from the list, the graph also updates. The catch is, every item on the list refers to a different table. How can we do this?

    Many thanks, Jon!

  88. Jon, thank your insights & for hosting this site. I to suggest to April that she could assign a named range to each table that she wants to use as a input to the graph. (for example TableA, TableB, TableC, etc…) Next, choose cell ($A$1) for the drop-down list to control (“cell link” within Format Control for the dropdown list.) Then, create a named range GraphData and have it reference as follows
    =choose($A$1,ThisSheetName!TableA,ThisSheetName!TableB,ThisSheetName!TableC)
    For your graph, in “select data”, put in
    =’ThisFileName’!GraphData
    That will populate your graph with whichever named data range choice coincides with the drop-down list selection.
    -Mike

  89. Hi Jon,

    This site has been very helpful to me in the last week or so, so thanks for that. I’m really struggling with something though. I’ve got two different sheets in my workbook that each have their own chart. Each of those sheets pull their own data from a separate sheet in the workbook, which is linked to an outside data source. I am trying to get my charts to update automatically, but I can’t seem to figure out how to make both do so independently. I have used your short module code on its own and the private sub worksheet change on each of the sheets containing my data, but for some reason I keep getting an error message. Advice?

  90. Hi Mike,

    Thanks for your advice. Actually, my graph was quite complicated (at least, to me) because I wanted it to be dynamic and interactive at the same time (well, I’m not so sure if these are the right terms ). The ranges are inconsistent so I had to use the suggestions indicated in the previous comments, like the OFFSET and INDEX functions, to define the range. Then, to select a range for the graph, I used the ActiveX combo box, and created some codes using the IF…ELSEIF… function. It worked for me, but one thing I didn’t like was that I had 53 different ranges, so I had to define at least 53 names for the ranges, then 53 if-elseif statements on the VBA…double yikes! As of now, I’ve created only 2 if-elseif statements just to test the code and the defined ranges. I’m afraid the looooong code might slow down the execution of the commands..

    I really love this forum because this is where I got all the ideas for my graph!
    To Jon and everybody who contributes, thanks a lot!

  91. Hi Mark,
    I think, we sort of have the same problem. My main source data is located in Sheet1, but my graphs should be in Sheet2. Since I’m not really good with the VBA codes, I just decided to create a workaround. I created a duplicate source data in the same sheet as my graphs. The dummy source data are just linked to the cells in the Sheet1, where the main source data are located. It was somewhat tedious, and it adds to the file size, but it worked for me. But if there are any other solutions to this, I would really give them a try.

  92. Mark –
    You don’t say what the error is.
    Perhaps a Worksheet_Calculate or Workbook_SheetCalculate event might work. I’ve used a dummy formula that returned an average of a column of imported data to trigger the recalculation.

  93. Hi

    I am trying to get the code working to automate the update of the chart. The dynamic range works perfectly, but (for some reason) the vba code never updates the range.

    I have multiple charts in the worksheet, so am assuming i need to add the name of the specific chart i want to auto update into the vba code. Thing is… i don’t know how! Can anyone help?

  94. Simon –
    You mean the code doesn’t update the data range of the chart? Do you have multiple charts?
    Note that the code processed
    .ChartObjects(1).
    which is the first chart in the worksheet, not the first one created, but the lowest in the stacking order (behind the rest).
    You could modify the code so it worked on the active chart:

    Sub UpdateActiveChartSourceData()
      ActiveChart.SetSourceData _
            Source:=ActiveSheet.Range("ChtSourceData"), _
            PlotBy:=xlColumns
      End With
    End Sub

    If you know the name of the chart, you could replace the index of the ChartObject with the chart’s name:

    Sub UpdateChartSourceData()
      With ActiveSheet
        .ChartObjects("MyChartName").Chart.SetSourceData _
            Source:=.Range("ChtSourceData"), _
            PlotBy:=xlColumns
      End With
    End Sub
  95. Hi Jon,
    Thank you for this great tutorial and the many others I have used to perfect my data. I am coming from the healthcare industry, specifically in clinical research. My interest in this tutorial is partially due to the need to add/remove data from my table and auto-update charts, but I have another need. My data set is semi-large (500-1500 rows), with each row of data representing a patient and an “event” or study visit. In this table there are several columns of study test results (numeric values), disease type, number of gene copies. What I am trying to do is graph some of the test results (Y axis) with the patient’s age in months (x-axis). I need to graph the data in several different ways. One group of charts would be this data graphed by disease type, another group would be graphed by # of gene copies, etc. I find that I have to copy and paste the table many times in several sheets, because when I sort the data naturally it messes up the charts because the data was referencing a range that obviously changes when I sort.
    So what I want is to be able to do all these chart from a SINGLE table, where I can sort the data, take data out, add data, all without having to manually change the ranges of the data in charts or update the changed/ extra/ removed data in all sheets of copied tables. Make sense? Very tedious and time-consuming. I like the idea of named ranges and auto-updating charts, but am struggling to figure out how to apply this to my data. Any thoughts, tips, instructions would be much appreciated, thank you!

    The sample worksheet image I linked below obviously contains fake names, DOB, etc. and only a small fraction of the data and many graphs I have to create, but should give you an idea

  96. Austin –
    Sounds like a lot of work, but best practice for making a bunch of charts is to have a separate staging area containing the data for that chart, conditioned for how the chart needs to display it.
    This means putting all of your data into a Table (the capital-T data repository on which Excel bestows special formatting and behavior), and either using blocks of formulas or pivot tables to extract the particular data. Pivot tables can be sorted independently of the original data range, and you can use them to cross-tab your data.
    You can make pivot charts from the pivot tables, or regular charts. Each has benefits and drawbacks. Basing defined names on the pivot table will help the chart adjust to changing pivot table sizes.

  97. hi jon.
    I have been trying to paste data from excel to powerpoint using vba.

    The problem that i am facing is random i.e sometimes it works and sometimes it doesnt and it says
    “Clipboard is empty or contains data which cannot be pasted here.”

    It would be of great help if you reply to this question as i am trying to solve it for the last 3 days but have no solid solution.
    please reply ASAP.
    Thanks,
    Prateek Kothari

  98. Prateek –
    That’s yet another off-topic question. I cannot spend time with questions that do not relate to the topic.
    Follow instructions in Getting Answers For Your Excel Questions to ensure a prompt response.

  99. Hi Jon,
    Very well done!
    I really appreciate the help, and have found this very useful. I have one more challenge I’d like to add to this. In the chart I am plotting, both the X and Y data change, so instead of having all series referenced to Column B (cat 1 through 7), each one of my series takes up two columns. Therefore (as an example) the first data set in my chart would be comprised of columns B and C, then the next data set would be comprised of Columns D and E etc… If you can think of a way to plot this data dynamically, I’d greatly appreciate it.

    Additional Notes: I arbitrarily added the legend name above the second column of each data set (though I could just as easily have added it above the first column if that’s easier to program for the legend). Also the number of points within each of my data sets is arbitrary, so perhaps I could simply include a large number of rows (about 10,000) to ensure all data is included in each set.

    Thanks,
    Josh

  100. Josh –

    For your case, using SetSourceData will not work, because the chart source data is not a simple rectangular range with the top and left edges used for series names and X values. Instead, you will have to construct each series formula and apply it to the chart.

    You can define the range ChtSourceData in the same way, but then divide the range into two column wide segments, etc. Something like this (untested):

    Dim nSeries As Long, iSeries As Long, iPoints As Long
    Dim rData As Range, sFmla As String, cht As Chart
    Set rData = ActiveSheet.Range("ChtSourceData")
    Set cht = ActiveSheet.ChartObjects(1).Chart
    nSeries = rData.Columns.Count / 2
    iPoints = rData.Rows.Count - 1
    If cht.SeriesCollection.Count < nSeries Then
        ' add series until there are enough
        For iSeries = cht.SeriesCollection.Count + 1 To nSeries
            cht.SeriesCollection.NewSeries
        Next
    ElseIf cht.SeriesCollection.Count > nSeries Then
        ' remove excess series
        For iSeries = cht.SeriesCollection.Count To nSeries + 1 Step - 1
            cht.SeriesCollection(iSeries).Delete
        Next
    End If
    For iSeries = 1 To nSeries
        sFmla = "=series("
        sFmla = sFmla & rData.Cells(1, nSeries * 2).Address(, , , True) & ","
        sFmla = sFmla & rData.Cells(2, nSeries * 2 - 1).Resize(nPoints) _
            .Address(, , , True) & ","
        sFmla = sFmla & rData.Cells(2, nSeries * 2).Resize(nPoints) _
            .Address(, , , True) & ","
        sFmla = sFmla & iSeries & ")"
        cht.SeriesCollection(iSeries).Formula = sFmla
    Next
  101. Hello to all,
    First thanks for sharing the information and have found this very useful.
    I have question regarding the “Dynamic Chart Source Data”
    I use this code
    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
    But when i Delete the information the chart update but the X axis sty as before but without information.
    How do i add refresh for the information the all chart will update according to the new data?
    I try to add this code”ActiveChart.Refresh ” but i got error.
    Thanks
    Amir

  102. Hello to all,
    First thanks for the information and the code that present, it’s very very useful.
    I have question regarding the “Dynamic Chart Source Data”
    I use the first one information
    Name: ChtSourceData
    Refers To: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)
    and the VBA code
    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

    But when i Delete information the chart was not updat, the information from the chat was Delete but the X Axis not update.
    Can you help?

    Thanks
    Amir

  103. Really helpful page you have here Jon; however, I’m having trouble making this work without run time errors.
    Essentially I have to changed the plot to rows (instead of columns), as the data on Report Data sheet could have any number of rows (Data Series) in column B. I used your recommendation from your comment above dated Thursday, December 9, 2010 at 3:33 pm.

    Also, I changed the code (based on your comments in the comments section dated Monday, January 31, 2011 at 6:54 pm) for when the chart is on a different sheet to the source data.

    My runtime error is 1004 – Method ‘ChartObjects’ of Object’_Worksheet failed

    My VBA code is:

    1. Worksheet Change:

    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:=xlRows
    End If
    End Sub

    2. General – UpdateChartSourceData (Module)

    Sub UpdateChartSourceData()
    Charts(“Resource”).SetSourceData _
    Source:=.Range(“ChtSourceData”), _
    PlotBy:=xlRows
    End Sub

  104. Andy –

    The Worksheet_Change event procedure has to be on the code module for the sheet with the data. “Me” in the code refers to the sheet corresponding to this code module, so Me.Range(“ChtSourceData”) is valid. However, the chart is on another sheet, so Me.ChartObjects(1) is not right. You need to use Worksheets("Sheet with my chart").ChartObjects(1) instead.

  105. Ah-ha! Thanks Jon. That now works. One issue now is that as I manually enter in a new row of data (new source), it adds in the plotted area to the chart, but for some reason I now have 55 series in my legend (but only have 3 rows/series of data in the source sheet).

    If I look at the name manager and click in the ‘Refers To’ formula box it highlights 55 rows in the data sheet (even though there are only 3 rows of data).

    The name parameters are:
    Name: ChtSourceData
    Refers To: =OFFSET(‘Report Data’!$B$1,0,0,COUNTA(‘Report Data’!$B:$B)+1,COUNTA(‘Report Data’!$1:$1)+1)

    Data on the Report Data sheet starts in cell B1 with dates across columns (starting in C1) and series names in rows (starting in B2).

    Thanks again for your help.

  106. Hello,
    I am having a similar issue as Simon (Thursday, September 12, 2013 at 2:14 am). I am trying to get the code working to automate the update of the chart. The dynamic range works perfectly, but (for some reason) the vba code never updates the range. I have multiple charts on my sheet. I tried the below two suggestions that Jon recommended but I get a run-time error ‘1004’. Any help would be greatly appreciated. Thanks.

    You could modify the code so it worked on the active chart:
    Sub UpdateActiveChartSourceData()
    ActiveChart.SetSourceData _
    Source:=ActiveSheet.Range(“ChtSourceData”), _
    PlotBy:=xlColumns
    End With
    End Sub

    If you know the name of the chart, you could replace the index of the ChartObject with the chart’s name:
    Sub UpdateChartSourceData()
    With ActiveSheet
    .ChartObjects(“MyChartName”).Chart.SetSourceData _
    Source:=.Range(“ChtSourceData”), _
    PlotBy:=xlColumns
    End With
    End Sub

  107. What is the error description? Run time error 1004 could mean a lot of things.

  108. Hi Jon,

    Thanks for this code tip, I go to your blog often to check out new things to learn. I’ve been using this code a lot and modified it. It works like a charm. Now I’m doing another change and need to run the code on a hidden chart (visible=false). From what I’ve tried SetSourceData needs an active chart, right? What would be a neat solution for this?

    Regards,
    Mans

  109. No,a chart does not need to be active, nor even be in the active sheet, to use SetSourceData:

    Worksheets(“Sheet1”).ChartObjects(1).Chart.SetSoutceData Source:=SomeRange

  110. G’day John,

    Hoping for some help here – you’re topic of discussion here has been very helpful so far.

    I’ve got two sheets – Catchment Data and Catchment Report.
    Catchment Data has VBA programming which inserts data from a .csv file into set out XY format ready for a chart to represent it.

    Catchment Report is a page of pre-set graphs which I want to link to Catchment Data.

    Example:

    Using first graph on Catchment Report which is named ChartPopulation and defined range on Catchment Data called DataPopulation.

    I want VBA code in a UserForm (or you could suggest alternative) to on each run of the program – relink the ChartPopulation to DataPopulation.

    I’ve been playing around with both codes below to get it to work but keep getting an out of range error:

    Code 1:
    Worksheets(“Catchment Report”).ChartObjects(“ChartPopulation”).Chart.SetSourceData _
    Source:=Sheets(“CatchmentData”).Range(“DataPopulation”), _
    PlotBy:=xlRows

    Code 2
    ActiveSheet.ChartObjects(“ChartPopulation”).SetSourceData _
    Source:=ThisWorkbook.Sheets(“Catchment Data”).Range(“DataPo[pulation”)

    I need the graphs to reset each time as the Catchment Data and Graph sheets are templates which are copied each time the program runs. As the user will likely do three or four Catchment Reports in the one spreadsheet.

    Appreciate your help.

    Chris

  111. The error sounds like the worksheets aren’t found by name. When you insert sheets from a template, the sheets often get names like “Catchment Report (2)” and “CatchmentData (2)”. Maybe you could create a new workbook from the template, populate the data and the chart(s), then move these sheets into the main workbook.

    A few notes:
    Use Worksheets("name") in place of Sheets("name"), because all good programmers are obsessive-compulsive.
    Make sure the names are spelled correctly.
    In Code 2, ChartObjects(“ChartPopulation”).SetSourceData will fail. It has to be ChartObjects(“ChartPopulation”).Chart.SetSourceData.
    Also, “DataPo[pulation” is probably wrong.
    Finally, note that blog comments use curly quotes, and your code needs straight quotes.

  112. What can I do if I clear a column in the center of my table area? For example lets say you have Series A, Series B, Series C, and Series D. If for some reason I delete Series C my graph no longer recognizes the Series D data. Because the blank column now exists in the middle of the range the formula is cut short and doesn’t continue to on to recognize the floating D Series.

  113. Anthony –

    Most of the Name-based range definitions will fail if the range has blanks in it. The simple formulas, using functions like OFFSET, INDEX, COUNT, and COUNTA cannot work with a discontiguous range.

    I can think of ways that the VBA routine could work with a range, using a column if it contains chartable values, skipping it otherwise. But these would not be simple.

  114. Is it possible to adjust the code to update all charts across all sheets? I have 10 charts on 10 sheets, each with its own source data and different number of columns listed in that exact sheet. What I would like to accomplish to have each chart reference own data and update as more columns are added
    Worksheets(“summary”).ChartObjects(“Trend”).Chart.SetSourceData _
    Source:=Worksheets(“Summary”).Range(“ChtSourceData”), _
    PlotBy:=xlColumns

  115. Anastasia –

    One way to do this is to have a uniquely named Names for each sheet, say, ChtSourceData1 on Sheet1, ChtSourceData1 on Sheet2, etc. Then code like this will work:

    For i = 1 to 10
      Worksheets("Sheet" & i).ChartObjects(1).Chart.SetSourceData _
          Source:=Worksheets("Sheet" & i).Range("ChtSourceData" & i), _
          PlotBy:=xlColumns
    Next

    If each chart’s defined source data range is completely isolated from other data (surrounded by empty rows and columns), you don’t even need the Names. You can simply parse the series formula of one of the series to get its Y values, and use the region of data containing these Y values. For the active chart it looks like this:

    Sub ExtendRangeActiveChart()
      Dim sFmla As String, vFmla As Variant
      Dim sY As String, rY As Range, rChart As Range
      
      With ActiveChart
        sFmla = .SeriesCollection(1).Formula
        vFmla = Split(sFmla, ",")
        sY = vFmla(3 + LBound(vFmla) - 1)
        Set rY = Range(sY)
        Set rChart = rY.CurrentRegion
        .SetSourceData Source:=rChart, PlotBy:=xlColumns
      End With
    End Sub

    So for all charts on all sheets in the active workbook:

    Sub ExtendRangeActiveChart()
      Dim ws As Worksheet, chob As ChartObject
      Dim sFmla As String, vFmla As Variant
      Dim sY As String, rY As Range, rChart As Range
      
      For Each ws In ActiveWorkbook.Worksheets
        For Each chob In ws.ChartObjects
          With chob.Chart
            sFmla = .SeriesCollection(1).Formula
            vFmla = Split(sFmla, ",")
            sY = vFmla(3 + LBound(vFmla) - 1)
            Set rY = Range(sY)
            Set rChart = rY.CurrentRegion
            .SetSourceData Source:=rChart, PlotBy:=xlColumns
          End With
        Next
      Next
    End Sub

    But there’s an easier way, which doesn’t require code, and which I didn’t really know about when I originally wrote this post so long ago. If each chart uses a simple rectangular block of data, you can make that block of data into a Table by selecting it and pressing Ctrl+T. This formats the range, as shown below.

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

    Using Excel Tables is so much better, that I’ve modified this tutorial to include it.

  116. Thank you, Jon for your prompt response! The first code you wrote would work for me since my data is not set up for a table.. except each sheet is named after a product so i can’t modify the sheet name. is there a way to update based on the unique name of each sheet? thank you for your help!

    For i = 1 to 10
    Worksheets(“Sheet” & i).ChartObjects(1).Chart.SetSourceData _
    Source:=Worksheets(“Sheet” & i).Range(“ChtSourceData” & i), _
    PlotBy:=xlColumns
    Next

  117. Anastasia –

    If the names are defined with the same name (“ChtSouirceData”) and scoped to the worksheet they are on and not scoped to the workbook, you could try this:

    For i = 1 to 10
      Worksheets(ii).ChartObjects(1).Chart.SetSourceData _
          Source:=Worksheets(i).Range(“ChtSourceData”), _
          PlotBy:=xlColumns
    Next

    But the point of the Tables approach is that you don’t need code, and the charts are dynamic without you having to do anything when the data changes. It’s easy to create the Tables, and if the data is set up for Names as defined here, they are also well suited for Tables.

Trackbacks

  1. […] and data are on different sheets. You are still going to have quite a bit of work to do – see if Dynamic Chart Source Data | Peltier Tech Blog | Excel Charts gives you some ideas (the link is from the 'VBA' section of the index given in the earlier […]

  2. […] document.write(''); Originally Posted by Levon The data source is split into groups (A,B,C) of countries based on a certain criteria. The groups can expand or shrink (Austria can move to group B from group A). My range covers the countries in group A. Maybe this link will help more than I can: Dynamic Chart Source Data – Peltier Tech Blog […]

  3. […] }); Here's a tutorial on my blog that may help: Dynamic Chart Source Data. The most likely cause of your problem is that you have not referenced the Name with its parent […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0