Slicers as an Alternative to Conditional Drop Downs

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

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

17 Comments

  1. David Rimon on October 6, 2015 at 12:12 pm

    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.

    • jefflenning on October 7, 2015 at 5:54 pm

      Glad to help 🙂

  2. Ahlyshawndra Means on October 21, 2015 at 2:23 pm

    This is perfect! Thank you!!! 🙂

  3. Salameh abi Karam on November 12, 2015 at 12:37 am

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

    thanks.

  4. Nicolas Blanchette on May 24, 2016 at 7:46 am

    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 on March 17, 2017 at 1:50 am

    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 on April 17, 2017 at 1:27 pm

    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.

    • Jeff Lenning on April 21, 2017 at 5:36 pm

      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. on April 25, 2017 at 12:50 pm

    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?

    • Jeff Lenning on April 25, 2017 at 6:36 pm

      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

  8. Julian on January 22, 2018 at 5:40 pm

    This got me excited and it just did not work in the middle of the exercise.
    I was trying to use to help people from various regions and functions to populate primary contact names associated to each of my customers, using conditional LOV to reduce the Name list to pick from.

    It would have been easier for you to upload / attached the Excel file that you used for the demo instead of using flash animations that look great but cannot help us repeat the exercise.

    Thanks for trying anyway

    • Jeff Lenning on January 24, 2018 at 11:40 am

      Hi Julian, the Excel file used in the blog post, which includes the completed approach, is available by clicking the Sample File link in the Additional Resources area at the end of the post. Hope it helps!
      Thanks
      Jeff

  9. sandeep kothari on February 10, 2019 at 8:06 am

    Dear Jeff
    I did not find any ribbon icon for Slicer Tools / Slicer Settings.
    Will you please inform where & how to find it

  10. Jason on October 3, 2019 at 5:53 pm

    Is there a way to get the slicer fields to appear as a drop-down selection list (similar to data validation lists)? Note- I also didn’t see any “Slicer Tools/ Settings” options, but there are other slicer controls under the “Options” tab. Thanks!

  11. Dylan on September 30, 2020 at 9:53 am

    Good Morning, Jeff,
    Thank you so much for the helpful article. I am using the Multi-select tool on the slicer tab and I was curious if there was a way to index rows other than the first one, or if there was a way to use the “SUM” function to add all of the visible rows once the slicers are applied to the table.

Leave a Comment