Excel Dashboard Controls – Multiple Value Filters

How to create a multiple value filter control including a “select all / none” option with Microsoft Excel

If you ever built interactive Microsoft Excel dashboards, I am betting the ranch that there was at least one with some kind of interactive filtering features. For selecting one out of several categories you probably used a drop down list (i.e. a combo box). If you need a multi filter control (i.e. selecting several categories) a multi select list box or several check boxes are the way to go. We had that before: In one of the first articles on this blog I already showed different techniques of how to implement a multi-select control feature on Microsoft Excel dashboards (Approach with Caution).

So far, so good. However, what I didn’t discuss in that article is one obvious requirement you may have for a multiple value filter control on a dashboard: an option to let the user easily select all or none categories with one single mouse click. Especially helpful if you are having a lot of categories.

Today’s post tries to fill this gap: this article shows several options of how to implement a multiple value filter control based on check boxes, including an additional check box to easily select and deselect all categories. As always, the post provides all Excel workbooks for free download. 

The Challenge

Well, I guess I already sufficiently described the challenge in the introduction to this post: Create a multiple value filter control including an easy-to-use option for selecting all or none categories with one single mouse click. I.e. produce something like this:

A painfully obvious requirement, isn’t it? However, there is no built-in functionality of Microsoft Excel that allows doing this with ease. Unlike with Tableau Software. But I am running off topic.

Here are 3 different ways of how to produce a multiple value filter list including a select/deselect all check box using Microsoft Excel:

Approach 1 – Form Control Check Boxes

This is probably the easiest way of doing this using form control check boxes. Only 4 simple steps necessary:

1. Insert a form control check box for each category and link them to a continuous range somewhere in your workbook. Add another check box called “All” and link it to a cell too.
2. Define names for the ranges like “myActualFilter” for the range with the target cells of the category check boxes and “myCheckBoxAll” for the “All” check box.
3. Add the following VBA to a new module in the VBE:

   Option Explicit

   Sub UpdateCheckBoxAll()
       On Error Resume Next
       Range("myActualFilter").Value = Range("myCheckBoxAll").Value
   End Sub

   Sub UpdateSingleCheckboxes()
       On Error Resume Next
       Range("myCheckBoxAll").Value = _
                 Application.WorksheetFunction.And(Range("myActualFilter").Value)
   End Sub

   The code is pretty straight forward. The sub UpdateCheckBoxAll assigns the actual value of the “All” check box to the target range of all category check boxes. This way all other check boxes are selected or deselected. The sub UpdateSingleCheckbox uses the worksheet function AND to set the value of the “All” check box. If all values of all category check boxes are True, the “All” check box is set to True. Otherwise it is set to False.

4. Finally assign the sub UpdateCheckBoxAll to the “Select/Deselect All” check box and the sub UpdateSingleCheckboxes to all other check boxes.

That’s it.

Here is an example workbook including this technique:

Download Multiple Value Filter using Form Checkboxes (Microsoft Excel 2003, 86.5K)

Approach 2 – ActiveX Controls and the Triple State Property

Instead of form controls, you can also use ActiveX controls. ActiveX controls are the sophisticated siblings of form controls. They are more complex to use, but they offer much more flexibility than form controls: for instance, you can change the appearance (like colors) or the font. Furthermore you have more options to define their behavior. One of the behavior properties of an ActiveX check box is the so called TripleState.  If you set the TripleState property to True, like this

a check box has three different values: True, False and Null. If it is Null, the check in the check box will be grey instead of black. A nifty additional feature and visualization, which can be used for indicating that some but not all categories are checked.

Unlike form controls, you can not directly assign a macro to ActiveX controls. Instead, Excel provides event procedures to be triggered with certain actions of the ActiveX controls.

Here are the main steps:

