Dynamic Charts
by Jon Peltier
Wednesday, May 14th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Oh no, not another article about Dynamic Charts! There must be dozens of articles on the interweb about dynamic charts, some of them even useful. I didn’t even write the first Dynamic Charts article on my web site; that honor goes to Debra Dalgleish’s Dynamic Column Chart. Debra enjoyed writing that tutorial so much, she went on to build an extensive library of tips on her Contextures web site, then go on to author several books on Pivot Tables:
Excel Pivot Tables Recipe Book: A Problem-Solution Approach
Excel 2007 PivotTables Recipes: A Problem-Solution Approach
Beginning PivotTables in Excel 2007: From Novice to Professional
Debra’s first example showed how to make a dynamic chart that added more data to the chart as it was added to the worksheet. You can define names to work with any cut of the data you want, if you’re clever enough writing formulas. But a chart that automatically expands with its data range is a good topic, so I’ll repeat it using the data below:
The data is a simple table of page views by month for my web site. The categories and values are arranged in columns; Excel will plot either by rows or by columns, but arranging your data by columns is probably a good habit to get into. Databases return data with this orientation, and Excel has longer columns and shorter rows. A chart series can contain up to 32,000 points, but if your series are aligned in rows, you are limited by the 256 columns in a worksheet (except for Excel 2007, of course). This example uses data in columns; if your data is aligned in rows, follow along, and there will be a special section at the bottom to help you apply the technique to your data.
Here is a simple line chart of this data:
Defining Names
To make this chart dynamic, for all modern versions of Excel (i.e., 97 and later), you need to define some dynamic ranges that describe the dynamic data. Excel calls these Names.
On the Insert menu, choose Names > Define, or on the Excel 2007 Formulas tab, choose Define Name, or in either version press CTRL+F3. The Excel 97-2003 Define Names dialog is shown below; the Excel 2007 version of the dialog is much the same.
I have entered StaticCats (for “static categories”) for the Name being defined. To populate the Refers To box you can enter a constant, select a range with the mouse, or construct a formula. I’ve used an OFFSET formula, because that is a handy way to define a dynamic range. The formula is
=OFFSET($A$1,1,1,4,1)
In the Offset function, there are up to five arguments: the range which defines the anchor for the range, the row offset from the anchor to the defined range, the column offset from the anchor to the defined range, the height in rows of the defined range, and the width in columns of the defined range. When you have added the name, you can click in the Refers To box, and the defined range will be highlighted by the “marching ants” border, as shown above. The marching antss are highlighting the range B2:B4, which starts one row down and one column right of the reference $A$1, and which is four rows tall and one column wide.
That’s how Offset works, but that example isn’t dynamic. We’ll use the following Refers To formula to define a dynamic name, VertValues:
=OFFSET($B$1,1,0,COUNT($B:$B),1)
Instead of hard-coding a value for the number of rows in the range, I use COUNT($B:$B), which counts the numerical values in column B. Note: to avoid counting errors, make sure there are no blank rows in the data range, and no stray numerical data in column B below this table, or enter a limited range within which to count, as in COUNT($B$1:$B$25).
Let’s define a dynamic name for category labels, VertCats. There are two ways to do this. One way is to write a parallel OFFSET function that counts appropriate entries in the categories column:
=OFFSET($A$1,1,0,COUNTA($A:$A),1)
Here I’ve used COUNTA which counts all non-blank cells. There was no header label in cell A1. If there had been, I would have to use COUNTA($A:$A)-1 for the number of rows.
An easier and more consistent definition for VertCats makes use of the fact that we already know how long VertValues is, and offsets from that range:
=OFFSET(VertValues,0,-1)
The -1 signifies that VertCats is one column left of VertValues. I can omit the length and width arguments, and VertCats will have the same dimensions as VertValues.
Working with Names
The built-in name management tools are not very powerful, though they’ve improved in Excel 2007. If you plan to use names in Excel, do yourself a favor and run, don’t walk, to Jan Karel Pieterse’s web site and download the Name Manager. This flexible and powerful tool makes working with names effortless.
NOTE: Excel 2007 Charts and Names of Dynamic Ranges
One strange detail recently came to my attention. Dynamic names can be used in Excel 2007 charts as well as in Excel 2003 and earlier charts. If you give the dynamic range a name beginning with the string “Chart”, in Excel 2007 you cannot edit the series formula to accept this name, although you can enter the name using the Select Source Data dialog. I’ve always used a shorthand like “cht”, so I didn’t notice it. But a colleague of mine, another Excel MVP, pointed it out, and more recently I saw it mentioned on another blog.
Making the Chart Dynamic
How do we get the names into the chart? I find the easiest way is through the series formula, but you can also use the Series Data dialog.
The chart above has a simple series formula:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$5,Sheet1!$B$2:$B$5,1)
A series formula has four arguments (five for a bubble chart), which are series name, category labels or X values, Y values, and plot order. In most cases, the X and Y values are range addresses. These addresses can be replaced by the appropriate names:
=SERIES(Sheet1!$B$1,Sheet1!VertCats,Sheet1!VertValues,1)
The chart is unchanged, because the names happened to refer to the cell addresses. One interesting note, since the names were defined with a worksheet scope, is that the series formula will change to show the workbook name, not the worksheet name, in the reference to the name:
=SERIES(Sheet1!$B$1,Book1.xls!VertCats,Book1.xls!VertValues,1)
The difference between workbook-scoped and worksheet-scoped names will be covered in another blog post.
The other way to include the names into the source data is via the Source Data dialog. In Classic Excel, go to Chart menu > Source Data > Series tab; in Excel 2007 go to Chart Tools > Design tab > Select Data; in any version, right click on the chart, and choose Source Data > Series tab or Select Data. Select the series, and you will see the X and Y data ranges in the edit boxes.
Replace the cell references in these addresses by the names you’ve defined. Be sure to include the sheet name, or Excel will not know where to find the range.
When the data for May is added, the names automatically update, and so does the chart. You can see that the highlighted ranges in the sheet have expanded to include May (these colored highlights appear when a chart series is selected).
Series in Rows
“But my data’s in rows! What do I do?” (remember Lisa Lupner and Todd DiLaMuca?)
Since my data’s in months, I’m not going to run out of columns for 20 more years. Suppose my data looks like this:
The names use OFFSET as above, with the row and column treatments reversed:
HorizCats
=OFFSET(Sheet2!$A$1,0,1,1,COUNTA(Sheet2!$1:$1))
HorizValues
=OFFSET(Sheet2!$A$2,0,1,1,COUNT(Sheet2!$2:$2))
Enter these names into the series formula:
=SERIES(Sheet1!$A$2,Sheet1!HorizCats,Sheet1!HorizValues,1)
And the chart works the same as above when May’s stats are added.
Further Information
As I said earlier, there are about three bazillion other pages on dynamic charts, and here is where to find more examples on my web site. The Dynamic Chart Links page contains links to other sites.
Dynamic and Interactive Charts (overview)
Dynamic Column Chart
Another Dynamic Chart
Chart the Last 12 Months
Dynamic Charting By Dates (TechTrax Article)
Charting Dynamic Normalized Ranges
Dynamic Chart Links
Related Posts:
- Dynamic Ranges to Find and Plot Desired Columns
- Interactive Multiple Line Chart
- Dynamic Chart Source Data
- Dynamic Chart with Multiple Series
- Dynamic Chart using Pivot Table and Range Names
- Dynamic Chart using Pivot Table and VBA
- Chart Source Data Highlighting
- VBA to Filter Chart Data Range
- Highlighted Chart Source Data
- Build an Excel Add-In 2 – Enhanced Functionality
Posted: Wednesday, May 14th, 2008 under Dynamic Charts.
Comments: 83
Comments
Comment from Tim
Time: Wednesday, May 14, 2008, 1:44 am
Jon, I’m surprised that you didn’t mention Lists (Tables in Excel 2007). When you create a chart using a List/Table then the chart will automatically extend as you add data to the List/Table. There are a lot of good reasons for using Lists/Tables and this is certainly one of them.
Comment from Jon Peltier
Time: Wednesday, May 14, 2008, 6:58 am
Tim -
The article was getting pretty long, so I left out the part about lists. I should insert a short reference, then write it up later.
In fact, when I was using Excel 2000 for my regular work, I never bothered upgrading to 2002, but the lists in 2003 were the killer feature that convinced me to upgrade to 2003. Their capabilities have been expanded in 2007 tables, but I haven’t yet gotten used to the new reference styles, which are relative in terms of column label even though they seem like they should be absolute.
In any case, lists only work for simple dynamic columnar ranges like the ones in this example, but not for those with varied starting and ending points.
Comment from Debra Dalgleish
Time: Wednesday, May 14, 2008, 12:26 pm
Nice article, and changing the references in the formula bar is a great tip — much quicker than going through the dialog box.
Comment from Jon Peltier
Time: Wednesday, May 14, 2008, 12:35 pm
Thanks, Deb. I’ve used the formula bar for a long time to select and edit chart data. It’s much faster and easier than the dialog, and in fact, I didn’t even start using the dialog until I started writing tutorials. Some people seem afraid of editing the formula.
Comment from Tim
Time: Wednesday, May 14, 2008, 3:48 pm
“In any case, lists only work for simple dynamic columnar ranges like the ones in this example, but not for those with varied starting and ending points.”
Excellent point that I hadn’t considered. If you write up the Lists/Tables thing, make sure to mention that caveat.
Comment from Jon Peltier
Time: Wednesday, May 14, 2008, 4:47 pm
There are even cases in which the algorithms that make sure worksheet formulas keep referring to every row of a list actually break the refers to definitions of names. These are much less common than cases where the lists are a great feature.
Comment from Hitesh
Time: Wednesday, June 25, 2008, 8:59 am
Hi
I would like to make a dynamic graph in flash which lodas all data from xml file but I am not sure where to start?
can any one help me on this.
Thanks
H
Comment from Hitesh
Time: Wednesday, June 25, 2008, 9:00 am
This is the example of what I want to create
http://www.fusioncharts.com/free/GalleryChart.asp?id=13
H
Comment from Jon Peltier
Time: Wednesday, June 25, 2008, 9:12 am
Hitesh -
I know nothing about making Flash charts. Try a Google search on a few relevant keywords, like flash chart xml.
Comment from Jon Peltier
Time: Thursday, June 26, 2008, 1:09 am
Hitesh -
These are not dynamic charts, in the sense that they change as the data changes or as the data range expands and contracts. These are dynamic in the sense that they are animated, that they have movement to capture the viewer’s attention. That’s the purpose of the Flash platform.
I’d suggest you download their free utility and give it a go:
FusionCharts Free
This is not an endorsement of FusionCharts Free, a product I have never used or even heard about before Hitesh’s comment. This is merely a suggestion to Hitesh. I’m sure there are other animated charting technologies, but in general I like to think that well displayed data can stand on its own merits without (pardon the pun) flashy animation.
Comment from JP Gonzalez
Time: Tuesday, September 23, 2008, 8:27 pm
Hi Jon,
I’ve been scouring the net and haven’t found a straight answer yet… Is there a way to create dynamic charts using data from an external workbook? Since OFFSET does not reference external cells, would it all be just as simple as switching over to the INDEX function instead?
Thanks for any help you provide!
Comment from Jon Peltier
Time: Thursday, September 25, 2008, 12:41 am
Offset can reference a range in any open workbook.
Comment from JP Gonzalez
Time: Thursday, September 25, 2008, 11:23 am
I tried and found out that OFFSET wouldn’t work with closed external workbooks. It also seems the same for validation lists as well too (even though the MS Help file says it can…). So if I were to try and create dynamic charts in one workbook but reference the data from a closed external workbook, it just wouldn’t fly. I’m gonna poke around and try some more things, until the, thanks though!
Comment from Oscar
Time: Friday, October 17, 2008, 9:01 am
I have spent some days trying to figure out how to make a chart update automatically in Excel 2008 as the data change. I am not using the same range of cells, but the data in the cells change every time I recalculate. It is a probability simulation, so the function I am using for each cell is =RANDBETWEEN(1,6), and then these data are collected in a frequency table, which is what the chart represents. Everything works fine, except the chart, which will stay unchanged showing the earliest state of the data. I found out that I need to close and open the file again (or save the file, if saved as .xlsx), which makes no sense. I remember when I could hit ctrl-= and see the chart change in real time after each recalculation. Following your advice, I tried using names, to no avail.
Comment from Jon Peltier
Time: Friday, October 17, 2008, 11:35 am
Oscar -
Excel 2008 as in the newest Mac version? I don’t directly know about it, but I have heard that sometimes charts in 2007 do not update to reflect changes in the worksheet data. I also don’t know about this issue, because I don’t use Excel 2007 much (in fact, I avoid it).
Just now I tried to reproduce the problem with about 75 cells of RANDBETWEEN(1,6), and a table that uses COUNTIF to calculate the frequencies. Naturally it worked as it should. Then I tried 10k cells, and it still worked as expected.
If I haven’t followed your repro steps, let me know and I’ll try again.
Comment from Oscar
Time: Friday, October 17, 2008, 12:01 pm
Thanks for the prompt response!
Excel 2008 is the latest version for Mac OS X. I tried COUNTIF and that solved it. I had used FREQUENCY:
For each row (50 total) I had RANDBETWEEN(1,6) in two contigous cells, and the sum of that in a third cell. I created a table for the frequency of the sums (from 2 to 12), using {FREQUENCY(C1:C50, E2:E12)} (as an array formula).
Using COUNTIF(C1:C50,E2) (and E3… up to E12) works perfect. This solves my immediate problem, but I find it disappointing that a rather reasonable approach (using FREQUENCY) won’t work for no apparent reason. It works fine with the table, it just doesn’t work with the chart.
Comment from Jon Peltier
Time: Friday, October 17, 2008, 12:48 pm
Oscar -
I replicated your two-dice model, but I kept the 10k rows I had been using, and I used FREQUENCY to count occurrences of scores of 2-12. The chart always refreshed when the data changed.
Have you applied SP1? This fixed a lot of dumb little issues.
Comment from Oscar
Time: Friday, October 17, 2008, 1:14 pm
I assiduously update Excel, always hoping that it will work better (currently I have version 12.1.3, which was released three days ago), so whatever bugs MS fixed, this one is still in the drawer. From your response, I assume that it is an issue that happens only with the Mac OS version. COUNTIF works, FREQUENCY doesn’t.
This is my whole file, in a picture: http://screencast.com/t/9d63GeFkb2
I also made a short videoclip. At the beginning you can see how the chart matches the frequency table. As I recalculate a few times, the table changes, the chart doesn’t:
http://screencast.com/t/QENWM4kyA
UPDATE! As I was writing this comment, I wanted to post the file on my web site. I was resizing the columns, to make it look nicer, and I noticed that the spreadsheet recalculates AND the chart refreshes! This is really weird. That is why in real life I prefer to use Fathom (http://www.keypress.com/x5656.xml).
Comment from Jon Peltier
Time: Friday, October 17, 2008, 1:32 pm
Oscar -
Excel must have decided that you have suffered enough.
Comment from Oscar
Time: Friday, October 17, 2008, 2:05 pm
No kidding! It has been more than a week since I began trying to solve this. After looking at tens of web pages with mostly useless information (useless for solving this problem), I’m very happy to have found your web site. I have bookmarked it, and I’ll share it with my students and colleagues. I really don’t like Excel, I don’t like the defaults, but I am happy to learn that people like you take these issues seriously and do an outstanding job of helping us, ordinary mortals, to make the best of this tool. What I don’t like about the whole Office suite is the underlying assumption that MS knows best, that users have no idea what’s good for them, so it is best to deprive them of ways to do what they would like to do, and so they set defaults that are, almost always, wrong or ugly or simply inappropriate.
Thansk again.
Comment from Tom
Time: Wednesday, October 29, 2008, 11:42 am
If my next cell contains a zero rather than being blank, is there some way yo omit the zero values from the chart?
Comment from Jon Peltier
Time: Wednesday, October 29, 2008, 12:28 pm
Instead of using COUNT(), which counts zaros as well because zero is numeric, us COUNTIF() as follows:
=OFFSET($B$1,1,0,COUNTIF($B:$B,”>0″),1)
Comment from Durand Sinclair
Time: Wednesday, November 5, 2008, 2:05 am
This article has been really good for a project I’m working on, where I’m putting dynamic charts into a template.
But the solution above only works if you know the name of the workbook. Is there a way of adding the workbook name with a formula, so you don’t have to hardcode it between quote marks?
Comment from Jon Peltier
Time: Wednesday, November 5, 2008, 7:53 am
Durand -
You have to use a magic worksheet function called INDIRECT(), plus another function that builds a range address, called ADDRESS(). This doesn’t always work in charts, but in this case it does.
Suppose the source workbook name is in cell D1. I used INDIRECT and ADDRESS to define the reference cell in the first argument of OFFSET:
YValues
=OFFSET(INDIRECT(ADDRESS(2,2,1,TRUE,Sheet1!$D$1)),0,0,9,1)
The other names were easy:
XValues
=OFFSET(YValues,0,-1)
MyName
=OFFSET(YValues,-1,0,1,1)
The chart series formula is
=SERIES(main.xls!name,main.xls!XValues,main.xls!YValues,1)
As long as the workbook in cell D1 is open, this works. However, INDIRECT will not work if the address it references is in a closed workbook.
Comment from Gabriela Cerra
Time: Wednesday, December 17, 2008, 1:04 pm
This is the first time I write a comment in your blog, but I am a fan of it. It is were I really learned how to make good graphs (at least I like them).
I have been using the OFFSET function for about 8 years and I have been using it a lot. I usually import external data using MS query and I never know how many rows will be imported. I name several columns using the offset function. Many times I use the named ranges in array formulas. If the data imported has many rows and if there are many array formulas, the calculation of the workbook can be very slow.
Somewhere I read that using the INDEX function would have a faster calculation than OFFSET
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
and to make it even faster it would be:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,countRows)
where countRows is a named cell with the formula
=COUNTA(Sheet1!$A:$A)
I just started using the INDEX function instead of the OFFSET, but as I just changed jobs I don’t have access to those large files to check if it is really a time saving tip.
Comment from Jon Peltier
Time: Wednesday, December 17, 2008, 3:04 pm
Gabriela -
The benefit of INDEX over OFFSET comes about because OFFSET is a volatile function while INDEX is not. A volatile function recalculates every time the worksheet calculates, whether or not its arguments are within the calculation tree.
Comment from Gabriela Cerra
Time: Wednesday, December 17, 2008, 3:16 pm
Jon,
Is there any benefit of OFFSET over INDEX?. Most articules about dynamic ranges use the OFFSET fuction
Comment from Jon Peltier
Time: Wednesday, December 17, 2008, 3:24 pm
Gabriela -
Good point. Why use OFFSET if it’s slower?
I think OFFSET is more common because it might be easier to understand. Or maybe it’s the first way people learned to do dynamic ranges, and INDEX is too much of a new trick.
Comment from Carmen
Time: Monday, January 12, 2009, 7:35 am
Hi,
I am trying to find out why, when using OFFSET, my external references only update the source document is open. I see that one of the comments mentions the same problem, yet you seem to think that it should work even if the source document is closed. Do you know what may be causing the problem. I do have the automatic calculation selected as well as the update remote references option. Thanks.
Comment from Jon Peltier
Time: Monday, January 12, 2009, 7:44 am
It’s the way it is. On September 25, 2008 I said Offset can reference a range in any open workbook. I don’t think I’ve said otherwise.
Comment from Chellie
Time: Wednesday, February 11, 2009, 3:24 pm
Hi,
I combined your method for dynamic ranges and Chart by Checkbox in a workbook I am putting together. You’re website helped a ton on trying to figure out something I’ve been working on off and on for a month now. The only question I have now is how do i hide the unchecked series in the legend on the chart? Is there a way to hide them so that the formatting is maintained even if the box is unchecked?
Thanks!
Comment from Jon Peltier
Time: Wednesday, February 11, 2009, 4:23 pm
To hide the legend entries you are forced to resort to code (and use more code to restore them). This is because the series is still part of the chart, even though it has no chartable points.
This little characteristic is another reason to prefer using data labels on the data series to using legends.
Comment from Chellie
Time: Wednesday, February 11, 2009, 4:27 pm
Is the code simple, or pretty complex? I’m not familiar with code to hide stuff in the legend.
Comment from Jon Peltier
Time: Wednesday, February 11, 2009, 5:24 pm
The code is not as long as it is convoluted. The problem is, you have no way of knowing from the object model which legend entry goes with which series. Your code can’t read the contents of the legend entry.
If all series in the chart are the same type, you can just count to get the one you need, though you’d better count backward, otherwise deleting one will mess up the order.
If the series are different types, or if some are on the secondary axis, then you have to follow more tenuous logic to determine which is which. I’ve actually written a blog post on the order of legend entries, but it’s not yet published. I still need to polish it off, but I’ll try to post it this week. Friday, I hope.
I suppose you could extract the formatting of the series, compare the formatting with the formatting of the legend keys, then delete the legend corresponding to the key with the same formatting. Of course, if everything is still using the default formatting, you’ll have to do something like format the series (temporarily) with big ugly old red and green squares, then delete the legend entry whose key is a big ugly old red and green square.
Yes, that would work, but what a pain.
Comment from Chellie
Time: Wednesday, February 11, 2009, 5:35 pm
Yikes! What a bummer.
Comment from Jon Peltier
Time: Wednesday, February 11, 2009, 6:07 pm
Think of it as a challenge!
Comment from Chellie
Time: Wednesday, February 11, 2009, 7:01 pm
Okay, so I figured out that if I hide the columns with the data series in them, then they won’t show up on the graph. Now I just need to write some simple VBA and attach it to the check box so that when it is true the column is visible and when it is false the column is hidden.
Only problem is my ’simple’ code isn’t working. Do you have to set up code differently for a checkbox that is a form rather than part of the VBA stuff?
For example: For my checkbox set up using VBA, my code is
If CheckBox16.Value = True Then
And it works with no problems.
However with my checkbox set up using form that code gives me back a Run Time Error ‘424′ – Object required. I don’t understand the difference between the two. Do I need to reference the cell that actually says ‘TRUE’/'FALSE’ instead of the checkbox?
Comment from Jon Peltier
Time: Wednesday, February 11, 2009, 9:38 pm
Chellie -
This is a good topic for another post.
Here’s how I would handle it. My data would be off to the side somewhere. My checkboxes would be near the chart. The linked cell for each checkbox would be in the column with data for the appropriate series. I would assign the following macro to each checkbox:
Sub ClickSeriesCheckbox()
Dim rCell As Range
For Each rCell In ActiveSheet.Range("M1:O1").Cells
rCell.EntireColumn.Hidden = Not rCell.Value
Next
End Sub
If the checkbox is unchecked, the linked cell value is false, so the Hidden property of that column would be true.
Sorry I didn’t think of this sooner.
Comment from Jon Peltier
Time: Thursday, February 12, 2009, 9:09 am
This morning Chandoo posted an alternative approach, which is available to anyone who understands filters in Make a Dynamic Chart using Data Filters.
Comment from Tarun
Time: Monday, June 8, 2009, 10:52 am
Hi Jon,
Thanks for the article !! It is very useful indeed. I am stuck with bubble charts in excel and you being an expert on charts may be able to help me.
I need to define dynamic bubble charts in excel, so i have defied the named ranges for ’series name’,’ x value’, ‘y value’ and ‘bubble size’. The chart is rendered properly but for ’series name’ the result is a concatenated string of the column values. For example
Series name x value y value bubble size
ABC 3 2 4
DEF 5 3 3
XYZ 5 3 3
Now the series name would be displayed as ‘ABC, DEF, XYZ’ for all the bubbles and to add to the misery all the bubbles are of the same color.
I need to keep these charts dynamic so I can’t go and manually specify specific series names or add multiple services. Do you have any ideas on how to solve this problem ?
I can mail you the excel but dont know your email id.
Thanks for you time.
Best regards,
Tarun
Comment from Jon Peltier
Time: Monday, June 8, 2009, 2:42 pm
For the chart to be dynamic, you should define names for X values, Y values, and bubble sizes. Then create your bubble chart with one series, but select the vary colors by point formatting option. Use a utility like Rob Bovey’s Chart Labeler to add the column of labels to the points as data labels. Now comes the tricky part. You should populate the chart with many more points than you expect to display, and fill in an equivalent number of labels. Use the Chart Labeler after doing this, then revert to the smaller number of points. The data labels in a chart are not dynamic to the extent that X and Y values are, but presetting the links to the labels will accommodate a lot of growth.
Comment from Tarun
Time: Wednesday, June 10, 2009, 8:53 am
Thanks for your reply John !! I wanted to avoid add-in because of the portability baggage. I wrote a macro to achieve this.
Appreciate you help.
Regards
Tarun
Comment from Ash
Time: Thursday, July 2, 2009, 9:01 pm
I am using names to plot charts in my worksheet. I have 15 such sheets in a workbook. Should I create names for each sheet ? Is there an easier way to deal with this? Also, the chart gets plotted only up till it encounters the first blank cell. It ignores everything after that. How can this be overcome?
Comment from Jon Peltier
Time: Thursday, July 2, 2009, 10:29 pm
I would generally create names in each sheet. It’s tedious, that’s for sure. If the sheets all have the same structure (except for something obvious like the number of rows of data), you could create a template with just one sheet and its accompanying chart. Then create a new workbook based on this template, and populate it with the first sheet’s data. Again using the template, add a new sheet populated with the second sheet’s data, and repeat until you have enough different sheets inserted from the template.
If the data is in a simple row and column layout, and you only need to vary the last row with data, then turn the range into a list (Excel 2003) or table (2007), and base the chart on a range of cells corresponding to a column of this list/table. As the list/table expands and contracts, any formulas based on an entire column of the list/table will update to reflect the length of the list/table. This includes chart series formulas, making this a lazy man’s dynamic chart technique.
Comment from Ash
Time: Friday, July 3, 2009, 1:13 am
Thanks very much for that. The columns have some blank rows in them followed by some rows of data. The chart only picks the range till the 1st or 2nd blank row. I used the range as some thing like =OFFSET(BS2!$D$3,0,0,COUNTA(BS2!$D:$D)-1), but some rows do not have any data for column D, and this cuts the graph out. Any ideas on this?
Thanks in advance
Comment from Jon Peltier
Time: Friday, July 3, 2009, 6:23 am
Don’t you have X values (or category labels) for all rows? If not, you need to reform your data range.
But assuming your X column is intact, define an X range:
Name: Xvalues
Refers To:
=OFFSET(BS2!$A$3,0,0,COUNTA(BS2!$A:$A)-1)
Then define your Y values as offset a number of columns from the X values:
Y1values =OFFSET(Xvalues,0,1)
Y2values =OFFSET(Xvalues,0,2)
etc. (leave off the last two OFFSET arguments)
These Y value ranges will be the same length as the X value range
Comment from Harry
Time: Tuesday, September 29, 2009, 9:58 pm
A great article! I have been trying to for quite a while to figure out how to create a dynamic chart, with no success. Jan Pieterse sent me to this article.
I do have one question. I am using Excel 2007 on Windows XP Pro. You talk about making the chart dynamic using the Series function. But I can’t determine where the Series function gets entered! Perhaps you can provide a more complete description. I had to use your second method (right click on the chart > Select Data), and that works just fine.
You mention to be careful with blank data cell rows when using the Count function. If you simply use the Count function on another (fully populated) column (i.e., if Column C has blanks and Column B does not, use something like
=Offset($C$1,1,0,Count($B:$B),1) everything works. This also works if column C calculates something like a moving average, where all cell rows are not populated on purpose.
Harry
Comment from Jon Peltier
Time: Tuesday, September 29, 2009, 10:38 pm
Harry -
The series formula in Excel 2007 is just like in 2003: select an existing series and look in the formula bar. You could select the chart area or plot area (both in 2003, I forget which one in 2007) and enter one from scratch, but that’s a pain.
Regarding the offset formula, if you have a column that’s fully populated, it probably has a name defined for it, for example, Name1 which refers to =Offset($B$1,1,0,Count($B:$B),1). Just use an offset of this name for your new name, so Name2 refers to =Offset(Name1,0,1). I actually showed this with the names VertValues and VertCats in this article. If the populated column has no associated name, of course, then using offset of that column in a different column’s name works just fine, as you suggest.
Comment from Harry Ward
Time: Wednesday, September 30, 2009, 2:14 pm
Jon:
I didn’t have Excel 2003 (upgraded from Office 2000) but I figured out that I had to create a chart the “normal” way first, then click on the displayed data series plot, and =SERIES(…. was displayed on the bar near the top noted as Fx.
Thanks for such a neat tool.
Harry
Comment from Harry Ward
Time: Friday, October 2, 2009, 3:24 pm
Jon:
After creating dynamic charts using the offset formula (following your instructions), I now get on SOME of the workbooks the following message when I open the file:
“File Error! Data may have been lost.”
Yet I cannot find any data or chart problems. And this message did not appear initially.
Have you ever seen this error on your dynamic charts, and can you guide me as what might be the cause?
Thanks,
Harry
Comment from Jon Peltier
Time: Friday, October 2, 2009, 4:04 pm
If you’re using Excel 2003, upgrade to SP3. If you’re using Crystal Reports, there may be an update that will help. Same with other third party plug ins. You might also try Detect and Repair from the Help menu.
Comment from Harry Ward
Time: Friday, October 2, 2009, 8:15 pm
I’m using Excel 2007 on Windows XP Pro, SP2. Sorry I forgot to mention that.
I don’t see any reference to Crystal Reports in my Excel (or in add-ins), but I do find a Crystal directory on my hard drive. I have no idea what it is used for.
I searched the web under the error message. Every mention of that message for Excel (and there were quite a few) said one could not open the workbook after getting the message. But I can open just fine, and as I said before, I can’t find anything amiss. Since I have never seen this message before, that is why I though it might have something to do with the way I created dynamic charts.
Excel 2007 has the equivalent of Detect and Repair, but I had not thought of using it. Thanks for the suggestion. Of course now, I don’t seem to be getting the error message (it comes and goes). If I get it again, I will run Detect/Repair.
Harry
Comment from Dan S.
Time: Tuesday, November 17, 2009, 2:12 pm
Advice needed.
I created a swell set of charts using about 30 range names–most dynamic, some not.
I wanted to copy the worksheet to a new worksheet so that I could enter another set of data (same structure, just for a different year). I see that while a new set of range names were created for the new sheet, the charts all have static references–even on the original page.
What the heck is going on and how do I keep it from happening?
Comment from Jon Peltier
Time: Tuesday, November 17, 2009, 2:44 pm
You need to scope the names to the worksheet, not to the workbook. In 2003, you need to prefix the name with the sheet:
Name: =Sheet1!MyName
Then save the sheet as a one-worksheet workbook with a new name. Open the main workbook, then open the one-sheet workbook, and move the sheet into the main workbook. For each copy, reopen the one-sheet workbook, and move the sheet into the main workbook, then change the data.
Comment from Dan S.
Time: Tuesday, November 17, 2009, 4:11 pm
Thanks–it took me a little while to understand what was happening, but this method worked great!
Comment from Syed
Time: Tuesday, November 24, 2009, 8:30 pm
Hi Jon,
I am having a difficult time in implementing the dynamic charts.
I was able to implement it on one sheet, however i have few dozens of sheets (with same structure which carries the performance charts of analysts).
I created the first sheet as per the article and was able to get the dynamic charts working for that one, however when i copied the dynamic charts sheet to a new sheet and changed the analyst reference to update the data, it will not update the chart even if the data is updated.
Please help.
Comment from Jon Peltier
Time: Tuesday, November 24, 2009, 9:41 pm
Hi Syed -
You need to use the techniques in Make a Copied Chart Link to New Data to handle your situation.
Specifically, you need to define your dynamic ranges for the worksheet they are on, not for the whole workbook. Do this by inserting a reference to the sheet in front of the name of the dynamic range:
Name:
Sheet1!StaticCats
Refers To:
=OFFSET($A$1,1,1,4,1)
Note: you need single quotes around name if it includes space or other “special” characters:
‘Sheet 1′!StaticCats
I always use single quotes, and if they’re not necessary, Excel removes them.
Once you’ve defined the ranges on a sheet-basis, save the worksheet as a template. Then insert a new sheet based on this template for each sheet you need. Replace the template’s data with the data you need to use.
Comment from andrdk
Time: Wednesday, December 2, 2009, 3:13 pm
Hi John,
I am having chart data in row series but the problem with my dataset is that the rows get added whereas columns remain fixed. Is it possible to define a dynamic chart for a row series with named ranges?
Thanks.
Comment from Jon Peltier
Time: Wednesday, December 2, 2009, 9:57 pm
Hi Andrdk -
See the very end of the article, where it talks about series in rows. It shows how to use the different parameters of the OFFSET function to identify a horizontal dynamic range rather than a vertical one.
Comment from Syed
Time: Wednesday, December 9, 2009, 1:20 pm
Hi Jon,
I was able to successfully deploy the Dynamic Charts, however i am facing a small problem here.
When i remove the data from all the cells which carries the values information i get an error on screen:
A Formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell reference.
But if i place some values in any of the cells that error goes away.
Is there a way to get rid of this one even if we dont have values.
Please help.
Comment from Jon Peltier
Time: Wednesday, December 9, 2009, 5:33 pm
The charts will be unhappy if there is no data to plot. But if you go so far as to delete the rows or columns with data, there will be a broken reference in the series formulas (#REF!), which gives you the error message.
Comment from Kirill
Time: Friday, December 11, 2009, 11:49 am
Hi John.
I made a dynamic range chart. It works perfectly until i close the file and reopen. After that a range behave in a strange way. If i add or delete (even all) data in a range it doesn’t change. When i select an existing series the formula bar is empty! When i go to Source Data window to check a name of series there i see [0]!prices when initially it was File_Name!prices (when i drew a chart initially i did it through Data Source window and wrote List_Name!prices but Excel (2007) translated it automatically in File_Name!prices)
What is the problem with it?
Thanks
Comment from Jon Peltier
Time: Friday, December 11, 2009, 12:32 pm
Kirill -
Changing the reference from sheet name to file name is not an issue. Changing file name to [0] is a severe issue. Have you upgraded to the latest service pack for Excel 2007 (SP2)?
Comment from Kirill
Time: Saturday, December 12, 2009, 9:46 am
No, i didn’t upgrade. How to do that?
Comment from Jon Peltier
Time: Saturday, December 12, 2009, 10:01 am
Kirill -
Click the big ugly round Office button. Click the Excel Options button, then click on Resources, and finally Check For Updates.
Comment from Kirill
Time: Saturday, December 12, 2009, 11:53 am
Yes, I did it and updated (SP3). Nothing changed ((
Comment from Jon Peltier
Time: Saturday, December 12, 2009, 12:10 pm
SP3? Excel 2007 is only up to SP2. Do you have both 2003 and 2007 on your computer? Maybe only 2003 was updated.
Comment from Kirill
Time: Sunday, December 13, 2009, 7:04 am
Sorry for misleading. SP3 were updates for Windows. Later I have installed SP2 for Office as you said and it started working! Great. Thanks a lot Jon for you advice. Your help is really appreciated!
Comment from Mary
Time: Thursday, April 22, 2010, 10:55 am
I seem to have a problem with dynamic charting. When I establish named ranges for my two columns and apply these names to the series ranges in the chart, I receive an error message: “A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference”
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,1)
becomes
=SERIES(Sheet1!$B$1,Sheet1!Months,Sheet1!Visit,1)
I don’t know what I have done incorrectly, since when I tried it at home, it worked correctly, but at my office it is giving me an error.
Comment from Jon Peltier
Time: Thursday, April 22, 2010, 11:01 am
Mary -
Are you using different Excel versions? Which version does not work? Does the file that works at home still work at the office?
Comment from Mary
Time: Thursday, April 22, 2010, 11:07 am
I use Excel 2007 at home and at work. It was a simple chart and table I was testing with months in one field and number of visits in another.
Comment from Jon Peltier
Time: Thursday, April 22, 2010, 11:54 am
If the work computer has not been upgraded to SP2, there might be some issues.
Comment from Mary
Time: Thursday, April 22, 2010, 1:56 pm
I was able to ask IT and I was informed that we have SP2. Has anyone ever experienced a problem like this before? I tried troublshooting with other sites, but to no avail.
Comment from Jon Peltier
Time: Thursday, April 22, 2010, 2:17 pm
Does the example you made successfully on the other computer work on both?
Comment from Chris
Time: Saturday, May 15, 2010, 4:17 pm
Jon-
My company sends out daily “Dashboard” sheets. The cell location for data is always consistant. If I saved each days Dashboard in a central location, how can I create a dynamic chart to chart specific data from each day?
Comment from Jon Peltier
Time: Sunday, May 16, 2010, 10:34 am
Chris -
Not enough information. Do you show multiple charts in one view, say, a week’s worth of charts? Is the data in each of a week’s worth of worksheets?
Or is it one chart with data from multiple sheets?
Comment from Alison
Time: Monday, May 24, 2010, 7:32 am
Hi Jon, you’ve helped me tremendously in the past with charts and hope you can again. I have the issue dealt with in the posts above dated 24 Nov 2009. I have a number of sheet with the same format and a chart referring to named ranges in each sheet. I have defined the named ranges for the worksheet not the workbook.
If I copy a working sheet, to create a new version for new data, the named ranges are THERE, but the chart doesn’t pick them up, and i have to reenter them each time.
If I click on one of the ranges in the chart Excel says “the text string you’ve entered is too long…. etc.”. If I look at I can see that all of the data points are listed in the series block rather than the named ranges. (so i type the named range in instead)
I’ve also tried doing as you suggested by saving the file as a template and then copying a working sheet from the template to the workbook, but the same thing happens.
Any idea what I’m doing wrong?
Comment from Jon Peltier
Time: Monday, May 24, 2010, 8:13 am
Alison -
There are steps you must take to copy a chart and its data while preserving the expected links. I wrote about this in Copy Chart and Data and Preserve Links.
Comment from Alison
Time: Monday, May 24, 2010, 8:45 am
Hi Jon. Thanks.
Am copying the whole sheet (several times), not just the chart, (each sheet has exactly the same format but deals with a different indicator) and the chart links were fine in the copied sheet when they were linked to unnamed (and static) ranges.
But now that I’ve made them named ranges copying the whole sheet loses the chart links to the named range, even tho the named ranges are in the new worksheet.
Comment from Jon Peltier
Time: Monday, May 24, 2010, 12:01 pm
It is the links to worksheet-scoped names that break when the sheet is copied. Do this: Move the sheet or sheets to a new workbook. Close the original workbook without saving, so it will still have the moved sheets in it. Save the new workbook created by moving the sheet(s) as a template. Open the original workbook, and for each new copy of the original chart you need, insert new sheet(s) from this template you’ve saved.
Comment from Alison
Time: Monday, May 24, 2010, 4:36 pm
Hi Jon. Thanks, that helps, (unfortunately, other formulae in the sheet link back to the template worksheet, and when i change the link in Edit Links, I get an error that the ‘formula is too long ..’.).
I will work around this, by making the entire workbook a template.
(Once I finish messing around with the workbook!).
Comment from Andres
Time: Thursday, August 19, 2010, 11:57 am
Hi Jon,
I have a problem I can’t figure out.
I have created a workbook that has several sheets with a dynamic chart on each sheet that allows me to zero in on different time periods and to vary the number of series shown. In order for this to work I have used named ranges and defined them using the “indirect()” and “address()” functions. My approach has worked great for all of the charts until I decided I needed to get a slightly different perspective on the data. I created another sheet within the workbook, and used the same approach that I had for the previous sheets but I cannot get the dynamic chart to update.
I created a range name and assign it to the source data in the chart dialog box just like I did for the others. when I click “Okay” in the source data dialog box, the values change in accordance with those shown in the named range. But when I change the amount of data to be shown the values in the chart do not change, even though the cells feeding into the named range are changing to represent the block of data I want to see. Just for grins I tried using the “offset()” method but have the same results.
Do you have any thoughts on why my chart remains static even though the ranges it uses are dynamic?
Thank you.
Comment from Jon Peltier
Time: Thursday, August 19, 2010, 1:22 pm
Andres -
Debugging these things is tricky. You can look at the series formula, and see what the series thinks it should be based on. If it’s a name, then copy the name with the sheet prefix and exclamation point, type this into the Name Box (to the left of the formula bar), and press Enter, and the associated range will appear.
I know that some folks have reported insoluble problems with charts not updating in Excel 2007. I have not really encountered this myself, and on a couple occasions I ‘ve been able to attribute the problem to pilot error.
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.