Let’s take a look. There are several ways to get to slicer options. First, you can click the Option tab of the Slicer Tools ribbon. The Slicer Tools ribbon will appear whenever you select a slicer. The Slicer Tools ribbon gives you direct access to slicer settings, slicer styles, tools for arranging slicers, and tools for configuring column count, button size, and slicer dimensions. You can set the heading or caption for a slicer directly on the ribbon itself. If you want to hide captions altogether, click the Slicer Settings button and uncheck “display header”. You can also get to the slicer settings dialog by right-clicking a slicer and choosing Slicer Settings from the menu. Let’s re-enable the header. By default, slicer buttons are sorted alphabetically A-Z, but you can reverse the sort order if you like. Just right-click, and select Z-A. You can also sort buttons using custom lists. Just make sure this option is enabled in the Slicer Settings dialog. The Slicer Settings dialog also contains various options for handling values that may no longer exist in the source data. To see how these work, let’s add a new slicer for Category. By default, the Category slicer shows the four existing categories. Now let’s add a new category item the source data called Custom. When we refresh the data, we see a new button in the slicer called Custom. It works just like the other buttons, and displays only a small amount of data, since it’s just one row in the source data. Let’s now revert our change in the source data and switch Custom back to Plain. When we refresh the pivot table, we see the Custom button has been dimmed. This indicates that there is no data to show for that item. The last three options in the slicer settings dialog box determine how to handle slicer buttons that don’t contain data. If you want to hide the button altogether, uncheck the option “Show items deleted from the data source”. If you want to show the button normally, reenable that option and uncheck “Visually indicate items with no data.” The last option forces buttons with no data to appear last in the list.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