1. Insert the ActiveX check boxes (one for each category and an additional one called “All”)
2. Define the target cell in the property LinkedCell (right click on the ActiveX check box, select properties and defined the target cell)
3. Set the TripleState property to True (see screenshot above)
4. Insert the following code into a new module in the VBE:

   Option Explicit

   Sub Update_CheckBoxAll()
       On Error Resume Next
       With ActiveSheet
           If .CheckBox1.Value = True Then
               .CheckBox2.Value = True
               .CheckBox3.Value = True
               .CheckBox4.Value = True
               .CheckBox5.Value = True
               .CheckBox6.Value = True
           ElseIf .CheckBox1.Value = False Then
               .CheckBox2.Value = False
               .CheckBox3.Value = False
               .CheckBox4.Value = False
               .CheckBox5.Value = False
               .CheckBox6.Value = False
           End If
       End With
   End Sub

   Sub Update_SingleCheckBox()
       On Error Resume Next
       If Application.WorksheetFunction.And(Range("myActualFilter").Value) Then
           ActiveSheet.CheckBox1.Value = True
       ElseIf Not _
           (Application.WorksheetFunction.Or(Range("myActualFilter").Value)) Then
           ActiveSheet.CheckBox1.Value = False
       Else
           ActiveSheet.CheckBox1.Value = Null
       End If
   End Sub

   The sub Update_CheckBoxAll assigns the value of the “All” check box to all other check boxes. If the user clicks on “All”, all categories are selected. If he clicks again, all are deselected.

   The sub Update_SingleCheckBox is an enhancement of the sub used in the form controls approach (see above). If all categories are checked, the “All” check box is set to True, if all categories are unchecked (the ElseIf part), the “All” check box is set to False, otherwise (the Else part) it is set to Null, i.e. the “All” check box will show a grey check.

5. Finally, add the following code to the VBE of your dashboard worksheet (i.e. the worksheet with the check boxes):

   Option Explicit

   Private Sub CheckBox1_Click()
       Call Update_CheckBoxAll
   End Sub

   Private Sub CheckBox2_Click()
       Call Update_SingleCheckBox
   End Sub

   [… according code for all other category check boxes…]

   These are the event subs, i.e. they are triggered as soon as the user clicks on the according check box. Nothing to explain here, I guess. The event subs simply call the subs defined in step 4.

Done.

Here is the workbook for free download:

Download Multiple Value Filter using ActiveX Checkboxes (Microsoft Excel 2003, 95K)

Approach 3 – Storing the Mixed Values

Let’s take approach 2 one step further. If you are having a lot of categories, it may be a nice idea to switch from a “mixed” combination (i.e. some categories checked and some unchecked) and all checked or all unchecked and back to the mixed combination. In other words: store the latest “mixed” selection.

Sounds easy at first sight, doesn’t it? Store the existing mixed combination somewhere and write the stored values back, if the value of the check box is set to Null (i.e. the grey check in the box).

But it isn’t that easy. Why? Because of an Excel oddity I wasn’t aware of. ActiveX controls have different event subs, like _Click (used above), _GotFocus, _Change, etc. Simple minded as I am, I assumed, _Change would be triggered if the value of the control is changed and _Click would be triggered if the user clicks on the control. The funny thing I learned is the fact that the event procedure _Click is also automatically called if you change the value of the control using VBA (i.e. there was no click on the check box). To be very honest, I do not really understand the difference of _Change and _Click.

The workaround for this oddity is using the _Click event only for the “All” check box and the _MouseUp event for all other check boxes.

The main steps in a nutshell:

1. Define a cell range called “myStoredFilter”
2. Use the _MouseUp event instead of _Click
3. Assign the stored value in the sub Update_CheckBoxAll to the actual values, if the value of the “All” check box is Null (i.e. a “mixed” combination)
4. Manipulate the value of the clicked check box before and after the IF-clause in the sub Update_SingleCheckBox to handle the oddity described above

If you are interested in the details, have a look at the VBA of the workbook. Here is the free download:

Download Multiple Value Filter with Store Mixed Values (Microsoft Excel 2003, 99K)

Recommendation

Let’s cut a long story short. First I thought approach 3 with storing the latest mixed combination would be the best and most user-friendly option. But it isn’t. If you play around with the workbook provided above you will notice very soon that it is more confusing than helpful.

So, let’s keep it simple. Most of the time, the form control approach will do the job, If you prefer having the greyed out check arrow for a mixed combination of checks, I recommend going with approach 2.

Acknowledgement

Many thanks go again to Matt Grams for the idea. I appreciate all of your contributions and inputs, Matt. Thanks!

What’s next?

I recently had the honor to do an Interview with Chandoo for the dashboard part of his great Excel School. We spent around 90 minutes discussing some techniques of creating interactive dashboards with Microsoft Excel. Due to the limited amount of time, we had to discuss a simplified example of an interactive Excel dashboard showing real estate KPIs in the Seattle region. The next post here will provide the fully blown interactive “Seattle Real Estate – Around the Sound” dashboard in Microsoft Excel.

Stay tuned.