How to cook a delicious dynamic chart that will have your boss drool

Dynamic charts are like my favorite food, Mangoes. They tempt, tease and taste awesomely. In this post, we are going to learn how to create a dynamic chart using check boxes and formulas. Are you ready for some excel chart cooking?

What our mouth-watering chart will look like when its done:

Ingredients:

Instructions for preparation:

First get your data. Make sure its clean and arranged neatly, like below, in the range B4:E11.

Since our data has 3 series (sales, profits and number of customers), we will take 3 check boxes and place them somewhere on our worksheet.
Insert check boxes from developer ribbon / forms tool bar (tip: show developer ribbon in excel 2007)

Now, we want the check boxes to tell whether to show or hide a particular series of data in the chart. So, link each check box to one cell, say C13, D13 and E13.

We will use IF formula to roast our data based on what the check boxes say. So, create a similar table and load it with IF formulas like this:
=IF(C$13,C4,NA())

Finally, make a chart with the data in this new table you created.

Put everything together and neatly arrange with your favorite colors and labels.

More recipes on dynamic charts:

Do you use dynamic charts?

I like dynamic charts a lot. They provide a wealth of information in a compact form. I use them whenever possible, especially in dashboards and analytical outputs.

What about you? Do you use dynamic charts often? What techniques do you use when implementing dynamic charts? Share your experience and tips using comments.

http://chandoo.org/wp/2009/08/27/dynamic-event-grouping-in-charts/

Sign-up for our FREE Excel tips newsletter:

Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

134 Responses to “How to cook a delicious dynamic chart that will have your boss drool”

Nice and simple.
Small suggestion: Sales and margins share a similar scale (or should share). However number of customers are of completely different order of magnitude. So, make the line chart use a Secondary Y-axis instead of sharing it with sales and profits.

I’m fairly new here and an amateur to boot so please bear with any stupid
questions.

I found this article very interesting and am trying to work my way through
making the chart myself from scratch (I learn a lot this way!) – I am
completely stumped on how you got the font colors in the checkboxes to
change. Can you point me to where you have (possibly) explained this
in a different article?

Nice trick. My boss would be confused as to the chart being a stacked or a100% overlapping bar chart. I almost never use 100% overlapping because of this confusion. Though by toggling it is so obvious that they are not stacked, but print out a graph with profit and sales and the confusion would be there.
Keep up the excellent work!

Thx for the tip, I was a the moment building the same kind of graphics but didn’t think to use the check boxes. It’s clever.

However the “linked cell” (don’t know the name in the english Excel version) has to use the True/False format. How can we change that ? I used a simple y/n (yes/no)format in front of a group of lines I want to hide or not. It is much more simple to use.

But I think the Tru/Falese format is automatically implemented in Excel and we can’t use something else except some trick involving macro ; and I can’t use it since I have to run the file on both Mac and PC.

I use dynamic charts a lot. I usually use a lot of named ranges. Earlier I used to use offset to create xValues but now a days I learnt the technique of using index instead of offset (as suggested by a lot of mvps – offset should be avoided wherever possible.)

However to import data from various worksheets I use offset. The various formulas I use are as follows to get an idea. e.g. The chart will have its own data worksheet. The data for various departments may be stored on different worksheets and pulled as per the dropdown selection.
1. To pull data on the data worksheet… =indirect(address(row(),column(),,,$A$1)) where A1 contains the name of the worksheet.
2. xValues = Index(Data!$D$1:$D$10,A1):index(data$!D$1:$d$10,A2) where a1 and A2 contain the indexes of selected months from the dropdown.
3. yValues1 = Offset(xvalues,0,A4), yValues2 =Offset(xValues,0,A5) etc.. again this comes from the categories you select.

@ Martin, Malachi
To create the overlapping chart, setup a chart with 2 Series
Right Click on one of the series and select Format Data Series
On the Series Options Tab, change the Series Overlap slider to 100% Overlap
Set the gap width to suit

@ Martin, Malachi
In Excel 2003
To create the overlapping chart, setup a chart with 2 Series and create a Clustered Column chart
Right Click on one of the series and select Format Data Series
On the Options Tab, change the Overlap slider to 100% Overlap
Set the gap width to suit

@Anne… Thank you … Powerpoint is another beast. I do not any reasonable way to port this chart in to ppt with all the dynamism. You should try embedding the whole workbook in to ppt. That way, during the presentation, you can double click on the excel object to open the chart from excel directly and show the interactivity.

I just wanted to share that I used the ideas in this dynamic chart to create a comparison chart for our commissioning dept. There are 17 PCTs (health groups) in our region, so I have set up 2 charts – monthly and cumulative – so that they can choose up to 3 PCTs to chart and compare. They love it and think I’m fantastic!! Thank you Chandoo. I am enrolled in the latest school and hope I can wow them with more innovative ideas!

