Slicers as an Alternative to Conditional Drop Downs

Slicers by Jeff Lenning

Beginning with Excel 2013 for Windows, we can use Slicers to filter table data. In this post, we’ll explore how to use Slicers as a relatively easy alternative to conditional drop-downs.

Objective

When we want to allow a user to select a choice from a list of items, we often consider using the Data Validation feature to set up a drop-down with the list of choices. When there is a second input cell whose choices depend on the option selected in the primary drop-down, we often consider using Data Validation to create a secondary drop-down as well. We use some formula wizardry to create the secondary drop-down list of choices. This type of scenario is illustrated below.

20150210-a

Unfortunately, the formula that defines the secondary list of choices can be tricky to set up 🙁

Fortunately, there is an alternative 🙂

Slicers are an alternative, as illustrated below.

20150210-c

Now, let’s walk through the steps of creating the slicers.

Details

To set up the workbook, we’ll go through the following steps.

  • Store the data in a table
  • Slicers
  • Helper column
  • Retrieve selected table data

Let’s get to it.

Store the data in a table

The first step is to ensure the data is stored in a table. If not, you can easily convert the ordinary range into a table by selecting any cell in the range and then the following Ribbon icon:

  • Insert > Table

The resulting data table is shown below.

20150210f

Excel automatically named the table Table1, but, we could change the table’s name if desired by using the following Ribbon field:

  • Table Tools > Design > Table Name

We’ll stick with the default name, Table1, and keep moving. With our data stored in a table, we can now proceed to inserting the Slicer controls.

Slicers

Beginning with Excel 2013 for Windows, Slicers can filter Tables. Since we want to allow the user to select a State, and then a valid City, and then a valid Street, we need to set up three slicers. We select any cell in the table and then the following Ribbon icon:

  • Insert > Slicer

The resulting Insert Slicers dialog allows us to pick which columns we’d like to filter. In our case, we check State, City, and Street, as shown below.

20150210b

Excel inserts the three Slicer controls into our worksheet, as shown below.

20150210c

Now, we can use standard cut/paste commands to move the Slicers into any other worksheet, in our case, the Slicers worksheet. After aligning them and re-sizing them, they appear as shown below.

20150210d

You can click on a slicer button and you’ll notice the other slicers update accordingly to highlight available options.

In order to hide the invalid options, for example to hide cities that aren’t in the selected State, select the City Slicer and then open the Slicer Settings dialog box by selecting the following Ribbon icon:

  • Slicer Tools > Options > Slicer Settings

In the dialog, check the Hide items with no data checkbox, as shown below.

20150210e

We do this for the dependent Slicers, that is, the City and Street Slicers. Now, only valid combinations of choices will appear.

There are many additional formatting options, and I’d encourage you to play with them by selecting a Slicer and then exploring the Slicer Tools > Options Ribbon tab. There are styles with many beautiful colors, alignment options, and you can control the button size and overall slicer control size as well.

With the Slicers in place, it is now time to retrieve the table values into cells. To do so, we’ll start by setting up a helper column.

Helper column

We’ll add a new column to our table that stores a helper formula. The helper column will allow us to easily retrieve the selected values with formulas. The purpose of the helper column is to identify which rows are visible and which are hidden after a filter is applied. We’ll use the AGGREGATE function and tell it to ignore hidden rows.

Before writing the helper column formula, we select the table and clear any applied filters with the following Ribbon icon:

  • Data > Clear

Then, we create a new column label such as “Visible” and confirm that the table auto-expands to the right to include it.

In the first data row of the column, we write the following formula:

=AGGREGATE(3,5,[@StoreID])

Where:

  • 3 tells the function to use the COUNTA function, which counts non-blank cells
  • 5 tells the function to ignore hidden rows
  • [@StoreID] tells the function which range to count, the current row StoreID cell

This will cause Excel to place a 1 into the visible cells, and a 0 into the hidden cells. Currently, there are no hidden cells because there is no filter applied as shown below.

20150210h

However, as soon as we apply a filter, either manually or by clicking a Slicer button, there will be hidden cells. The AGGREGATE function will return 0 for the cells in hidden rows. This makes it easy for us to retrieve related cell values based on the choices selected with the Slicers. Speaking of which, it is now time to retrieve the related table values into some cells.

Retrieve selected table data

Our final step is to retrieve the related table values. This is easily done with the INDEX/MATCH functions.

To retrieve the selected Store ID, we use the following formula:

=INDEX(Table1[StoreID],MATCH(1,Table1[Visible],0))

Where:

  • Table1[StoreID] is the structured table reference for the Store ID column.
  • MATCH(1,Table1[Visible],0) returns the position number of the first visible row
  • Where:
    • 1 is the value to find (1 will appear in visible rows)
    • Table1[Visible] is the column that contains our helper formula
    • 0 means exact match

We can set up similar formulas to retrieve the remaining related values. For example, to retrieve the selected State, we use the following formula:

=INDEX(Table1[State],MATCH(1,Table1[Visible],0))

And so on. If you’ve not yet explored the INDEX/MATCH functions, I’ve included links to related Excel University blog posts.

The resulting workbook is shown below.

20150210-e

Note: the formulas above retrieve the values from the first matching row.

There are many benefits to using the Slicers approach, including, Slicers automatically create unique buttons from a column with duplicate values, automatically include scroll bars if needed, and automatically sort the buttons. This approach is terrific if your version of Excel supports Slicers and Tables, and if you have a single set of input cells. Otherwise, you may want to check out the conditional data validation post referenced below.

And that my friend is how to use Slicers as an alternative to conditional drop-down controls. If you have any alternatives, please share by posting a comment below…thanks!

Additional Resources

This article was written by Jeff Lenning

12 comments:

  1. David Rimon
    Reply

    I just want to thank you so much for this. This is exactly what I was looking for.
    It is also very simple to understand.

    1. jefflenning Post author
      Reply

      Glad to help 🙂

  2. Ahlyshawndra Means
    Reply

    This is perfect! Thank you!!! 🙂

  3. Salameh abi Karam
    Reply

    what if we have Excel 2007? there is no “Slicer”. what is the other choice?

    thanks.

  4. Nicolas Blanchette
    Reply

    Thank you very much. This is good information that will be usefull and I think more user friendly than data validation list with multiple parameters.

  5. Will
    Reply

    This is great! Thank you so much for this. I was wondering if there is a way to make it so the filters that are applied only change one cell and when I click on the next active cell the filters reset without affecting the previous one?

  6. Matt
    Reply

    This is very useful information. Thanks! Is it possible to use this in a workbook with multiple worksheets? The sheets are structured the same but have different pieces of data and are in multiple sheets because changes are easier to make if its sorted by category.

    1. Jeff Lenning Post author
      Reply

      Yea indeed, this technique should work in a workbook with multiple sheets. You can even move slicers as needed with a standard cut/paste. If you give it a try and get stuck, post back with specifics and I’ll try to help out 🙂

  7. Kristi G.
    Reply

    This is perfect! Exactly what I was looking for – other guides to data validation drop-downs didn’t even mention this alternative. I do have one question, however. The INDEX/MATCH function displays “0” in a cell if the corresponding cell in the data table is blank. Is there any way to suppress this?

    1. Jeff Lenning Post author
      Reply

      Kristi,
      A couple options come to mind to replace the 0 with something else. One option is to apply a Custom number format such as #;#; which would display nothing when the calculated value is 0. This is probably the easiest. Other options would be to modify the formula to replace 0 with something else with a function such as IF, or maybe SUBSTITUTE.
      Hope it helps.
      Jeff

Leave a Reply

Your email address will not be published. Required fields are marked *