The figure below is using Slicers to control a setting in a workbook, a setting that tells Excel’s Camera tool which of three images to return: a bar chart, a line chart, or a table.
In Interactive Dashboard Magic with Excel Slicers, I explained how to display one of three figures when you enter the values 1, 2, or 3 in a cell.
Now, in this article, I’m going explain how I set up the slicer to change that setting, rather than using a validation list control or a form control.
Slicers vs Other Methods
In years past, I might have used a form control to control this figure. To see form controls, in the Developer, Controls group, choose Insert.
Form controls might not officially be deprecated, but they’re on their way. They’ve not been updated in years, and Microsoft never talks about them. Therefore, I try never to use them.
On the other hand, the validation list control is used widely.
When you set up a validation list, you first set up the list of choices you want to offer your user.
For example, you could set up a list with Bar Chart, Line Chart, and Table in three adjacent cells. You select a cell where you want the dropdown list box to reside. Then you choose Data, Data Tools, Data Validation, Data Validation to launch the Data Validation dialog. Next, in the dialog, you choose Settings, Allow: List, and then you specify that three-cell list as the Source.
When you choose OK, your active cell will contain a dropdown list box with the choices in your list. Then, in this example, you’d set up a MATCH formula in your FigNum cell to return 1, 2, or 3—depending on which of the items you chose in your validation list.
The validation list has at least four problems. First, I recently discovered a bug in Excel 365 that makes a validation list difficult to use when a Camera is on the same sheet as the validation dropdown list box. Second, validation lists require more clicking than Slicers. Third, they’re not as professional-looking.
And fourth, you can’t set up two validation list dropdown list boxes to control the same cell. That means that you can’t set up validation lists on each sheet in your workbook to change the same setting for all sheets in your workbook.
Slicers have none of those problems.
On the other hand, they also do have a few problems. First, they can directly control only PivotTables or Excel Tables in the current version of Excel 365. That’s fine, but what if you want to use them for other purposes? Second, they do take more work to set up than the other two methods.
To see how I dealt with those issues, let’s set up my slicer from scratch…
Set Up Your Slicer Table
Here’s the general strategy for using slicers to control settings in your workbook:
Because slicers can control only PivotTables and Tables, you set up either a tiny Table or a pivot for your slicer to control, then you set up formulas that translate those changed tables into the settings you need.
That’s easier than it sounds.
Here’s the Slicer Table I set up for the example at the top of this page. To set it up, I first entered the text and numbers you see in rows 2 through 5.
I selected a cell in that little table, then pressed Ctrl + T to launch the Create Table dialog. Because Excel set it up correctly by default, I then pressed OK.
After you set up the Table, here’s how to set up the Total row in row six,…
Select anywhere in the Table. In the Table Tools, Design, Table Style Options group, check Total Row. That will give you a result like this figure.
Now make three changes to the Total row.
First, type FigNum to replace the Total label.
Second, assign the FigNum range name to cell B6. To do so, select the range A6:B6; press Ctrl + Alt +A3 to launch the Create Names dialog; if Excel ask if you want to change the definition of FigNum indicate that you do; and then choose OK.
Note, by the way, that cell B6 is the FigNum cell that the Camera tool references in your Report sheet, which you learned about in Interactive Dashboard Magic with Excel Slicers,
Third, replace the subtotal formula in cell B6 with this formula:
B6: =IF(SUBTOTAL(2,[Choice])>1,2,SUBTOTAL(109,[Choice]))
Here’s what this formula is about…
We can set up Slicers to be either multi-select or single-select. But even if we specify single-select—which is the option we want—users still can override that setting. Therefore, we need to set up a formula that forces the FigNum formula still to return 1, 2, or 3 if someone chooses multiple items in the slicer.
To do that, we take advantage of the fact that slicers hide unselected rows in the table, and that the SUBTOTAL function ignores those unselected rows.
So here’s what the formula says: If the count (which is SUBTOTAL option 2) of the Choice column is greater than 1, return the value 2, otherwise return the sum ( option 109) of the Choice column.
As the Slicer Table figure illustrates, the value 2 in the formula is associated with the line chart. I also could have specified either 1 or 3 to display one of the other figures by defaut.
We can use the sum in calculation in the formula because if the slicer chooses only one item, only one row is unhidden, and therefore, the second SUBTOTAL function in the formula returns the sum of only one value. And if more than one item is chosen, the formula returns 2, while ignoring the sum’s value.
In other words, when only one value is chosen, SUBTOTAL(109,[Choice]) works kind of like a lookup formula.
Now that we have the Slicer Table, we can set up a slicer.
Set Up Your a Slicer
To set up a slicer like the green one below, first select any cell in the Slicer Table. Then in the Table Tools, Design, Tools group, choose Insert Slicer.
After you do so, Excel launches the Insert Slicers dialog, with the names of both of the Table’s columns listed. Check theFigure Type checkbox, and then choose OK.
Your challenge now is to turn a slicer that looks somewhat like the figure above into one that looks like this version.
Format Your Slicer
The first step I took was to change the slicer from one column to three columns. To do so, go to the Slicer Tools, Options, Buttons group, and change the number of columns to 3.
Then click and drag the slicer object to make it wider so it can display the labels properly, and shorter to eliminate wasted space. After you do so, it should look something like this figure.
Note that if you had set 2 columns, and if there weren’t enough room in your slicer to display the second row of buttons, the slicer would have displayed a scroll bar automatically.
To format colors and fonts in most of Excel, we press Ctrl + 1 to launch a Format dialog or pane, and then use the settings we’re given to format the item. But that’s not what we do with slicers.
Instead, we use Styles for that type of formatting.
Therefore, in the Slicer Tools, Options, Styles group, click the More arrow icon at the bottom-right of the group. And then choose New Slicer Style to launch the New Slicer Style dialog.
Having no better option, I accepted the Slicer Style 1 name and clicked OK.
When I did so, Excel displayed my new style at the far left of my Slicer Styles group. So with my slicer selected, I clicked my new style to assign its settings.
But when I did so, it looked like I’d made a horrible mistake, because my style, with no formatting specified, removed all formatting from my slicer, as shown in this figure.
But, with the slicer selected, I right-clicked the icon in the Slicer Styles group for my cleverly named Slicer Style 1, chose Modify, and looked at my options. The first option is Whole Slicer. So I selected that option, chose Modify and played around with the color settings that I wanted to assign to the slicer’s background.
When I clicked OK, I got this result. That wasn’t much better, but I was making progress.
In all, I changed these settings in the style I set for the slicer:
- Whole Slicer
- Selected Item with Data
- Unselectd Item with Data
- Hovered Selected Item with Data
- Hovered Unselected Item with Data
I ignored settings for items without data, because I had data for everything. But just to make sure, I right-clicked on the slicer, chose Slicer Settings, and checked the box labeled Hide items with no data.
I also ignored the Header option. I made sure the Multi-Select option in the header was de-selected, then I hid the header. To do so, I launched the Slicer Settings dialog again and deselected Display Header.
And that gave me the style you see here. And also, in Interactive Dashboard Magic with Excel Slicers, I explained how to display one of three figures when you enter the values 1, 2, or 3 in a cell.
