SubscribeMaking a chart with dynamic range of values
We all know that to make a chart we must specify a range of values as input.
But what if our range is dynamic and keeps on growing or shrinking. You cant edit the chart input data ranges every time you add a row. Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows?
Well, you can do it very easily using excel formulas and named ranges. It costs just $1 per each change. 
Ofcourse not, there are 2 ways to do this.
The easiest way to make charts with dynamic ranges
If you are using Excel 2003 or above you can create a data table (or list) from the chart’s source data. This way, when you add or remove rows from the data table, the chart gets automatically updated.
See the below screencast to understand how this works
Using OFFSET formula to make dynamic ranges for chart data
For some reason if you cannot use data tables, the next method is to use OFFSET formula along with named ranges.
We all know that OFFSET formula is used to get a range of cells by passing on starting point and number of cells to offset. Steps for creating dynamic chart ranges using OFFSET formula:
1. Identify the data from which you want to make dynamic range
In our case the data should be filled in the following table. As user keeps on adding new rows we will have to update our chart’s source data.
Lets assume the data table is in the cell range: $F$6: $G$14
2. Write OFFSET formulas and create named ranges from them
Ok, the problem is that as and when we add a row at the end (or remove a row), we should update the chart’s data range. For this, we can use OFFSET formula.
A refresher on how to use OFFSET formula:
3. Create a new named range and type OFFSET formula
Create a new named range and in the “refers to:” input box, type the OFFSET formula that would generate a dynamic range of values based on no. of sales values typed in the column G. I have used the below formula. You can write your own or use the same technique.
=OFFSET($G$6,0,0,COUNTA($G$6:$G$14),1)
Set the named range’s name as “sales_data” or something like that.
Now repeat the same for years column as well and call it “years_data”
4. Create a column chart and set the source data to these named ranges
Create a column chart. For the source data use the named ranges we have just created.
Important: You must use the named range along with worksheet name, otherwise excel wont accept the named range for chart source data.
That is all, now your chart is dynamic
Download the Dynamic Chart Ranges Tutorial Workbook
Click here to download the dynamic chart ranges workbook and use it to learn this trick. I have given Excel 2007 file since the file includes tables.
Bonus Tip: Edit chart series data ranges using mouse
If you have no time for writing lengthy formulas or setting up data tables, you can still save time when editing chart series data ranges. Just select the series by clicking on the chart. Now excel shows highlighted border around the cells from which the chart series is created. Just click on the bottom-right corner and drag it up and down to edit the chart series data ranges. (more: Edit formula ranges using mouse)
See the demo to understand this:
More tricks to make dynamic charts using Excel
Here is a list of tutorials and examples recommend just for you. Go check them out and make your charts even more dynamic.
- Filter Charts just like you filter Data
- Select and show one chart from many
- Dynamically Group Related Data in the Charts
- Use Data Filters as Chart Filters and Make Dynamic Charts
- A Dynamic Donut Bar Chart – Show total and break-ups in an interesting way
Tell me about your experience with dynamic charts using comments.
| ||||
|
| ||||
|
Leave a Reply
 |
