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
Enter your email address below to download the sample 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:
- They’re not available in Excel 2007 (or earlier…in case anyone still uses Excel 2003)
- 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.
- 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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
Rita
Hi Mynda, Thank you! I have managed to improve one of my reports with this neat trick. 🙂 The only thing I cannot figure out if there is a way to keep the chart on screen while I scroll down in the table (I have a long table on the left and the the chart on the right.) I tried to investigate and it seems it should be solvable by adding some VBA into the sheet but none of the examples I found actually worked. Do you have maybe an idea how to solve this?
Thank you in advance!
Mynda Treacy
Hi Rita,
That’s a nice idea but I don’t have any VBA code I could point you to, sorry.
I prefer to make the table scrollable so you don’t have to use VBA. I teach this scrollable table using form control scroll bars in my Dashboard course. You can see an example in my le Tour de France Dashboard.
Mynda
Mark
Hello Mynda, in your opinion is there a downfall in using (“”) vs ,NA in the formula? Are there any pros or cons in using one above the other?
=IF(H$20=FALSE,NA(),B22) will show #N/A in cell B22
=IF(H$20=FALSE,(“”),B22) will show a blank in cel B22
Mynda Treacy
Hi Mark,
The downfall is when you’re using this data in a line chart. In a line chart a “” will display the line on the horizontal axis because Excel reads “” as zero. The only way to hide this line is to return #N/A as these errors don’t display a line in the chart.
If you’re using column charts then you can use “”.
Mynda
Mark
Thank you Mynda.
Catalin Bombea
Hi Mark,
There is a major difference between those: #N/A values are ignored in charts, empty strings are displayed as gaps, which in most cases is not a desired behaviour. Of course, there may be situations when you want to see all results, you have to choose the one you need.
Cheers,
Catalin
Mark
Thank you Catalin.
Joe
I like this idea but I’m wondering if it can be adapted when the data range is changing (let’s say it is a daily chart and there is additional data each day). Since slicers rely on pivot tables, they automatically resize. Any options?
Mynda Treacy
Hi Joe,
You could use Dynamic Named Ranges and use these as your chart references so they grow as the data grows.
Note: when you use the dynamic named range in your chart source you also need to prefix it with the sheet name e.g. =Sheet!your_dynamic_named_range
Kind regards,
Mynda