Excel Fox
10-05-2013, 05:52 PM
I recently got a request from a friend to help him create a dynamic chart that expands as data is added to the source. As I put it together, an onlooker said that it doesn't look as easy as you made it look. And I thought that was right. Even some of the experienced Excel users tend to consider this a not-so-easy task. On the contrary, I think it's one of the most easiest tricks to master.
This is how you do it.
Let's take a simple column chart. For extra effect, I've changed one of the 3 series in to a line chart also within the same chart group
1256
The source data should expand all the way from B3:E50 excluding the header row, depending on how many rows of data we have. So for example, if there are 10 rows of data, the source of the chart should only be from B3:E12. If there are 15, the source should automatically expand to B3:E17.
OK, now that we know what is expected, let's find out how this can be achieved.
Excel has something called a Dynamic/Defined Named Range (http://support.microsoft.com/kb/830287)
We will assume that the date column will be our key to identify how many rows there are in the source data. And we will give the name DateList to the source column.
Here's how you do it. Go to the Name Manager (http://office.microsoft.com/en-in/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMmanage_names_by_using_the_name_ manage) and create the defined name mentioned above as
=OFFSET(Chart!$B$3,,,COUNT(Chart!$B$3:$B$50),)
Now, create defined named ranges for the data columns with Workbook scope. To know more about scope of named range, read the Name Manager link posted above.
Column1=OFFSET(DateList,,1,,)
Column2=OFFSET(DateList,,2,,)
Column3=OFFSET(DateList,,3,,)
I've used Column1, Column2, Column3 here, but it is always better to give a more meaningful defined name than what I've used above.
I will assume that you already know how to create a chart. Now, create the desired chart with some dummy data. It should look like the picture above. Now select one of the series within the chart, and look at the formula bar. You should see something like this.
=SERIES(Chart!$D$2,Chart!$B$2:$B$5,Chart!$C$2:$C$5 ,1)
Replace this with
=SERIES(Chart!$D$2,'Dynamic Chart.xlsm'!DateList,'Dynamic Chart.xlsm'!Column2,1) where Dynamic Chart.xlsm is your workbook name.
Note the use of the apostrophe ('). This is necessary when using workbooks that have a space in their file name. Also, you can play around with the order of the series, by modifying the last argument (in this case, it is 1)
Repeat this for all the 3 series. You should now get what I've done in the attached file. Enjoy :)
This is how you do it.
Let's take a simple column chart. For extra effect, I've changed one of the 3 series in to a line chart also within the same chart group
1256
The source data should expand all the way from B3:E50 excluding the header row, depending on how many rows of data we have. So for example, if there are 10 rows of data, the source of the chart should only be from B3:E12. If there are 15, the source should automatically expand to B3:E17.
OK, now that we know what is expected, let's find out how this can be achieved.
Excel has something called a Dynamic/Defined Named Range (http://support.microsoft.com/kb/830287)
We will assume that the date column will be our key to identify how many rows there are in the source data. And we will give the name DateList to the source column.
Here's how you do it. Go to the Name Manager (http://office.microsoft.com/en-in/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMmanage_names_by_using_the_name_ manage) and create the defined name mentioned above as
=OFFSET(Chart!$B$3,,,COUNT(Chart!$B$3:$B$50),)
Now, create defined named ranges for the data columns with Workbook scope. To know more about scope of named range, read the Name Manager link posted above.
Column1=OFFSET(DateList,,1,,)
Column2=OFFSET(DateList,,2,,)
Column3=OFFSET(DateList,,3,,)
I've used Column1, Column2, Column3 here, but it is always better to give a more meaningful defined name than what I've used above.
I will assume that you already know how to create a chart. Now, create the desired chart with some dummy data. It should look like the picture above. Now select one of the series within the chart, and look at the formula bar. You should see something like this.
=SERIES(Chart!$D$2,Chart!$B$2:$B$5,Chart!$C$2:$C$5 ,1)
Replace this with
=SERIES(Chart!$D$2,'Dynamic Chart.xlsm'!DateList,'Dynamic Chart.xlsm'!Column2,1) where Dynamic Chart.xlsm is your workbook name.
Note the use of the apostrophe ('). This is necessary when using workbooks that have a space in their file name. Also, you can play around with the order of the series, by modifying the last argument (in this case, it is 1)
Repeat this for all the 3 series. You should now get what I've done in the attached file. Enjoy :)