On my web site and blog, I have over a dozen pages that talk about dynamic charts. A partial list of these articles is provided at the end of this post.
I was asked to expand on one of my examples, specifically to show a dynamic chart with multiple dynamic series.
I made up the following data set:
I created the following chart, and when the first series was selected, the SERIES formula showed the cell addresses of the ranges defining its data.
I defined the following dynamic names:
The OFFSET formula defining XLabels basically says, starting with cell A1, move down one cell and right zero cells, and give us the range which is as tall as the number of labels, given by the COUNTA function, and as wide as 1 cell. The other OFFSET formulas merely say, give us the range 1, 2, 3, or 4 columns to the right of XLabels. This is a shortcut that assures all dynamic ranges are the same size.
To link the series to the dynamic ranges, all that is required is to replace the static cell references in the SERIES formulas with the names defined above. Here is the first SERIES formula when the cell addresses have been replaced by the names, before pressing Enter.
Since the names were defined with the workbook as their scope, pressing Enter converted the references to workbook references, that is, referenced to MultipleDynamicChart.xls instead of Sheet1.
When data is added to the range…
… the SERIES formula is not changed, but the ranges defined by the dynamic names does change, so the chart updates.
The workbook with this example can be downloaded as a zip file, MultipleDynamicChart.zip.
Here is a partial list of my articles on dynamic charts.
- On the PTS Blog
- Dynamic Charts
- Dynamic Chart using Pivot Table and Range Names
- Dynamic Chart using Pivot Table and VBA
- Dynamic Ranges to Find and Plot Desired Columns
- Split Data Range into Multiple Chart Series without VBA
- VBA to Split Data Range into Multiple Chart Series
- Dynamic Chart Source Data (VBA)
- Display One Chart Dynamically and Interactively
- On the Peltier Tech web site
Peter H says
Jon
I believe there is an easier method to construct a dynamic chart than using the Offset Formula. ( I have mentioned it a couple of times on the Pointy Haired Dilbert Site). It works in 07 but not sure about earlier versions)
Using your example:-
Simply highlight the data (A1:E7), Insert Table then Insert again and select Chart type (in this case Line) and it is done.
To add data simply enter it in the next line (Row 8).
More series can also be added simply by adding the data to column F and the chart will insert it automatically.
Are there any disadvantages to this method?
Peter
Andy Holaday says
Hi Jon,
Charts with dynamic ranges are cool.
In a related vein, I’m wondering if you have done much in the way of interactive charts? Using form controls, I have put together just a couple of these. Most recently I released one that uses checkboxes that control hiding/showing different series. It is a big hit with the users.
Thanks as always for your great tips.
Jon Peltier says
Hi Peter –
This technique works in 2007 as you describe, and also in 2003, although the object you insert is called a List, not a Table. If your data is arranged in this kind of flat list, then there is no disadvantage in using a List or Table for dynamic data.
Of course there are more complicated situations, for example if you want to plot between two dates which are not endpoints of a column in the list, or some cases which are arranged by row instead of by column.
Jon Peltier says
Andy –
I’ve done lots of interactive charts. I have listed some examples in Dynamic and Interactive Charts (overview). You can find some in the tag index for “interactive”. And of course Google can help you find some (http://www.google.com/search?q=site%3Apeltiertech.com+interactive+chart).
jeff weir says
Hi Jon. Another great tutorial. The way you use a simplified offset to quickly relate the ‘slave series’ (i.e the y series) to the master series (the x series) is a nifty shortcut.
It’s worth pointing out though that if you delete columns between the master series and the slave series at some point in the future, then your offsets will all need to be adjusted to compensate. This might be the case if you’re working on a dashboard, and are still finalising what data you will plot.
In this case, it’s probably safer to use the same method as you used for the x series when defining each of the y series – that is, base the offset reference from the specific column in which the data resides. That way, no matter where the column gets moved around the worksheet, the function will still point to the data you want.
Jon Peltier says
Jeff –
In this case, I’d probably name the label cell of each column (A1, B1, etc.), and then use COLUMN(Alpha)-COLUMN(Labels) as my column offset. Then my series names would also be a name rather than a hard coded address.
jeff weir says
That is such a simple, effective work around that I can’t believe I didn’t think of it! After all, I use the ROWS function all the time in formulas for this very reason…why didn’t I think of using the COLUMNS function!
It’s funny…sometimes when we use Excel’s functions I guess we can concentrate too much on fufilling the parameters of those functions, rather than thinking about the problem.
How does the saying go…you learn a new trick from an old dog every day?
Thanks Jon
TV says
If I understand your formulas, this means that you can’t put additional data in column A below the data table? The COUNTA function on column A won’t work like a CTRL+SHIFT+DOWN selection would, which would ignore any non-contiguous regions. If you insert text below the table in column A, it will count that number of rows and append it to the end of the series, right?
Jon Peltier says
TV – That’s correct. You either have to keep the worksheet simple (not a bad policy) or modify the formula. Or you could change the definition of XLabels to:
=OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,ISBLANK(Sheet1!$A$2:$A$1000),0)-1,1)
The Match statement finds the first blank in the range A2:A1000, which I have made arbitrarily large, and ignores any nonblank cells below the first blank.
Peter H says
TV – Using the technique of using a Data Table (or List in 03) will overcome this problem as long as you leave a blank row and column around the Table.
You can have several Data Tables on the one worksheet to generate several different Charts
Gene says
First, thanks for all your help…I bought you a cup of coffee this morning…I suspect I will be buying more…
Is there anyway to avoid putting the ‘workbookname.xls’ in the charts series definitions. I am creating templates for my customers to use, and they can rename them to anything…
Thanks again…
Gene says
NEVERMIND !!!
I posted my question to soon…never thought to try a “Save As” to see what would happen…even renamed the workbook, and excel still managed to change the workbook name reference…
Smarter than I am I guess…
Thanks again
Jon Peltier says
Gene –
Try It is one of the best ways to learn how to do something. If you screw up, there’s always Undo, or closing the workbook without saving. The difference between a blog reader and a client is that I’ll tell a blog reader to Try It, while I will Try It myself for a client.
Dan Pitcock says
Using your Panel Chart tutorials to build stock charts. Awesome tutorial! Using error bar process for variable height panels. After using the paste special process, when I double click the series and and try to align to primary or secondary axis, the line won’t go exactly vertical in line with the axis; therefore, the line won’t extend completely across the chart. It does above first panel but then is “short” for each succeeding panel. Any ideas what I’m doing wrong?
savithri.v says
By far, I feel, the most comprehensive resource on dynamic charting is your article in Techtrax ezine. The approach of using column function as reference to the specific column in which data resides (mentioned in the comment above) has been put into use by you there albeit in a different context
The technique of using min & max values to ensure that end date is always > start date in the selected range would save many an embarrassment from nitpickers.
Was psychology ancillary to metallurgy in MIT?
Jon Peltier says
Savithri –
I like to think that I’ve actually learned from some of my mistakes, and from those of others. The safeguard you mention is an example of something that has burned me in the past. It’s also an example of the maxim that it’s hard to program something that is easy to use.
Josh says
Hi,
Thanks for the very informative site. Do you know of any problems using this technique using Office ’08 on Mac?
I have downloaded your sample sets and they all look and behave well. I have tried taking these concepts to a test version of my data, but get errors. if I try to use the =SERIES() function I get a “this is not a valid function” error. if I try to edit the chart and use =Xvalues/Yvalues, I can get the X values to work, but the Y values do not and the X legend shows up as test1!Xvalues.
Im sure it is something very simple that I am missing. any suggestions?
thanks again,
JO
Jon Peltier says
Josh –
I don’t know anything about Mac Excel. I do know Excel 2008 is the Mac version of Excel 2007. Excel 2007 had some major issues with using names as chart data ranges, and the issues didn’t get mostly fixed until SP2.
To use the SERIES function, you have to select either a series, the plot area, or the chart area. It won’t work in a cell or in the Define Names dialog.
Paul Beck says
Hi
very useful example – but I have 1 thing I can’t seem to do. I want to reverse the categories – ie have alpha beta etc displayed on the x axis. Column A is still the column I want to dynamically increase. Hope this makes sense.
thanks
Paul
Jon Peltier says
Paul –
If you plotted the data by row and you had expanding rows (i.e., adding more columns), you could adjust the OFFSET formula to count columns and stretch the named range widthwise.
You can’t use this technique to add series to a chart when a range increases in size. But you can use the VBA technique in Dynamic Chart Source Data to do this.
Alex says
Jon,
Is there a way to make the charts dynamic without editing each individual series? I have to reproduce 18 charts each with a minimum of 7 series each. As you can see, this would a monstrous undertaking.
Thank you,
Alex
Schatzii says
Hi, Very Helpful forum,
could you please also tell us how to dynamically add series to the chart without using macros?
Jon Peltier says
Schatzii –
That’s a problem. Using named formulas (named ranges) you can dynamically change the number of points in a series. To dynamically change the number of series in a chart, however, cannot be done using formulas. You need to use VBA, perhaps using an approach like that in Dynamic Chart Source Data.
monica says
hi,
Im breaking my brains trying to know how to do a dinamic chart that you can sorted by date and source>
One column for date, another one for location, and the next 3 for critical characteristics.
so, i want to sorted by date and location. and also I will add more values every month, for the same characteristics.
If I filter the data, the chart wont display it. how can I do it?
Thank u!
Jon Peltier says
Monica –
In Easy Dynamic Charts Using Lists or Tables I show an easy way to make dynamic charts if all you need the chart to do is to expand or contract when data is added or removed. The data is in a Table, and the chart is based on this Table. As the table expands or contract, the chart adjusts accordingly. And if you sort the table, the chart’s data is also sorted.
monica says
Hi Again Jon,
Well the thing is I have a lot of information, so when I tried to sorted by different criteria (date, Location 1 and Location 1.1) the chart shrinks and the axis dont change to the new sort (If I have 1000 dates but I sorted to two, I dont want to see the 1000 spaces in axis label chart)., I dont know if I explain myself but If you want I can send you the document so you can see it and give me some advices.
I appreciatte your help, I have two nights I cant sleep well because of this.
Jon Peltier says
Monica –
Did you put your data into a Table? When you sort, the chart updates accordingly. If you filter out some data, the chart hides it without leaving lots of empty space (hidden rows or columns are not charted by default).
Of course, if the chart has a date axis, and you filter out all but the first and last date, you’ll have all those blank dates in between. Correct this situation by changing to a text axis.
monica says
Hi Jon,
Still not working, dont know why. I have the table, then I use Ctl L and it creates the list, then I created the chart, but as soon as I sorted the chart shrinks, is there an example of this spreadsheet that I may download?
Thanks again.
Kedar says
Many thanks for the above example. This helped in understanding the multidimensional dynamic chart .
Kamran says
You are a genius!
Thank you very much!
Tom East says
One of those times you have been hitting your head on a brick wall for days and then……
I never write comments. Thank you!
anil says
Hello Peltier
Above Example is quite helpful to me
X Labels are Dynamic And lengends are Fixed for the Mentioned Dynamic Chart
But I Want X Labels to Be Fixed And Legends to be Dynamic…
I Tried to interchange row/column switch..but Chart is not Updated as before
Can You Please Help me on this
Jon Peltier says
Using Names can only make the length of each chart series dynamic. Names cannot make the number of series in a chart dynamic. I talk about this problem in Dynamic Chart Source Data, where I provide a VBA workaround. At the end of that article I also show how you can put your data into a Table, and as that table expands or shrinks, the number of series, and number of points per series, change accordingly.