Dynamic Charts
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
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
Possibly Related Posts:
- Dynamic Ranges to Find and Plot Desired Columns
- Interactive Multiple Line Chart
- Dynamic Chart Source Data
- 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
- Dynamic Chart Review
- Easier Interactive Multiple Line Chart
Posted: Wednesday, May 14th, 2008 under Dynamic Charts.
Comments: 46
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
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.