Interactive Chart Example-1

Did you know that you can make interactive charts in excel? It is pretty easy and once you learn how to, making interactive charts will be child’s play.

(We are going to use check-boxes for this tutorial. Please check our related post you are unfamiliar with excel form controls.)

Imagine that you are need to report your company’s sales and profit for past 5 years and you want to impress your manager with a simple to read, yet stunning chart. Than it is time to feed him/her some interactive chart.

For this example we’ll use a summarized set of data. Our summarized data looks like this:

If we use this data as it is, there will be no interactivity in it. Static data = Static chart. So we are going to need to put some interactivity to our data. One way to achieve this is through use a combination of check boxes and if formula.  Make a copy of this table to a couple of rows below and arrange 3 cells for key values. After you completed this step, new dataset will look like this:

Our formulas in related cells checks key cells(row10) in their column. If value is true it takes related years values (=C3), if value is false it will show #N/A ( NA() does that). By the nature of true/false values you don’t need to specify any condition in this formula since true and false are conditions themselves.

Formula in english: =if(check C10; get value of C3 if true; put #N/A if false)  – copy these formulas to other cells of same column in the table.

Now that all is set up, leave row10 values at TRUE for your table to have full data, select whole table and insert a column chart to anywhere you like in this or another sheet. We are going to make a combined chart so arrange  %profit serie as secondary axis (see Combining Two Charts into a Single Chart if you have trouble doing it). As another nicety we are going to show sales and profit on top each other to show better effect. For this to happen, right click on profit and from format series menu set series overlap option value to %100.

Your chart will look like this:

Since we want an interactive chart , we still have work to do. Put 3 check box under this chart and label them with series’ names. Than set their target cells as C10, D10, E10 respectively (see instructions in excel form controls).  See the connection?

Our check-boxes are going to update values of our key cells as true/false depending on checked or not. So related series will have data and shown on our chart or not:) Here is your finished interactive chart:

To check it yourself and see finished chart download Interactive Chart Example – 1

If you like this tutorial please share.