Interactive Excel Charts

Using interactive Excel charts in your dashboards and reports allows the user to pick and choose what they want to see.

This means you only have to build the report once and then the user can easily create their own view instead of you having to create multiple permutations of the same report, customised for each user. Happy days.

Download the Workbook

Interactive Excel Charts with Slicers – Excel 2010 Onwards

In Excel 2010 you can use Slicers to quickly and easily create interactive charts like the one above.

Now, I love Slicers but they have got some downsides:

1. They’re not available in Excel 2007 (or earlier…in case anyone still uses Excel 2003)
2. They take up a lot of space, and while I could make the Slicer in my example above smaller, I still can’t get it as compact as the next example.
3. They filter data in PivotTables (and Tables in Excel 2013), so you are limited to using Pivot Charts, or you have to build another manual chart table if you want to use regular charts.

Interactive Excel Charts with Check Boxes – All Versions of Excel

In all versions of Excel we can use Check Boxes (and other types of Form Controls) to enable filtering in our charts.

It takes a bit more set up but it also comes with more flexibility than Slicers offer.

To create an interactive chart using Check Boxes:

1. Double the Data. To create this chart I need my original data table plus another table that actually feeds the chart. The second table tests which check boxes are selected and only displays the data for those check boxes:
   

   

   In the image above we can see column I is displaying #N/A errors because the South check box is unchecked. More on building the second table for 'Chart Data' in step 4.

   Tip: your 'original data' table could be created using a PivotTable.

2. Insert your check boxes. You’ll find them on the Developer tab (you may have to enable the Developer Tab first).
   

   

   After selecting the check box from the Form Controls simply left-click and drag to draw them onto your worksheet, then right-click to edit the text and give each one a name.

3. Set the cell link for each Check Box. This is a cell on your worksheet that captures whether the Check Box is checked or unchecked. To set it right-click > Format Control. I’ve linked mine to cells H27:K27 and you can see below that the North check box is linked to cell H27, right above the North column of data for my chart:
   

   

   The Check Box returns TRUE in the Cell Link cell when checked, and FALSE when unchecked.

   We can use the value in the Cell Link cell to control what data is visible in the chart by referencing it in a formula.

4. Write the Formula for your Chart Table: I used an IF formula in columns H:K. For example, in cell H29 I have the following formula:

   =IF(H$27=FALSE,NA(),B29)

   It tests whether cell H27 contains FALSE. If it does it returns #N/A (using the NA() function), and if it doesn’t it grabs the value from the Original Table.

   

   Tip: we use NA() in the formula to return #N/A if the check box is unchecked, because #N/A’s don’t display a line in the chart. If you were to replace NA() with zero or blank you would have a straight line across the bottom of the chart.

   Note: if you’re using a column chart you can use blank or zero instead of NA() since you don't have the line problem. In fact, your formula could be simplified to =B29*H$27 since multiplying anything by FALSE is the same as multiplying by zero and multiplying by TRUE is the same as multiplying by 1.

5. Insert Chart: now that you have built your Chart Data table you can go to the Insert tab of the ribbon and insert your chart.
6. Position the Check Boxes: You can put them anywhere but in this example I chose the bottom of the chart area and used Shapes to manually create a legend beside each Check Box.
   

   

Don’t Stop There

I’ve shown you how to create one interactive chart but why stop at one. You can use this technique to build multiple charts, or even a Dashboard, all linked to these Check Boxes, or Slicers. I teach these techniques and more in my Excel Dashboard course.