Thanks a ton Chandoo, had a presentation w/ my CRO this morning and used this technique to track large deals for the last 4 quarters, and yes you were right, they were drooling over it.. Keep on doing the good work, really appreciate it..

Hi, Chandoo. Thank you for this tip – it’s incredible! I’m using this with a clustered column chart. My series are fiscal years 2006-2010, with the months along the horizontal axis. I was wondering if there’s a way to eliminate the gaps for years that have been de-selected – for example, I may want to compare data from FY 2006 and FY 2010, and I’d like to see these next to each other in the chart; however, when I de-select FYs 2007. 2008, and 2009, I’m left with a chart that has a sizable gap between the two selected series.

Using Auto Filter works, but it’s not dynamic – when I re-select a fiscal year (say I now want to show 2006, 2008, and 2010, so I re-select 2008), I have to re-select the year in the Auto Filter’s drop-down boxes. Is there a way in Excel 2007 to have these rows hide and unhide automatically when you select/de-select a check box?

Hey chandoo,I work for Nabler Web Solutions and i was thinking of using the same in my next report.
I have tried making this dynamic chart, but i am not getting any success in that. Every time i try to make it the bars in my chart changes. Can you tell me how have you keep the length of those bars fixed.

you need to set axis min and max to fixed values. Select vertical axis, goto format axis (right click) and then choose fixed for min and max of axis and enter 0 and a large value (based on your data). This should do the trick.

I am new to your website and I love excel but get very discouraged when it comes to using formalas and manipulating different functions. But this one was very beginner friendly. Now, I am excited to learn more. Thank you!

@Vetrina
Don’t be discouraged by formulas
If you understand that 20+10=30
Then a Cell will have =20+10 and display 30
And you can link cells =B2+B3 will simply take the values from B2 and B3 and add them

eg:
=Sum(20, 10) will add up 20 & 10 and return 30
=Sum(B2, B3) will take the values from B2 and B3 and add them
=Sum (B2:B10) will take the values from the range B2:B10 and add them

Dynamic charts are awesome! But, is there any way to add individual data labels to this chart? I have created three histograms and three line graphs, but when
I am adding a data label, excel is showing #N/A at the bottom which is not looking good. So is there any way to have individual data labels ?

Thanks for sharing it. Very simple and easy to understand. I used this technique and it was very impressive. One question, is there any option by which we can use dynamic chart in ppt??
Also had the same doubt that Abhishek Sinha asked about the data labels..

I just created these charts 100% because of this site, and although no one has told me yet, I am totally a rock star for doing this (and I even impressed my husband!). I’m a Business Analyst at a hospital and this has taken my work to a whole new level….thanks so much for this blog and these wonderful posts and comments! (although I could use a bit more detail in the implementation details since I’m not as technically capable as others are on this site…)

I am so happy to have found this site! Thank you Chandoo. I have a question: My data has daily prices, for multiple years, of different metals. I have recreated the dynamic chart explained above, but now I’d like to be able to select specific year, month, or number of days, and have the charts update instantly. Please help! Thank you.

I use Excel on a day to day basis; so I find your tips very valuable. The tutorial is so easy to follow and the outcome is fantastic. I had a great time trying it out. Thanks for these tips; I will definitely apply the things I learned from your site to make awesome and professional looking report.

Thank you for giving us this project and to all of the others who explained the extra steps involved (@Hui.. & @KateB). I will surely amaze my colleagues as soon as I get a chance to use this technique at work!
Just to add a little help for any who come after me: The check boxes will not work unless you are out of “Design Mode” in Excel2010. That took me a few minutes to figure out.

Hey Chandoo, such charts makes your work alive. But I have a confusion.
what to do if we want to compare sales of 2 quarters in this same data. For example, sales of November and march for year 2003. Rest all is same.Then how will we link two cells with one check box.
Please reply

Hi Chandoo, I’m really at my wits end! I did up a dynamic chart similar to yours, but everytime i checked the third option (ie. the line graph), the plot area will auto resize and shrink. when I unchecked, the chart will take it upon itself to expand itself to presumably “optimize” the plot area. But yours does not seem to resize at all. I have tried twiddling with object positioning properties, horizontal axis options, horizontal axis alignment auto refit is grayed out, etc…

Thanks Hui for the reply! Well actually I accidentally solved it when I remove the legends since I can use the checkboxes as my legends (better that way too). But in any case, I have uploaded in the link below, so you can have a look at the problem if you want.

Each checkbox is currently formatted to show the same type of chart and i don’t see anything on the Checkbox options to change that. if you click on the chart itself and then click “Change Chart Types” and then click on Templates, you can see that there are 3 templates saved (2 column and 1 Line), but they don’t seem to be linked correctly as the line chart does not display.

