Interactive Charts with Checkboxes and Formulas

An interactive dashboard by definition includes mechanisms for users to control aspects of the display. There are many ways to accomplish this. In Interactive Charting by Combo Box or Checkbox I wrote about using checkboxes to do this. That used a very simple example, which I will explore further in this article.

There are a couple of ways to achieve this interactive control. There is an easy method that relies on worksheet formulas to alter the contents of the source data range of each series, and there is a more involved technique that uses VBA to programmatically adjust the chart’s source data. This post will describe the approach that uses worksheet formulas.

Checkboxes and Worksheet Formulas

Start with a column chart that includes all of the series you will allow the user to select from (A, B, and C in this example). Add a checkbox from the Forms toolbar for each series.

This works as well for a line chart, though the formulas have to be different for line and column charts.

The data range is shown below. The original source data is in J3:M8, and the three checkboxes link to the three cells in K1:M1.

There are two calculated ranges below the original data range. The category labels have simply been copied from the original range, but the other data is linked using worksheet formulas.

For the first calculated range, select K10:M15 so K10 is the active cell, enter

=IF(K$1,K3,"")
 

then hold CTRL while pressing ENTER to fill the formula into the entire selected range.

For the second calculated range, select K17:M17 so K17 is the active cell, enter

=IF(K$1,K3,"")
 

then hold CTRL while pressing ENTER to fill the formula into the entire selected range. Then select K18:M22 so K18 is the active cell, enter

=IF(K$1,K4,NA())
 

and again hold CTRL while pressing ENTER to fill the formula into the entire selected range.

If the Series B checkbox is unchecked, L1 is FALSE, so L10:L15 appear blank, L17 also appears blank, and L18:L22 contain #N/A.

The source data range for the column chart has been changed from J3:M8 to J10:M15. The data for Series B appears blank due to the “” in the formulas, which Excel treats as zero. The bars for Series B have a height of zero, so they don’t appear, and the legend entry for Series B is “”, so it doesn’t appear.

There is still a gap between the bars for Series A and Series C where Series B fits. There also is still a legend key for Series B, which is the unlabeled orange square between the green and blue squares. The legend can be hidden, but then another means is needed to identify the series in the chart.

We don’e want to use “blanks” in the source data for the line chart. Since the “” are interpreted as zero values, the line and markers for Series B lies along the horizontal axis, where Y=0. The legend entry for Series B displays no label, but the legend key still appears (the red bos and line).

For a line chart, use the range J17:J22 as the source data range. This uses #N/A instead of “” to indicate undisplayed values, so the line and markers for Series B don’t even appear in the chart. We still have that pesky unlabeled legend key, though.

It would be nice to use the same data range for both line and column charts, but the #N/A error does not preclude plotting a point in a column chart. The value is zero as in a line chart, but if you put value labels on the points, the result is a label that says “#N/A”. There is no fancy trick using number formats to hide the error.

Using the range with apparent blanks results in labels of “0″ at points corresponding to apparent blanks.

Using a custom number format of “0;;;”, we can suppress the display of any label except positive numbers (and errors).

What happens if the user gets excited and unchecks all of the boxes? The data ranges respond as expected:

And the column chart and line chart both show no data, with unlabeled legends.

CheckboxAndFormula.zip is a zipped workbook which you can download to illustrate this technique.

Checkboxes and VBA

In the next article, I will show how to use VBA in conjunction with the checkboxes to control a column or line chart with a single worksheet range and no formulas, while avoiding gaps for missing columns in the column chart and also avoiding  unlabeled legend entries.

Related Posts:

• Interactive Charts with Checkboxes and VBA
• Physics Lesson
• Gas Prices – Interactive Time Series
• Easier Interactive Multiple Line Chart
• Interactive Multiple Line Chart
• Chart Event to Highlight a Series