Table slicers have a variety of settings. To access these settings, select a slicer and click “Options” under the Slicer Tools menu in the ribbon. Options appear grouped in 5 main categories

  1. Name and settings 2. Slicer styles 3. Commands to arrange and align slicers 4. Controls for slicer buttons, and 5. Inputs to set slicer size   The Slicer Tools ribbon will appear whenever you select a slicer.  The slicer settings button opens a window with more settings. You can access these same settings by right-clicking on a slicer. You can set the heading or caption for a slicer directly on the ribbon itself. If you want to hide the caption, uncheck the “display header” option. By default, slicer buttons are sorted A-Z, but you can also sort buttons in descending order. There’s even a setting to sort buttons using custom lists. Just make sure the option is enabled in the Slicer Settings dialog. The Slicer Settings window also contains various options for handling values that no longer exist in filtered data. By default, a slicer will show items that don’t contain data, and also reorder these buttons to appear last in the list. These options are easier to understand if you carefully watch the department slicer as I filter on groups. Notice departments with no data appear in a different color and are shifted to the bottom of the list. If I uncheck the option to show empty items last, the empty departments still have a different color, but they no longer move around. If I remove the option to visually indicate empty items altogether, all department buttons remain in the same location and look the same, whether they contain data or not. Finally, if I enable the option to hide empty buttons, departments without data disappear altogether. You can set options for more than one slicer at the same time. Select one slicer, then hold down the Control key and select another. Then open the slicer options window and make adjustments. Notice the name is grayed out, but all other options are available.

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.