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!