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.
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.
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.
Now, let’s walk through the steps of creating the slicers.
To set up the workbook, we’ll go through the following steps.
- Store the data in a table
- 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.
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.
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.
Excel inserts the three Slicer controls into our worksheet, as shown below.
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.
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.
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.
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:
- 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.
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:
- 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
- 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:
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.
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!
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
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.
Glad to help 🙂
This is perfect! Thank you!!! 🙂
what if we have Excel 2007? there is no “Slicer”. what is the other choice?
An alternative to slicers is using the data validation feature. I’ve written a post that goes into the details here:
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.
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?
That would be really cool, but I don’t think that is a built-in option. For that, data validation drop-downs may be a better fit. https://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/
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.
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 🙂
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?
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.
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
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!
I did not find any ribbon icon for Slicer Tools / Slicer Settings.
Will you please inform where & how to find it
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!
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.