The Project Management Bundle for Excel is Here, Download your copy today | How to get Excel 2003 Toolbars in Excel 2007 [productivity hack] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
56 Responses to “Making a chart with dynamic range of values”
I was looking for how to do this about 6 months ago and found it. Just yesterday I realized I had forgotten about it. This time I won’t forget.
Thanks. Fantastic read. As always.
@Finnur.. thank you
Watch out for a fun use of this trick in the upcoming posts.
A very nice way to have a dynamic chart… Thank you for sharing
Now what if I want to be able to dynamically add a new series? All of the dynamic chart tutorials assume that there is only one series with new data being added. I’ve got a line chart with multiple data sets being tracked. I want to be able to add or remove a new data set dynamically. For instance, let’s say I’m tracking the sales figures for apples, bananas, and pineapples. I have a “YES/NO” field so a user can choose to show bananas and pineapples, but not apples on the chart. What then?
[...] This article uses the concepts from How to make chart data ranges dynamic. I suggest reading that article first if you [...]
[...] make a dynamic named range that would refer to column headers in the pivot table. I am leaving this to your imagination, but [...]
Hi Chandoo. Awesome page. MS did not make this easy. Strange. It’s very useful. Make one chart for the whole year, then add data as you go. I have a problem. Even if the result of a cell is =”" as in empty, the series function (or offset function) reads this as zero. Is there any way to get around this?
Many thanks
/Magnus
[...] Using OFFSET formula to make Dynamic Chart Ranges [...]
I have been using this feature for a while successfully. I got a new laptop which has some trouble with this feature. The excel on the new laptop does not recognize the dynamic ranges in charts, and does not update the charts as the range chagnes.
What setup do I need to change in order to be able to use the dynamic charting?
The ranges are recognized outside of charts.
The formula bar does not show the range either.
Any suggestion what to chagne is appreciated.
Erika
@Erica: It should work ok. What version of Excel do you have on this new laptop.
also, when entering range name for the source data, you need to include the worksheet name as well, like Sheet1!range_name. Only then excel accepts that range for chart.
Hello …
I am experiencing a similar issue to Erica. When using both Excel 2007 on a PC and Excel 2011 for Mac, I set up the named ranges (including the data sheet name) and reference them (including the data sheet name) and the chart looks fine. However, nothing happens if extra data is added to the named ranges.
When I then check the source data references they are no longer named ranges but absolute references. It doesn’t seem to matter how many times I re-enter the named cell references or which order I do things. The chart just won’t update and the range references become static and absolute rather than dynamic. A colleague has had similar issues doing this sort of thing in Excel 2007 but not older versions.
Is there some sort of work-around I can use or an ‘update workbook’ type setting I need to adjust … ?
Thanks.
Hello again …
Problem solved thanks to my colleague.
Make sure the named data range IS NOT inserted in the ‘Chart data range’ section (which will disply the result of the named data range – and will therefore look like absolute references) but inserted through editing the data series within the ‘Legend Entries (Series)’ section.
That should do the trick – the problems I was experiencing are a result of being less familiar with this version of Excel.
Hope that clarifies things for anyone else who may have been having similar troubles.
Thanks …
When editing the source data for a chart the only location where I can enter the range name is in the Chart data range where you specified NOT to put it. The legend series section involves each individual series. I may be doing this wrong so could you give a extremely detailed explanation of where you are putting your data range name.
Thanks in advance
The info from this site is very useful. But I have a question. How can you create a dynamic chart if the month are on rows not on column as in the below tabel:
Jan 2010 Feb 2010 March 2010
1 2 3
I have tried all the exemples, but it does’t work. Can you please help.
Thank you !
@rrra
you will use an offset formula like
=OFFSET(A1,0,1,0,COUNTA(B2:B100))
You are a LEGEND!!!!! Do you realise how many tips I get off you for Excel.
Thanks Heaps! Keep up the good work, you Champion!
Thanks for sharing this. This was very useful and very clear to follow.
[...] Another tip that I learnt today. When plotting a graph, the data that is to be plotted must be linked to the graph. What happens when the range of the data changes in length? You need to be able to plot using a dynamic range. [...]
[...] Dynamic Named Ranges using OFFSET formula [...]
Hi Chandoo,
This method works fine for entering data manually, but I’ve got the same issue Reeves has, as per his/her post no. 4from 6 November 2009.
I’m trying to show a range of prices against a raw material, tracking this back from 2000 onwards per month/quarter.
However, the user may want to see a different selection.
Using match/offset I’ve got the data selected, but when applying the described method it shows the data for the range in the graph, but the whole date range on the X-axis, which I awant to avoid.
Any suggestions?
(Note: I have amended a VBA-script I found on Peltier tech, for adjusting 1 or 2 Y-axis, but this doesn’t seem to be working with the X-axis).
Thanks so much for this tip. My question is : if your series data has a formula in it to automatically populate the cells with data every month, EXCEL does not see this as an empty cell. Is there a way of still creating a rolling 13 month chart or do we have to manually populate the cells every month?
PLEASE HELP
Excel 2002 SP3
Bar graph with 2 series
2 named ranges defined in same workbook w sheet called: Annual Compare DATA
AnnualSales=INDIRECT(“‘Annual Compare DATA’!$B$3:”&ADDRESS(3,MATCH(9.99999999999999E+307,’Annual Compare DATA’!1:1)))
AnnualProfit=INDIRECT(“‘Annual Compare DATA’!$B$19:”&ADDRESS(19,MATCH(9.99999999999999E+307,’Annual Compare DATA’!1:1)))
(I know these work as I tested the sum(AnnualSales) etc in the Annual Compare DATA sheet)
Tried In Graph:
=SERIES(,,’Running Data Graphs.xls’!AnnualSales,1)
=SERIES(,,’Running Data Graphs.xls’!AnnualProfit,2)
Doesn’t work, (your formula contains an invalid external reference to a worksheet)
yet If both series are the same, or even a different name referring to the same actual range – it works!!! I tried everything for 7 hours – PLEASE HELP – > thanks
Anyone have any suggestions or fixes regarding my issue (hbeer444)?
Thanks in advance.
Have you tried using OFFSET or INDEX to define the ranges instead of INDIRECT?
No I havn’t CHANDOO – you think that may be better to rewrite it that way? – I will take a look at that…thanks
Hello Chandoo!
One question about this, is it possible to somehow set the marker option so that it will only show one marker as per year to date data without the need to do this manually. I have to do this is a regular basis update the markers of many charts to its current monthly/currently values. I have been wondering if there is an approach to avoid this and set this as an automatic procedure.
Usually we like to have only the current value to emphasize actual situation, we don´t need all markers appearing in the line chart, for each values but only for the last one.
Thanks a lot for your help!
BR
These are great tips!
But I have a question…
I have multiple series and the series are horizontal not vertical.
I also only need the current year, so my table has many months on it but I only want (let’s say I’m doing the chart for december and decemeber is the current month) Jan 2011-Dec 2011(current month). Do these functions only work if you want to add dates to the end. Is there a way to make the chart populate for only the current 12 month period?
Thanks
I’m having the same problem as derKaefer, but even with his explanation, I can’t figure out how to fix it. I reference the named range and the sheet appropriately in the formula, but even when I enter the named range using the “add series” field, the named range converts to an absolute range in the “Chart Data Range” field.
This is driving me nuts! Any help would be greatly appreciated.
@Sam
Did you read derKaefer’s second post where he explains how he fixed it?
Ya, he says: “Make sure the named data range IS NOT inserted in the ‘Chart data range’ section (which will disply the result of the named data range – and will therefore look like absolute references) but inserted through editing the data series within the ‘Legend Entries (Series)’ section.”
The problem is that I can’t figure out how to keep the data range from being automaticall entered into the ‘Chart data range’ section. I enter the data into the data series section, but excel is automatically adding the range into the ‘Chart data range’ section, too.
Any ideas how to keep this from happening? I’m pretty sure I’m using sheet and named referenes correctly…
-Thanks.
I have a problem:
I want to achieve a functionality that if a paste a set of data in my excel sheet(variable no. of rows and columns) and i run my macro it will show me the chart out of that data.
Can anyone help me on this.
Tushar,
If I understood your query correctly, then you want only the lates data set to show on the chart.
You can insert a new sheet where you may track the last row and column of the data.
Now everytime you import new data, you may store the last row and column number and also the new row and column number. Since you are already using VBA this would be very easy.
Now since you have the row and column number with you, use that to update the dynamic range that feeds onto your charts.
Thank you dude!
This PRO information helped me out to finish my worksheet
much appreciated!
This page is very helpful!
I got the dynamic range to work on one worksheet. However, I need to use this worksheet as a template (I go to “move or copy, copy” to make a new worksheet). I’m using Excel 2010.
Whenever I do this, the formula in the new worksheet simply has the range from the template based on how many data points the template had when it was copied, not the dynamic range. I need each worksheet to use the dynamic range based on the information in that worksheet. Do you know how to do this?
Thanks!
[...] http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/ [...]
Great post. Helped a lot and looking forward for more.
Cheers!
Thanks!!!!
Hi chando ….Please share format file to have better understanding of OFFSET formula to create graph…….Please
@Chintan
Have you read either:
http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
or
http://chandoo.org/wp/2010/11/04/analysing-large-tables/
Hi,
Refer both links. but, still my graph show that parameter as blank. can u please share the example file for ”
Using OFFSET formula to make dynamic ranges for chart data
Please….
@Chintan
Can you share your file so we can be specific for your requirements
Thank you Chandoo!
I’ve using this in a lot of charts, but recently I’ve got problems when I try to move the worksheets to another workbook, because the name ranges I created in old workbook will not be copied with the worksheets. I’ve fixed this by recreating name ranges in new workbook.
Actually, I’ve found another way to automatically update charts. When you select series range, you can select longer than the exsiting data points. For example, if there’re 4000 data points, you can select to 6000, then the charts will contain all the data when updated. And I arrange data in reverse chronological order, so I’ll not miss the newest data at least.
No sure what do you think about this method?
@Kunlin… Thanks for your comments. Ideally, adding extra points to the chart (even though they are blank) would make Excel slow. I suggest using tables to keep your dynamic ranges. This way, you do not have deal with moving named ranges to new workbooks or worry about growing or shrinking data.
Hi Chandoo,
I have a problem with the dynamic charts. whenever I close the workbook that contains dynamic charts the formulas will be changed. whenever I open the workbook I need to reinsert the worksheet name into that every charts data.
Please tell me how to solve this problem.
Awesome! Just what we were looking for to change our Gantt Chart in Excel!
Hi
I have been using the dynamic charts, they are cool, indeed.
Also, I use the Multile Time Scale chars described here:http://peltiertech.com/WordPress/plot-two-time-series-with-different-dates/.
I am not able to combine the two to get a dynamic chart AND multiple time scale in the same chart.
Any advice pls ?
@Josef
Can you post a sample of your data for us to see what going on, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Sample data is here:
https://hotfile.com/dl/168289539/fba040a/Time_Series_With_Different_Time_Scales.xlsm.html
Thanks for your attention. Josef.
I don’t know why, but I have not been able to make the offset functionality work. When i attempt to use the Offset function, I get an error message, “That function is not valid.” I am using Excel 2010. Is there something that I need to do to set the environment?
Super helpful post!
Thank you.
Chandoo,
Wonderful site.
Is it possible to do this for graphing on a scatter plot?
Thanks!
i want to make dynamic chart,in that rather than rows,but columns are going to get increase,row no. are fix eg. row no. would be 1 to 23 (hour) and column would be month names, and each month new column is added and chart needs to get updated with last 10 months values.
your any help will be appreciated.
[...] Chandoo’s article on this site: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/ [...]
Hi,
I have a chart linked to name range X. X is defined using OFFSET. In my dashboard chart throws an error “A formula in this worksheet contains one or more invalid references”. I want to suppress that message since I know my offset function doesn’t have any data to show and Formulas are totally OK !
[...] Another tip that I learnt today. When plotting a graph, the data that is to be plotted must be linked to the graph. What happens when the range of the data changes in length? You need to be able to plot using a dynamic range. [...]
I was trying to use dynamic ranges for my charts and kept getting ”That function is not valid” errors. None of the other web sites I read mentioned the need to have the sheet name in the reference (e.g. Sheet1!MyRange). Thank you so much!!
I believe I have this working “as advertised”. I am, however, using a “rolling” time period. The problem comes when I delete the first row of the range. I get a message “A formula in this worksheet contains one or more invalid references.”
Here’s the definition of the named range: =OFFSET(Sheet1!$D$3,0,0,COUNTA(Sheet1!$D$3:$D$19),1)
D3 is where the data begins. Once row 3 is deleted, the above error appears. Any other row is deletable and I can add data okay.
I need this because the first row of the data “falls off” as I ad a new “last row”.
Any ideas – Am I doing something bad wrong?