Display One Chart Dynamically and Interactively
by Jon Peltier
Thursday, November 6th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Once again, I rely on Chandoo for inspiration for a charting tutorial. In Select and Show One Chart from Many he shows how to let a user choose which of several charts to show, in an interactive display. He uses data validation to enable the user to select from a finite list of charts, and uses a little-known image linking trick in Excel to show the selected chart. Basically, selecting an item changes a dynamic range so it refers to the range beneath the selected chart, and the picture is linked to this dynamic range.
This linked-picture trick is related to Excel’s Camera tool, which means two things. First, it’s pretty cool. Second, using it more than a few times will consume resources and slow down the workbook’s response. If you are linking to one picture of many, this is the way to do it, but if you merely want to show different set of data in your chart, it’s more than you need. In this post I will show you an easy and resource-friendly way to achieve the same effects in your dynamic report.
The Report Layout
I’ve set up my report as shown below. Cell F2 contains the selected quantity to be plotted, and I’ve named the cell “WhichChart”. The original data is in B17:E25, and a second data range linked to the first is in B28:E36. The borders around parts of the range B28:E36 show the chart source data range of the working chart, which I will discuss shortly.
One or both of the data ranges can be moved to a different place on this sheet, or onto another sheet, so that the focus is on the chart itself.
Data Selection
To make the selection of a chart more flexible, I changed the data validation list source of cell F2 (“WhichChart”) so it refers to the range C17:E17 rather than a hard-coded list (see the dialog below). This way, if I change one of the headers, I don’t need to update the data validation list.
I generally prefer using a dropdown control or a listbox. A dropdown always shows the drop down arrow icon, so the user knows he can choose something even when the cell is not selected. A listbox shows all options without having to go to all the trouble of dropping down a list. But data validation is pretty easy to use, so it’s really a matter of choice.
The Dynamic Chart
I then constructed a chart that contained all of Chandoo’s data and the formatting from Chandoo’s three charts. I used B18:B25 for the category (X-axis) labels, C17:E17 for the series names, and C18:E25 for the series Y data. To make the chart title dynamic, I selected the title, typed = in the formula bar, then selected the range C17:E17, and pressed Enter. The chart title then displayed the formula =Report!$C$17:$E$17. When multiple cells are selected like this, Excel concatenates the cell values, inserting a space between non-blank items.
The Dynamic Data
The second data range is linked to the original data, with a twist. It compares the column header to the selection in F2 and shows or hides the column as appropriate. I selected the range C28:E36 with C28 the active cell, entered this formula, then pressed Ctrl+Enter to enter the formula into the entire selected range:
=IF(C$17=WhichChart,C17,"")
This hides the headers and Y data for the non-selected column of data. I changed the source data of the chart so that B29:B36 (purple outline) is used for the category (X axis) labels, C28:E28 (green outine) for the series names, and C29:E36 (blue outline) for the series Y data. I also changed the title link formula to =Report!$C$28:$E$28. The outline colors correspond to the colors that Excel highlights these ranges when the chart’s plot area or chart area is selected. When Sales is selected, the data range looks like this (with only the Sales column displayed):
and the chart looks like this:
Final Touches
This isn’t quite perfect yet, but it’s easy to finish. First, apply a custom number format of 0;;; to the data labels in the chart, so the zero values (corresponding to the blank cells) are not shown. Second, format any one of the series in the chart so that it has an overlap of 100, so that all columns overlap, and there is no horizontal space wasted for columns with no data. Here is the finished chart:
Download this dynamic chart (in a zipped workbook).
Related Posts:
- Interactive Multiple Line Chart
- Dynamic Chart with Multiple Series
- Easier Interactive Multiple Line Chart
- Link Chart Text to a Cell
- Physics Lesson
- Interactive Charts with Checkboxes and Formulas
- A Linked Table to Sort Data for Charting
- Sample Parallel Coordinate Chart
- Quick VBA Routine: XY Chart with Axis Titles
- Dynamic Chart Source Data
Posted: Thursday, November 6th, 2008 under Dynamic Charts.
Comments: 34
Comments
Comment from Robert
Time: Thursday, November 6, 2008, 11:42 am
Jon,
very nice trick. Thanks for sharing.
I found an Excel 2007 oddity though. The technique itself is working fine with Excel 2007 as well. But referring to line 28 in the chart title has a strange effect: It works fine for the first item, but shows ‘320 Expenses 208′ for the second and ‘320 112 Profit’ for the third, i.e. the title of the columns accompanied by the values in the last(!) row of the other two data sets.
If you are using a textbox instead of the chart title, Excel 2007’s behavior is different but odd again: ‘Sales’ is displayed correctly, for the other 2 columns the text box seems to be empty.
You can easily resolve this by linking the chart title to input cell F2.
What do you think? Another bug in Excel 2007?
Comment from Jon Peltier
Time: Thursday, November 6, 2008, 11:52 am
Hi Robert -
So Excel 2007 is making things interesting again. I’ll have to take a look. you’ve applied SP1, haven’t you? That service pack fixed a lot of little annoyances with charts.
Comment from Robert
Time: Thursday, November 6, 2008, 12:05 pm
Jon,
yes, I have Microsoft Office Professional 2007 and 2007 Microsoft Office Suite Service Pack 1 (SP1).
I guess this is an ‘unfixed little annoyance’…
Comment from Jon Peltier
Time: Thursday, November 6, 2008, 12:29 pm
Robert -
In the spring, we’ll see another service pack, so the annoyance factor might go down. of course, SPs only reduce total annoyance incrementally, and you have to wait for a whole new version for large changes in annoyance. (Note that I didn’t say “large reductions”: the change from 2003 to 2007 was a “large increase”.)
Comment from Andrew
Time: Thursday, November 6, 2008, 4:24 pm
I also tried the Chandoo version and I wasn’t able to link a picture to a cell in 2007.
I have SP1 and I tried to do it in 2003 and it worked just fine. Is there a special trick for linking a picture to a cell on 2007?
Comment from Jon Peltier
Time: Thursday, November 6, 2008, 4:41 pm
Andrew -
I just tried, using the tried and true technique that has worked since Excel was invented in the late 1800s, and I could not link a newly inserted picture to a range of cells.
Here’s the two versions of the trick:
1. Use the camera tool to copy the range beneath a chart and then paste it.
2. Select and copy the range beneath a chart, then use Paste Special > As Picture > Paste Picture Link.
Both of these result in a picture which has a link to a range. By the way, it’s not necessary to use a single cell for this. You can use any contiguous rectangular range.
Comment from Simon Wilson
Time: Tuesday, December 9, 2008, 7:07 am
I have a similar but different requirment in that I’m trying to build an auto exceptions report based on RAG status of KPIs.
So if the RAG status is R or A, I want it to appear in the report, if it’s G, I don’t want it to appear.
The graphs are already built in a separate worksheet named “graphs”, and I want them to appear in another called “Exceptions Report”
Any ideas?
Comment from Jon Peltier
Time: Tuesday, December 9, 2008, 8:53 am
Do you mean you have separate charts with only one series, or do you want to be able to control which two of three series appear in a single chart?
The first of these requires a different approach altogether, while the second is covered on my web site in Chart by Combo Box or Checkbox.
Comment from Clay
Time: Thursday, May 21, 2009, 9:54 am
Love the post – been looking for a solution like this. I found I don’t even need to NAME a range. Just use “{=sum(if(}” array formulas in the dynamic chart areas. Catch the column and the year to select the appropriate value. Easy peasy.
Comment from Jon Peltier
Time: Thursday, May 21, 2009, 10:56 am
Clay – Thanks for your alternative. Excel offers many ways to skin a cat. Some people will shy away from using array formulas because “they’re so slow”, but if you have only a small range, there’s no worry about performance issues.
Comment from Brian
Time: Wednesday, June 10, 2009, 1:17 pm
How would you use this in relation to a stacked bar 100% chart?
Comment from Jon Peltier
Time: Wednesday, June 10, 2009, 2:52 pm
Presumably you know which data you want to show in the chart based on the selected item in F2. There would be more than three columns of data in rows 17:25, and the range to populate the chart (rows 29:36) would show more than one set of data. I’d stack all of the data, use a formula that placed either zero or the data value into the display cells
Comment from derek
Time: Friday, June 12, 2009, 2:13 pm
It occurs to me that Simon’s problem could be addressed by the trick Chandoo described, of using Autofilter to display charts. Instead of the chart name being the trigger for display of the right rows, the RAG status can serve that purpose.
Comment from Jon Peltier
Time: Friday, June 12, 2009, 3:16 pm
Derek – That’s a good idea.
Comment from Alan
Time: Monday, June 15, 2009, 7:09 pm
Hi.
I’m just new to this method for displaying data and experimenting a tad, – and learning a lot!
My question is, if I’m placing my own data in the appropriate ranges, how do I display 2 decimal places in the data labels?
I’m imagining it’s particularly easy, but I just can’t seem to do it…
Cheers,
Alain
Comment from Alan
Time: Monday, June 15, 2009, 7:17 pm
No wait. It was as easy as I thought. For some reason it only worked on my third or fourth attempt though…
My question now is, does the custom format on the data restrict the possible formatting on the labels?
Comment from Jon Peltier
Time: Monday, June 15, 2009, 9:06 pm
Alan -
When you add the labels, they pick up the formatting of the cells containing the values, and the Linked To Source option is selected (checked). You can format the labels independently, though. When you change the label formats, the Linked To Source option is unselected (unchecked). With this setting unchecked, you can change either format without affecting the other. Check the Linked To Source option and the labels will again assume the formats of the cells.
Comment from Krinsom
Time: Monday, October 19, 2009, 1:19 pm
Hi Jon, very good post.
But what happen if I have negative values on my table?
If I apply the 0;;; cell formatting it simply isn’t shown.
Any tip?
Cheers
Krinsom
Comment from Chandoo
Time: Monday, October 19, 2009, 1:22 pm
@Krinsom… try the formatting code 0;-0;;
Comment from christian
Time: Friday, April 9, 2010, 9:11 am
how do i create a dynamic chart from just one cell
this cell is updating every .5 sec so i want to plot its values dynaically in excel on a chart
would anyone know how to do this…
thanks
christian
Comment from Jon Peltier
Time: Friday, April 9, 2010, 11:26 am
If you are just displaying the one value, make a chart from that cell. The bar will get longer and shorter as the data changes. Enter reasonable values for the axis scale min and max, so the bar is all that changes.
If you want to display the history of that value, you need a mechanism to save the values the cell has had. This usually means an On Time procedure that runs regularly, checks the value, and if its different, save the new value in a list. The chart updates to show the new value plus all the old ones, or the new one and the N last old ones.
Comment from Mira
Time: Tuesday, April 27, 2010, 3:18 pm
Is there any way to make the data validation arrow appear all the time, without having to click on the cell first (Cell F2 in the example)? I know if you use the combo box then the arrow appears permanently, but can you also do it for data validation?
Comment from Mira
Time: Tuesday, April 27, 2010, 3:19 pm
Sorry, instead of “data validation arrow” I should have said the “list arrow”
Comment from Jon Peltier
Time: Tuesday, April 27, 2010, 10:06 pm
Mira -
As far as I know, and as far as I can tell, you can’t affect the appearance/visibility of the dropdown arrow used in data validation.
Comment from Nate
Time: Tuesday, July 6, 2010, 4:43 pm
Hi Jon,
Thanks for this article, it’s very helpful. I ran into one little issue though, hopefully you can give me some advice to resolve it.
When I switch between graphs, sometimes I have data for 14 bar graphs in the same chart, sometimes I only have the data for 4 bar graphs. Since I’ve selected a range of cells, there are 10 empty spots on the x-axis of the chart.
Do you have any tips to get rid of these empty spots on the chart, so that the 4 bar graphs can be distributely evenly?
Comment from Jon Peltier
Time: Tuesday, July 6, 2010, 9:02 pm
You need to combine this technique with the dynamic charting approach such as those in Dynamic Charts and in Dynamic Chart Review.
Comment from Bijal
Time: Sunday, July 11, 2010, 10:25 pm
Hi Chandoo,
Thanks so much for this really helpful technique. I want to display my charts using this technique. However i have one complexity, instead of selecting form just one drop down list, i want to use 2 drop down lists. E.g. In the first one i want to select the region, e.g. Sri Lanka and the inthe 2nd drop down i want to select what to show E.g. Sales, Volume, EBIT etc.
I tried If AND formula with indirect, but doesnt seem to work.
Any thoughts on how i can do this.
Thanks Again.
Bijal
Comment from Jon Peltier
Time: Monday, July 12, 2010, 10:48 am
Bijal -
In your data (B17:E25 in my example), add another row for the region. The existing data validation dropdown can be used to indicate Sales, Volume, EBIT, etc. Add a new data validation dropdown for region and insert a row in the data range with these regions. Each column of data then has two headers: Variable and Region.
Adjust the formulas in the lower table from this:
=IF(C$17=WhichChart,C17,”")
to this, which tests for two variables:
=IF(AND(C$17=WhichChart,C$18=WhichRegion),C17,”")
The rest of the protocol is the same.
Comment from Veena
Time: Monday, July 19, 2010, 9:41 am
Ques: i have 17 charts already made in one sheet. But i want to view only one chart at a time. I want a drop down box so that i can select which chart is to be displayed. please help me.
Comment from Jon Peltier
Time: Monday, July 19, 2010, 12:35 pm
Veena -
If you use my approach, you replace many charts with one chart that has many series. The dropdown lets the reader select which series to display, and it seems like a different chart.
Comment from Veena
Time: Monday, July 19, 2010, 1:02 pm
Thank you Jon. But do you have any other alternative.
Comment from Jon Peltier
Time: Monday, July 19, 2010, 10:02 pm
Veena -
I guess there’s Chandoo’s approach, in his post that I wrote about, that I offered this alternative for.
Comment from Veena
Time: Wednesday, July 21, 2010, 2:29 am
Thanks Jon, It worked..
But the problem m facing after using your approach is than
Suppose I plot 5 series. After using this approach i should be able to see 1 series at a time. I am able to do that but the graph is also showing 1 more series with all values as zero. I dont want t graph to show blank series as zero.
please help!! :(
Comment from Jon Peltier
Time: Thursday, July 22, 2010, 1:19 pm
Veena -
Select your main series (the one you want to show) and look in the formula bar. It looks like this, with four arguments I’ve replaced by A, B, C, and D:
=SERIES(A,B,C,D)
A stands for series name, B for X values, C for Y values, and D for plot order). A and B are allowed to be empty. I suspect you’ll find that B is empty, and C is the Y values you need to plot. Then select the other series. I’ll bet it has an empty B parameter, and its Y values (C) are really the X values you wanted to use in the other series.
Delete the unwanted series, then select the wanted one. Put your cursor in the series formula where B should go, and select the range with X values. Press Enter and you’re good to go.
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.