Excel: Using A Slicer on A Dashboard

SLICERS

Microsoft introduced in 2010 the concept of a slicer to filter a pivot table. In Excel 2013, the functionality was expanded to include data ranges that have been formatted as a table using Ctrl+T.

Microsoft built many features that allow users to customize their slicers. You can control the caption above the slicers. You can control the number of columns that the slicers are presented in. If you dive into the Slicer Styles gallery and create a new Slicer Style, you can hide or recolor the font, fill, and outline of 16 slicer elements.

With so much customization possible, it makes sense to use a slicer on a dashboard as an easy way to select one or more items from a list.

A SNEAKY SOLUTION

The solution is to build a simple list of items that should be in the slicer. Then, in an out-of-the-way section of the worksheet, build a pivot table with those items in the rows section of the pivot table. Create a slicer from that pivot table and format it to match your dashboard.

Cells X1:X13 of Figure 1 contain a list of months. Select that data. Insert a pivot table and specify that the pivot table will be located in cell Z1. Add “Months” to the rows area. Right-click the Grand Total and choose Remove Grand Total.

On the PivotTable Analyze tab, choose Insert Slicer, then Months. The slicer is shown in AB2:AF9 of Figure 1. There are various customizations applied using the Slicer tab in the ribbon:

• Enter a caption of “Select a Month.”
• Increase the Columns setting to 3 or 6.
• In the Slicer Styles gallery, choose New Slicer Style. Format the various elements of the slicer to remove the border of the Whole Slicer.

Then, as people click on a month in the slicer, you can be sure that the selected month will appear as the second cell in the pivot table. In Figure 1, you know that Z2 contains the selected month.

With the selected month in Z2, you can build formulas to retrieve facts about the selected month, including FILTER, INDIRECT, SUMIFS, COUNTIFS, VLOOKUP, XLOOKUP, or other functions. In Figure 1, a SUMIFS in AB10 retrieves the total sales for July and a formula in AB11 formats the answer with the month name and total sales amount.

THE DASHBOARD

To build your dashboard, move the slicer from AB2 to cell A1. Note that the important formula is in cell AB11, as you will need this address in a moment.

From the Insert tab, choose Shapes and then select the rounded rectangle. Drag in the worksheet to draw an appropriate-sized shape. With the new shape selected, click in the formula bar and type a formula of =AB11. When you press Enter, the text from the formula in AB11 will appear in the shape. There are several steps to format the shape shown in Figure 2:

• On the Home tab, choose Center Align and Middle Align.
• On the Home tab, change the font color to white.
• On the Home tab, change the font size to 16.
• On the Page Layout tab, change the effects to Office 2007-2010.
• On the Shape Format tab, choose any item from the sixth row of the Shape Styles gallery.

As the person using your worksheet selects a different month from the slicer, the results in cell AB11 are updated for the new month, and those results are displayed in the shape. You can create more formulas and more shapes to add more statistics to the dashboard.

One problem to look out for is that it’s possible for a user to select multiple items from a slicer. Although the caption says “Select a Month,” some managers will try to rebel and choose a few months. If that happens, the worksheet will report totals for the first month selected.

If you are using Microsoft 365 and have access to Dynamic Arrays, you could design the workbook to be able to handle multiple months. In Figure 3 below, the SUMIFS formula is wrapped in a SUM function and totals any months that appear in Z2:Z13. The formula in AB11 uses TEXTJOIN to list all of the selected months. Both of these formulas requires Dynamic Arrays to work.

Note that nothing in the dashboard is using a pivot table, but the sneaky pivot table used to select a month from a slicer allows the formulas that drive the dashboard to update.