How to create interactive charts with radio buttons and a scroll bar

Occasionally you need to deal with years and years of data across multiple data sets.  Seeing all of that data in one chart is distracting and difficult to read.  One of the best ways to get round that (IMHO) is to add some simple interactivity to your chart.

Obviously this is a static picture, but you can add scroll bars and check boxes to your excel sheet very easily.  I originally learned how to do the check boxes from Peltier Tech, the scroll bar I worked out for myself after seeing how the radio buttons worked, though I am sure he’s got the how to on there as well.

You can download the sample worksheet here.

Step 1: Prepare your data

First you need to work out what time range you want the chart to show.  I chose ten years, but you can set (almost) any time range you want.

My original data looks something like this:

As you can see, there’s a lot of data.  Too much for one chart.  So on a second sheet, I created a new table.  In the downloadable example it is called “Formulas”.  I left columns A and B blank as we will need them later for the interactivity.  In cell C1 I put the formula:

=(OFFSET('Interactive chart'!C1,0,Formulas!$A$7,1,1))

The offset function allows you to use one cell as a reference point and then move X number of cells across and / or down and then copy / paste the contents of the new cell location.  We are going to use this calculation with the scroll bar.  Every time the scroll bar moves one across the offset function will then display the next cell across and so one.  In the calculation above “Interactive chart” is the worksheet with my original data. For the calculation to work you need to enter the following values:

=(OFFSET(Original location, number of cells down, number of cells across, height and width))

I’ve used “1” as the height and width.  The number of cells down is zero as the scroll bar will be horizontal.  The number of rows across will be decided by the value in cell Formulas A7.  I’ll show you how to link your scroll bar to this cell in step 3, so that when the scroll bar moves your table updates.

Once you’ve added this formula to cell C1, copy and paste the formula across the columns for the required number of years.  As I said earlier I chose 10, so my table stops at column L.

In cell C2, we are going to do something similar, only we want to incorporate the results of the check boxes.  So we are going to add an IF statement to the beginning.

=IF($B2="",NA(),(OFFSET('Interactive chart'!C2,0,Formulas!$A$7,1,1)))

This IF statement says that if the cell directly before it is blank, then return a N/A# value.  If not, perform the offset function.  As usual the $ signs stop the reference from changing when you copy and paste the formula.  This is important for values like the slider bar reference and the check box reference.

Once you’ve added this formula to cell C2, copy and paste the formula across the columns for the required number of years, and down the rows to complete the table.

We just need to add one more set of calculations, starting in cell B2.

=IF(A2=TRUE,'Interactive chart'!B2,"")

Cell A2 will be the reference cell for our first check box.  If it is selected, A2 will return a value of true, if not it will return a value of false.  This means that if the first check box is selected, the calculation will show what is in cell B2 of the original worksheet.

Once you’ve added this calculation, copy and paste it down column B to complete your table.

Step 2: Add the checkboxes

Go back to the original worksheet.  If you don’t have the developer toolbar visible, you can use this blog post to make it visible.  Then, click on Developer > Insert > Form controls > Check box.

The cursor will change to a black cross.  Draw your check box on the worksheet next to where you are going to position your chart. Then right click to edit the text.  I deleted the text on mine as I want to add coloured labels in the cell next to the check box.  Once you’ve edited the text, right click again on the check box and choose Format control.

One the Control tab of the pop up menu box that appears, type in your cell reference.  In my example (used in the calculation above), I want it to be cell A2 of the second worksheet “Formulas”

 

You can then press the OK button.  Repeat for the other rows of your table, one check box and cell reference for each row.

Step 3: Add the scroll bar

To add the scroll bar, click on Developer > Insert > Form controls > Scroll bar.

Draw the scroll bar on your worksheet (like you did the check boxes).  I put mine below where I wanted to put the chart.  Once drawn, right click on the scroll bar and choose Format control.

Add the cell reference as before.  Normally the current value is 1, with a minimum of 0.  The maximum is calculated by subtracting the number of years you want to show from the total number of years in your table.  For example, if you have 23 years in your table and only want to show 13 at any one time, the maximum will be 13.  Finally the incremental change is usually 1. Once you’ve added the values you can then press the OK button.

Before going any further, test your check boxes and scroll bar by checking to see if the values in your table on the second worksheet change when you select or deselect the check box or you move the scroll bar.

Step 4: Add the chart

This is probably the easiest part IMHO.  Simply ensure all of the check boxes are selected and then highlight the chart on the second worksheet.

Excel will automatically create the chart for you.  I then right clicked the chart and moved it to the first worksheet, simply for aesthetic reasons.  I positioned it next to the check boxes, labels and scroll bar.  I deleted the grid lines and legend (my check boxes and custom labels create a great legend), I also added a data label to the last data point of each series and finally tweaked the formatting a little to match my custom labels.  When you select and deselect the check boxes and / or move the slider bar the chart should automatically update.

 

Step 5: Additional extras

You’ll notice that my chart has an average line.  If you’ve downloaded the sample worksheet here, you’ll also notice an extra line in my second table. It calculates the average of the values, ignoring any #N/A values, and updates as you interact with the check boxes etc.

To do this add the calculation to the last row of your table, in my example it is cell C6:

=AVERAGEIF(C2:C5,"<>#N/A",C2:C5)

Copy and paste that calculation across your table, create a check box for it and finally add it as a data series to your chart and that is it.

Hope you find this useful!

+Alesandra Blakeston

Advertisements