Excel slicers offer an easy way to filter a PivotTable or PivotChart, making them a great visual tool for your dashboard sheets. If you don’t like the visual properties of the slicer, you can change a great deal. For instance, you can change the size and position of the buttons, you can change some behaviors, and you can change styles and create your own. In this article, you’ll learn how to customize slicer buttons and a few behaviors, but the biggest take-away is the results. You can make some stunning changes with just a few clicks.
I’m using Excel 2016 on a Windows 10 64-bit system. You can work with your own data or download the demonstration .xlsx file. Slicers aren’t available in the older menu versions. The browser edition will display a PivotChart and slicers work fine. However, you can’t create or modify a slicer in the browser.
SEE: Build your Excel skills with these 10 power tips (free TechRepublic PDF)
Setup
We’re not going to walk through creating a PivotChart and slicer because that’s not our focus; this article assumes that you know how to add a slicer to a PivotTable or PivotChart. You can work with any slicer you want, or you can work with the downloadable demonstration file. If you want to create everything from scratch, it might be useful to start with Create a quick and effective dashboard using Excel’s PivotChart and Slicer objects.
Figure A shows the data, the initial PivotTable, and the PivotChart. The dashboard sheet in Figure B displays only the PivotChart and its modified slicer. We haven’t changed a lot, as you’ll soon find out, but the difference is significant.
Figure A
The data and PivotTable.
Figure B
You can turn the default slicer into this.
The one thing you should notice about the data is that the Central region has records, but no sales. It’s a bit contrived, but you will occasionally work with slicer filters (buttons) that exist but have no data. We’ll deal with this problem, but I wanted to point it out to you now.
SEE: The art of the hybrid cloud (ZDNet/TechRepublic special feature)
Buttons and captions
Customizing the buttons is probably the easiest change you can make. The slicer arranges them in columns by default. To change the default arrangement and size, simply click the slicer and then click the contextual Options tab. To the right, you’ll see the Buttons group, where you can change the number of button columns and the button height and width. You can change the size of the slicer without changing dimensions for the buttons; the buttons will adapt. Use the height and weight options only if you want to anchor their size.
Now, let’s change the one-column slicer to a five-column slicer as follows:
- Select the Region slicer.
- In the contextual Options tab, change the Column option to 5 (Figure C).
Figure C
Change the number of columns to 5.
As you can see in Figure C, the captions are no longer visible. Changing the number of columns didn’t adjust the size of the slicer. To do so, use the pull handles to reduce the depth of the slicer to accommodate a single row of buttons. Then, increase the width until Excel can display the captions, as shown in Figure D.
Figure D
Change the size of the slicer to accommodate the new columns.
With the slicer selected, position it (drag it) over the top of the chart. Don’t worry about removing the text above the title; drag the slicer right over it. Adjust the width of either the slicer or the chart so both are flush. Position the bottom curve of the buttons just below the top of the chart, so they don’t show. At this point, the row of buttons looks a bit like a series of tabs. Next, with the slicer still selected, change the caption to Personnel by Region, as shown in Figure E.
Figure E
Change the caption.
Finally, change the chart’s border color to the same as the slicer’s. To do so, select the chart. Click the contextual Format tab and choose a color from the Shape Outline dropdown. The slicer buttons look like tabs, as we saw in Figure B.
SEE: The secret to being a great spy agency in the 21st century: Incubating startups (TechRepublic cover story)
Behaviors
Earlier, I mentioned that the Central region has no values, but as you can see, the Central button (filter) persists in the slicer and it’s not even dimmed–which you might expect. Let’s see what we can do to remedy this behavior. Right-click the slicer and choose Slicer Settings. In the resulting dialog, check the Hide Items With No Data option and click OK. Unfortunately, this has no effect whatsoever. Reopen the dialog and uncheck this option–don’t skip this step or what’s coming next won’t work.
This behavior, or rather, lack of behavior, is the result of the $0 values for the Central region. As far as Excel is concerned, the Central region has values; they just happen to be 0. If you want to customize a button to react to no data, you can, but the slicer expects no value, not even 0. To get the data to work nicely with the slicer, delete those 0 values for the Central region in the data source. In the downloadable demonstration file, the source data is in the sheet named Data. Figure F shows the (now) blank cells.
Figure F
Delete the 0 values for the Central region.
Before returning to the dashboard sheet, stop at the original PivotTable and refresh it. In the demonstration file, you’ll find that PivotTable in the Personnel by Region sheet. After selecting the PivotTable, click the contextual Analyze tab. Then, choose Refresh or Refresh All from the Refresh option in the Data group. Remember, you must always refresh tables and charts when you change the source data. (These changes weren’t saved with the downloadable demonstration file, so you can experience the changes.)
When you return to the dashboard sheet, you’ll see an immediate change: The Central button is now dimmed (Figure G). In addition, the slicer has moved the Central button to the far right.
Figure G
With the Central region values truly blank, the slicer knows to dim the Central button.
Now that the slicer knows that the Central region has no data, you can hide the Central button. Simply right-click the slicer and choose Slicer Settings. Check the Hide Items With No Data option, as shown in Figure H. (Note: This option isn’t available in the earliest Ribbon versions.) Instead of hiding the button, you might choose to leave it in its original position. To do so, uncheck the Show Items With No Data Last option instead.
Figure H
Change a few of the slicer’s behaviors to suit your needs.
Stay tuned
In this article, we made a few simple changes to change the slicer’s visual orientation. In a future article, we’ll tackle slicer styles. Not only can you apply built-in styles and build your own custom style for a slicer, you can do so with the slicer’s many different elements.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
Also read…
- Video: Add a drop-down list to a cell in Microsoft Excel (ZDNet)
- Four ways to specify dates using Excel data validation (TechRepublic)
- Make Office 2016 work your way by changing these default settings (free TechRepublic PDF)
- You’ve been using Excel wrong all along (and that’s OK) (ZDNet)