Hi Chandoo,
Great little file – thanks!
I am just trying to manipulate it, but am having some issues. I am trying to break-down the sales and profits by each business unit (but keeping the year as well).
Any suggestions?

I am new to excel and have seen the above dynamic chart. When i followed the instructions i get graph only when i select profit but dont get graphs for sales and customers, please help me in learning how to get graphs when select/tick on other options aswell.

Damn Awsome!!
Thanks man…I feel so awsome today…actually was bored sitting in front of the Idiot box, so started surfing and stumbled upon this..
One more thing ..Can I make this site invisible to my bosses? plz…:P

Hi, I wanted to know how I can upload this site on to a website. Google gives you an embed code so we can put that in a report and publish it. But how does it work for this particular chart on top using excel.
(I regularly use interactive charts for stories so need to figure out a fast and easy way of making them work)

Thanks for the tutorial. It works great and looking forward to showing off. The instructions were easy to read and implement.

Have a couple of questions if anyone else has run into the same issues:

1.) I have two columns and one line in my chart. All of my data is based on the calendar year, so data today forward does not exist yet. Therefore the line runs on the X axis for the remaining year. Is there a way where no data (=IF populates zero) values do not appear on the chart? To describe better, my line illustrates the actual values from JAN – JUL then falls to zero and runs the X axis for AUG – DEC. I’d like to have nothing there until the actual data is populated. I hope this makes sense what I am trying to describe.

2.) In my example, the line on the chart is based on data for a particular region. I have three regions that I’d like to use the dynamic chart with check box feature (my checkboxes are not in the printed dashboard, but are in the worksheet and data entry section on worksheet.) However, the legend shows all items even when not checked, therefore sort of “junking up” the real estate and showing the audience that there are other items available but aren’t being shared with this particular audience. Is it possible to have the legend only visible as it relates to the check box? For example, the legend would only show the line that is checked?

I’ve rambled quite a bit. Thanks for reading and hope to hear some suggestions from all the great Chandoo users. Good evening!

In this scenario profit will never be higher than Sales… What if we have to deal with the data like Target V/s Achievement where achievement can be higher than target or vice versa? This can make one of our scale (target or Ach.) hide behind the other….. Kindly guide how can we tackle this situation…. I was looking for any transparency sort of technique to implement in here but no success yet…

[…] chandoo.org as guidance on making an interactive graph. great stuff actually. here is the url: FREE Excel Dynamic Chart Template – Download – Dynamic chart shows values based on user selection | … everything is working great but i cannot figure out how to graph the Line portion on top of […]

@Anita
Either case is ok if you are using the formula in a single cell
Where the $ sign comes in is when you go to copy the formula
In the case of =IF(C$13,C4,NA())
If you copy that down the next cell down will show
=IF(C$13,C5,NA())
The $ sign locks the reference to Row 13 to Row 13
With the second formula =IF($C$13,C4,NA())
If you copy that down you will get
IF($C$13,C5,NA())
in the next cell down

A similar thing happens if you put the $ sign in front of the C eg $C$13
That locks the Column when you copy across

Excellent, amazing, outstanding tutorial, thanks a lot!! I am sure that everyone that shows this interactivity will get surprised with ooh and ahhs. Thanks a lot for this fantastic and easy-to-follow explanation

@Daniel
Yes
There actually called Option Buttons, not Radio Buttons
With Option Buttons you can only have one selected at once
But you can group them using a Group Box so that you can have different groups of Option Buttons and each group can only have one Option Button selected
Refer: http://chandoo.org/wp/2011/03/30/form-controls/

Hello, nice article. I have a question, when I try to do it i can’t erase the nº customer line when is not selected, it always appears in the bottom as if the result is zero. How can I make to make it dissappear at all when not selected? Thank you in advance. Great web, following!

Thanks a lot for the tip. It is very useful.
I would like to know only one thing.. when I create the chart and uncheck sales..I get N/A in place of the sales value.
What can I do so that I will avoid the N/A?

Is there a way to do this with a pivot table. This works great for looking at a complete set of data. I want to setup a workbook that will be added to periodically. I have setup of pivot chart that allows you to select by check box a number of selections but it always wants to sum everyting and chart one line.

Dear Chandoo,
First of all hats off to you man. You are doing marvelous work. I am learning from your site.

Regarding this dynamic chart, I have a small question if that can be done in power point? You know all analysis and data are presented mainly in power point slides and I was thinking if it could be possible to execute same charts with checkboxes in power point that will be great.

I want to make the charts appear separately- next to each other.
If I am selecting sales the chart must appear and now if I am selecting Sales and Profits both, both the chart must appear next to each other.