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
Tim says
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.
Jon Peltier says
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.
Debra Dalgleish says
Nice article, and changing the references in the formula bar is a great tip — much quicker than going through the dialog box.
Jon Peltier says
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.
Tim says
“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.
Jon Peltier says
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.
Hitesh says
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
Jon Peltier says
Hitesh –
I know nothing about making Flash charts. Try a Google search on a few relevant keywords, like flash chart xml.
Hitesh says
This is the example of what I want to create
[broken link]
H
Jon Peltier says
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.
JP Gonzalez says
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!
Jon Peltier says
Offset can reference a range in any open workbook.
JP Gonzalez says
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!
Oscar says
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.
Jon Peltier says
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.
Oscar says
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.
Jon Peltier says
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.
Jon Peltier says
Oscar –
Excel must have decided that you have suffered enough.
Oscar says
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.
Oscar says
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 ).
Tom says
If my next cell contains a zero rather than being blank, is there some way yo omit the zero values from the chart?
Jon Peltier says
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)
Durand Sinclair says
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?
Jon Peltier says
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.
Gabriela Cerra says
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.
Jon Peltier says
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.
Gabriela Cerra says
Jon,
Is there any benefit of OFFSET over INDEX?. Most articules about dynamic ranges use the OFFSET fuction
Jon Peltier says
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.
Carmen says
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.
Jon Peltier says
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.
Chellie says
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!
Jon Peltier says
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.
Chellie says
Is the code simple, or pretty complex? I’m not familiar with code to hide stuff in the legend.
Jon Peltier says
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.
Chellie says
Yikes! What a bummer.
Jon Peltier says
Think of it as a challenge!
Chellie says
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?
Jon Peltier says
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 SubIf 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.
Jon Peltier says
This morning Chandoo posted an alternative approach, which is available to anyone who understands filters in Make a Dynamic Chart using Data Filters.
Tarun says
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
Jon Peltier says
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.
Tarun says
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
Ash says
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?
Jon Peltier says
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.
Ash says
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
Jon Peltier says
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
Harry says
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
Jon Peltier says
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.
Harry Ward says
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
Harry Ward says
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
Jon Peltier says
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.
Harry Ward says
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
Dan S. says
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?
Jon Peltier says
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.
Dan S. says
Thanks–it took me a little while to understand what was happening, but this method worked great!
Syed says
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.
Jon Peltier says
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.
andrdk says
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.
Jon Peltier says
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.
Syed says
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.
Jon Peltier says
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.
Kirill says
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
Jon Peltier says
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)?
Kirill says
No, i didn’t upgrade. How to do that?
Jon Peltier says
Kirill –
Click the big ugly round Office button. Click the Excel Options button, then click on Resources, and finally Check For Updates.
Kirill says
Yes, I did it and updated (SP3). Nothing changed ((
Jon Peltier says
SP3? Excel 2007 is only up to SP2. Do you have both 2003 and 2007 on your computer? Maybe only 2003 was updated.
Kirill says
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!
Mary says
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.
Jon Peltier says
Mary –
Are you using different Excel versions? Which version does not work? Does the file that works at home still work at the office?
Mary says
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.
Jon Peltier says
If the work computer has not been upgraded to SP2, there might be some issues.
Mary says
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.
Jon Peltier says
Does the example you made successfully on the other computer work on both?
Chris says
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?
Jon Peltier says
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?
Alison says
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?
Jon Peltier says
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.
Alison says
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.
Jon Peltier says
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.
Alison says
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!).
Andres says
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.
Jon Peltier says
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.
saafron says
I am having a very strange issue (Excel 2003) regarding the use of a dynamic range in a chart
Cell A1 = $D$1:$D$100 (note: absolute references)
I’ve defined a Range Name = “YVAL” as =Indirect($A$1)
I’ve attempted to use that range name in a series – e.g .,
=SERIES(“Name”, Sheet1!$C$1:$C$100, Sheet1!YVAL, 1)
I get a error dialogue indicating invalid reference.
FYI I’ve used other dynamic ranges successfully in this model.
Any thoughts?
Thanks in advance!
Jon Peltier says
saafron –
I have not been too lucky using names based on INDIRECT() as chart source data, I don’t know why, it seems like it should work.
You could select another columnar range, type =YVAL into the active cell, and hold Ctrl+Shift while pressing Enter. This enters YVal as an array in this range. Plot this range, which will update as $A$1 and YVAL change.
Anonymous says
Thank you Jon. You’re creating a lot of good karma for your unselfish assistance! If I can ever return the favor, pls let me know.
-Paul
Jennifer says
I used the instructions you posted as “Chart the last 12 Months Dynamically.” I did this for 14 charts both bar and line in PowerPoint 2010. It was going well and I double checked each chart before moving onto the next one. A couple hours later I went back to check again on the charts and the dynamicness was lost. I have checked all of my names in the name manager and they are correct. Please help me I am really really lost.
Jon Peltier says
Jennifer – Do the series formulas still have links to the names, or just to the cell addresses?
Jennifer says
The series name says: =Sheet1!$B$1
The series values says: =’Chart in Microsoft PowerPoint’!chtValA
I am assuming that means links to the names.
Jon Peltier says
Jennifer –
I’ve never built an embedded chart in PowerPoint 2007 before, at least not when using dynamic ranges. So I was underwhelmed by the capabilities.
First, I noticed that the dummy data was in a Table in the source sheet. It wasn’t formatted as such, and you didn’t get the handy dropdowns on the series headers, but it’s a table. When I turned it back to a range, the blue outline remained behind. Turns out there was no Table highlighting outline, it was just a colored border. Huh.
So I defined my dynamic ranges and inserted them into the chart using the Select Data command on the Chart Tools > Design tab. Fine.
I added a row to the data. The dynamic names updated fine. The chart did not update, no matter what I thought to do. To change the source data, I had to actually select the new data. When I then recreated a Table based on the range, the chart became dynamic again, insofar as the Table height changed (see below).
I made another chart, and did not change the table to a range. When I added a row, it became part of the table, and the related chart did in fact update. This is the nice thing about Tables (a/k/a Lists in Excel 2003): whenever a formula is based on a column that spans the entire height of a Table, the formula adjusts when the Table changes size, to incorporate the new height of the Table. Fortunately this includes the SERIES formula in a chart, so it simplifies about 98% of all dynamic charts.
I thought I’d written a tutorial about this simpler way to get a dynamic chart, but apparently I have not done so. Stay tuned.
Paul says
I have an Excel 2003 scatter plot with a number of curves (series).
These series are defined by named ranges, which works very well.
My issue is that I would like to be able to hide any of the curves in the plot with the click of a dedicated command button for each curve.
For curves defined with a normal range, you can just hide the cells containing the data and the curve (including the name of the curve in the legend) will disappear.
This is not the case when the curves are defined with a named range, you can hide the cells containing the data and the curves remain on the plots.
Is there a way to hide data defined with named ranges?
Jon Peltier says
Paul –
You need a checkbox for each series. Say the linked cell for the series is cell D1. Change your Name definition (Refers To) from
=<some formula>
to
=IF($D$1,<some formula>,NA())
Chris Waters says
I’m using Excel 2007 on Windows 7. I have a simple scatter chart base on two columns of data, and wanted to make the chart update when I add a new row of data each day. I was delighted to find your blog and set up the range names using the OFFSET function, and then used the Select Data dialog to put the x and y data ranges in to the appropriate boxes.
This worked perfectly, and I saved the workbook and closed Excel. The next day I opened the workbook and added a new line of data but the chart did not update. The Edit Series dialog shows the range names for x and y and the new ranges are highlighted (crawling ants) when you click on the icon to the right, but the chart remains the same.
I can retore the dynamic updating: Highlight the x and y ranges with the mouse, say OK, then re-enter Edit Series and change to the range names. But if I save, leave and re-enter Excel the chart no longer updates. As long as I leave Excel open, the chart updates properly each day.
Any Ideas?
Anant says
Hi
I am trying to create a dynamic stacked bar chart having three plot series, but I am unable to edit series formula for 2nd and 3rd plot order formula. My question is why am I not able to edit series for 2nd and 3rd plot order formula..
Plz help!
Jon Peltier says
Excel 2007 is unreliable in its support for editing the series formula to assign range names instead of range addresses. If this is the case, you can open the Edit Data dialog, and change the range addresses to range names within the dialog.
Anant says
Hi
Thanks for your prompt response.
Yes you are right. I am using excel 2007 and I made named ranges and made a dynamic chart then replaced the Sheet1 with file name.xls and range address with range names. It worked fine with first order plot series formula, but when I tried to edit series formula for 2nd and 3rd plot order it won’t change. It simply sits there. Where am I going wrong? I am unable to figure it out.
Jon Peltier says
Don’t change the worksheet name to the workbook name. Excel will do that if necessary.
Also, maybe your Refers To formulas are too intricate for a chart-related name. Could you post the formulas here?
Anant says
Hi!
I think I figured it out. This is what I did. Instead of editing the series formula in the formula bar, I entered the named ranges in the series values and in the Axis Label range while adding data series to make a dynamic chart. I have also included my recently started blog where I have illustrated how I did it.
Thank you! You really did help me.
Thomas says
Great article. I do have two questions, though. I’m creating a chart and dynamic named ranges for charting the last 30 entries in a large data set.
#1. I am using the index() formula to chart the data. And with that, how do you create only the last 30 days chart? I was using the Offset() formula but I found it to be too volatile.
Here is an example of a formula I have used to create an overall chart:
=’Data-Entry’!$D$5:INDEX(‘Data-Entry’!$5:$5,COUNTA(‘Data-Entry’!$5:$5))
#2. To go along with this question, my data is in rows, now columns. How do you switch the syntax of the formula to accommodate this. Thank you for the help!
Jon Peltier says
Thomas –
1. If
INDEX(‘Data-Entry’!$5:$5,COUNTA(‘Data-Entry’!$5:$5))
is the last entry, then the entry 30th from the end is
INDEX(‘Data-Entry’!$5:$5,COUNTA(‘Data-Entry’!$5:$5)-29)
so you want
INDEX(‘Data-Entry’!$5:$5,COUNTA(‘Data-Entry’!$5:$5)-29):INDEX(‘Data-Entry’!$5:$5,COUNTA(‘Data-Entry’!$5:$5))
(Watch out, WordPress converts straight quotes to curly quotes.)
2. This is in rows. If you want columns:
INDEX(‘Data-Entry’!$D:$D,COUNTA(‘Data-Entry’!$D:$D)-29):INDEX(‘Data-Entry’!$D:$D,COUNTA(‘Data-Entry’!$D:$D))
Thomas says
John –
Thanks for the help! The formula works a lot better…I do have one more question in regards to it…the question may have more to do with my office security settings then anything else but I wanted to ask anyway.
The workbook I am working out of has something like 45 separate worksheets. Each sheet has two graphs – overall & the last 30 days. When I create the graphs within their specific worksheets, the graphs work fine. However, when I move them to a single sheet to combine all the graphs, the 30 day graphs “Series” formula does not translate over. When I open and close the spreadsheet, the graphs do not update anymore.
Any suggestions for a fix?
Thanks for all of the help!
Thomas says
Another comment in regards to that:
The “Series Values” section of the “Select data” changes to [0].
Jon Peltier says
Thomas –
If the named ranges are scoped to the worksheet and not the workbook, chart formulas lose the names when they are copied and pasted on another sheet. I think even copying the whole sheet hoses the names. You’ll have to fix the formulas.
Thomas says
Hey Jon –
Thanks for all the help. I apologize it took so long to get back to you. I have changed the scope to the formulas but the charts are still not dynamically updating.
Any thoughts?
Thanks!
James says
Great post. I’m still left with the question I came here seeking the answer for.
I did a cursory scroll through the comments and also have looked at a number of online and print resources but have been unable to determine whether it’s possible to dynamically add series (instead of just adding data to a series). This would be sort of like selecting a whole area of data and charting based on it (and letting Excel choose the series), but dynamically. Using an offset to define a two-dimensional area doesn’t appear to do the trick — Excel seems to convert that dynamic range into a static range when it’s first processed.
I know this can be done with VBA, but as I often work with clients who prefer macro-free workbooks (and in general, ’cause I’m interested) I wonder if there’s a way to do it with just formulas.
Jon Peltier says
James –
In Dynamic Chart Source Data I wrote:
“One limitation of [the dynamic range] 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.”
James says
Thanks Jon — I just knew you had addressed this issue and I really appreciate you pointing me to it. It’s a great service you do here for all of us out in the trenches …
Kelly says
Using Excel 2007 (for now) I’ve made a table that has 7 columns.
In column 4 I have an item description and in column 5 i have the amount I paid for that item.
In column 4 there are item descriptions that show up more than once, but with different prices in column 5. For example, I put ‘Walmart’ in column 4, and $22.00 in column 5. Then below it I’ll put in ‘Walmart’ in column 4 again, but this time $10.00 for the price in column 5
I’ve tried the PivotTable option, but it kept giving me a pie chart with every single transaction, i.e., ‘Walmart’ – $22.00, ‘Walmart’ – $10.00.
What I would like to do is create a pie chart that shows a ‘Walmart’ slice, but next to the heading I would like it to say $32.00 and update dynamically with each time I enter another transaction.
Kelly says
I forgot to say please and thank you for any help. -_- Sorry.
Jon Peltier says
Kelly –
If you use column 4 as a route field and sum of column 5 as a data field, you should get the distinct items in column 4 each listed once, with their totals. Make sure there are no variations in spelling (like trailing blanks).
Sajan says
Hi,
Thanks for the writeup regarding dynamic charts. It was very helpful.
My question is regarding non-contiguous data.
How could one create dynamic charts with non-contiguous data? (such as every 3rd cell in a row)
My data setup is similar to the following:
A B A B A B A B A B
Jan-12 Jan-12 Feb-12 Feb-12 Mar-12 Mar-12 Apr-12 Apr-12 May-12 May-12
1 10 2 20 3 30 4 40 5 50i.e. the header row is row 1
the date row is row 2
the value row is row 3
What I would like to do is create a chart (e.g. line chart) with the dates as the X-axis, and two data series: one for the “A” columns, and the second for the “B” columns.
Based on examples I have seen on this site and elsewhere, I have been able to create dynamic charts where the data is contiguous. i.e. when the resulting data range is one single contiguous range, the chart gets plotted correctly. However, when the data range is selected from non-contiguous cells, I am finding that Excel plots only the first cell.
Essentially, what I am attempting to plot are the following two (dynamically created) series:
“A” series would be the array {1,2,3,4,5}
“B” series would be the array {10,20,30,40,50}
A simplified formula for generating the “A” series is:
=OFFSET($A$3,0,(COLUMN($A$3:INDEX($3:$3,5))-1)*2)
“B” series is:
=OFFSET($A$3,0,(COLUMN($A$3:INDEX($3:$3,5))-1)*2+1)
A simplified formula for generating the “date” values is:
=OFFSET($A$2,0,(COLUMN($A$3:INDEX($3:$3,5))-1)*2)
Any help will be very much appreciated.
thanks,
Sajan.
Sajan says
Hi,
I was able to find the solution for my previous post. Apparently, I needed to wrap the OFFSET results in N() to force them as numbers.
I will need to study why the OFFSET alone did not work in the dynamic range.
-Sajan.
mike selman says
One of the replies suggested using the INDEX function. I created the following function in Excel 2007, but an error message appeared when trying to add it as the Series Value in the Select Data Source box.
=’IS History’!$E$117:INDEX(‘IS History’!$117:$117,COUNT(‘IS History’!$117:$117))
With values to chart in columns E through AZ of row 117, what should the formula be? My intent is to dynamically update the chart as new data is added.
James Bronzan says
Hi Mike —
I think this comment by Jon from last year will answer your trouble: https://peltiertech.com/dynamic-charts/comment-page-1/#comment-182609.
Cheers,
James
Jon Peltier says
Mike –
I find OFFSET to be the most reliable formula for defining a chart data range. So try:
=OFFSET(’IS History’!$E$117,0,0,COUNT(‘IS History’!$117:$117),1)
kary says
Hi Jon
I have a dynamic chart with named ranges using the Indirect function. The only item which is not dynamic is the filename in the data range of the chart i.e. =’NameOfFile.xlsm’!RangeName. I have a function which provides the filename in a cell – have tried to use it directly in the data range viz: =$R$2&RangeName; I have also tried creating a range name for the cell which contains the filename (i.e. R2). Can anyone help – please!
Thanks, Kary
Michel GERDAY says
Dealing with financial timeseries, I found another easy way to create a self expanding chart.
My chart data starts in cell A2, which should contain the first date, with the following:
=IF(ISNUMBER(B2),D2,#N/A) , provided that the financial data are in column B2. In column D, I have predefined dates from January 2015 up to December 2020 in cell D73.
I expand down the formula from A2 to A73.
Every month, additional financial data is retrieved into column B. The corresponding date appears instead of #N/A and the chart expands itself.
Note that this only works with a “Date Axis” type.
Done with Excel 2010.
Jon Peltier says
Michel –
“Note that this only works with a “Date Axis” type.” True, in a line or column chart. But it also works for the value axes (X and Y) in an XY Scatter chart.
Simone says
After a bounch of years.. another comment on “[0]” issue.
Office 2016.
Powerpoint with slides with charts, data source in a linked excel.
excel files with Named range (dynamic), with workbook scope.
Charts is not updating, and trying to select data.. my series show [0]!MyNamedRange.
If I change the series source to =MyNamedRange it won’t accept it and I have to use =MySheet!MyNamedRange. Ok and then the chart update.
If I check the chart source again, I see that all was replaced with ExcelFile.xlsx!MyNamedRange.. then if I save the powerpoint and reopen it… again [0]
I’m going crazy! :(