In This Chapter

Understanding slicers

Creating and formatting standard slicers

Using Timeline slicers

Using slicers as command buttons

Slicers allow you to filter your pivot table in a way that’s similar to the way Filter fields filter a pivot table. The difference is that slicers offer a user-friendly interface, enabling you to better manage the filter state of your pivot table reports. Happily, Microsoft has added another dimension to slicers with the introduction of Timeline slicers. Timeline slicers are designed to work specifically with date-based filtering.

In this chapter, you explore slicers and their potential to add an attractive and interactive user interface to your dashboards and reports.

Understanding Slicers

If you’ve worked your way through Chapter 6, you know that pivot tables allow for interactive filtering using Filter fields. Filter fields are the drop-down lists you can include at the top of a pivot table, allowing users to interactively filter for specific data items. As useful as Filter fields are, they have always had a couple of drawbacks.

First of all, Filter fields are not cascading filters — the filters don’t work together to limit selections when needed. Take, for example, Figure 12-1. You can see that the Region filter is set to the North region. However, the Market filter still allows you to select markets that are clearly not in the North region (California, for example). Because the Market filter is not in any way limited based on the Region Filter field, you have the annoying possibility of selecting a market that could yield no data because it’s not in the North region.

Another drawback is that Filter fields don’t provide an easy way to tell what exactly is being filtered when you select multiple items. In Figure 12-2, you can see an example of this. The Region filter has been limited to three regions: Midwest, North, and Northeast. However, notice that the Region filter value shows (Multiple Items). By default, Filter fields show (Multiple Items) when you select more than one item. The only way to tell what has been selected is to click the drop-down menu. You can imagine the confusion on a printed version of this report, in which you can’t click down to see which data items make up the numbers on the page.

By contrast, slicers don’t have these issues. Slicers respond to one another. As you can see in Figure 12-3, the Market slicer visibly highlights the relevant markets when the North region is selected. The rest of the markets are muted, signaling that they are not part of the North region.

When selecting multiple items in a slicer, you can easily see that multiple items have been chosen. In Figure 12-4, you can see that the pivot table is being filtered by the Midwest, North, and Northeast regions. No more (Multiple Items).

Creating a Standard Slicer

Enough talk. It’s time to create your first slicer. Just follow these steps:

1. Place the cursor anywhere inside the pivot table.

   Doing so activates the PivotTable Tools contextual tabs on the Ribbon.

2. Click the Analyze tab and then click the Insert Slicer icon, as shown in Figure 12-5.

   This step activates the Insert Slicers dialog box, shown in Figure 12-6.

3. Using the Insert Slicers dialog box, select the dimensions you want to filter.

   In this example, the Region and Market slicers are created.

4. After the slicers are created, simply click the filter values to filter the pivot table.

   As you can see in Figure 12-7, not only does clicking Midwest in the Region slicer filter your pivot table, but the Market slicer also responds by highlighting the markets that belong to the Midwest region.

   You can also select multiple values by holding down the Ctrl key on the keyboard while selecting the needed filters. In Figure 12-8, I held down the Ctrl key while selecting Baltimore, California, Charlotte, and Chicago. This highlights not only the selected markets in the Market slicer, but also their associated regions in the Region slicer.

   To clear the filtering on a slicer, simply click the Clear Filter icon on the target slicer, as shown in Figure 12-9.

Getting Fancy with Slicer Customizations

If you’re going to use slicers on a dashboard, you should do a bit of formatting to have your slicers match the theme and layout of your dashboard. The following sections cover a few formatting adjustments you can make to your slicers.

Size and placement

A slicer behaves like a standard Excel shape object in that you can move it around and adjust its size by clicking it and dragging its position points; see Figure 12-10.

You can also right-click the slicer and select Size and Properties from the menu that appears. This brings up the Format Slicer pane, illustrated in Figure 12-11, allowing you to adjust the size of the slicer, how the slicer should behave when cells are shifted, and whether the slicer should appear on a printed copy of your dashboard.

Data item columns

By default, all slicers are created with one column of data items. You can change this by right-clicking the slicer and selecting Size and Properties from the menu that appears. This brings up the Format Slicer pane. Under the Position and Layout section, you can specify the number of columns in the slicer. Adjusting the number to 2, as demonstrated in Figure 12-12, forces the data items to be displayed in two columns, adjusting the number to 3 forces the data items to be displayed in three columns, and so on.

Other slicer settings

Right-clicking your slicer and selecting Slicer Settings from the menu that appears activates the Slicer Settings dialog box, shown in Figure 12-13. With this dialog box, you can control the look of the slicer’s header, how the items in your slicer are sorted, and how filtered items are handled.

Creating your own slicer style

The default slicer styles are, let’s face it, a bit of a drag. Oftentimes, the look and feel of slicers don’t match the aesthetic of your dashboard. Luckily, Excel provides a way for you to customize your slicers to fit into any reporting theme. With minimal effort, your slicers can be integrated nicely into your dashboard layout.

Figure 12-14 illustrates a few examples of how slicers can be customized to almost any style you can think of.

To change the look and feel of your slicer, you have to get into some style customizations. The following steps show you how:

1. Click the slicer to reveal the Slicer Tools Options tab on the Ribbon and then expand the tab’s Slicer Styles gallery.

2. Click the New Slicer Style button at the bottom of the gallery, as shown in Figure 12-15.

   Doing so brings up the New Slicer Style dialog box, shown in Figure 12-16.

3. Using the New Slicer Style dialog box, customize any (or all) of the following slicer elements:
   • Whole Slicer
   • Header
   • Selected Item with Data
   • Selected Item with no Data
   • Unselected Item with Data
   • Unselected Item with no Data
   • Hovered Selected Item with Data
   • Hovered Selected Item with no Data
   • Hovered Unselected Item with Data
   • Hovered Unselected Item with no Data

The idea here is to select each slicer element and then format that element by clicking the format button. Sound easy enough, but it can be a bit difficult to know exactly which part of the slicer you are formatting.

Whole Slicer and Header are fairly self-explanatory, but what does the other junk mean?

Well, the other options refer to the values within the slicer. Some values have data associated with them, and others do not. The elements that are listed let you define what each value (values “with Data” and values “with no Data”) look like when selected, unselected, and hovered over. Figure 12-17 offers a visual map to help understand how each kind of value is typically represented in the slicer.

After you finish making the needed customizations to all the elements, you can apply your newly created style by clicking the slicer and then selecting your custom style in the Slicer Styles Gallery.

You can also right-click on your custom style to modify, duplicate, and delete it (see Figure 12-18), using the contextual menu that appears.

Custom styles are saved at the workbook level, so your custom style is saved and travels with your workbook. However, other workbooks will not have your styles included.

Controlling Multiple Pivot Tables with One Slicer

Another advantage you gain with slicers is that each slicer can be tied to more than one pivot table; that is to say, any filter you apply to your slicer can be applied to multiple pivot tables.

To connect your slicer to more than one pivot table, simply right-click the slicer and select Report Connections from the menu that appears. This activates the Report Connections dialog box, shown in Figure 12-19. Place a check next to any pivot table that you want to filter using the current slicer.

At this point, any filter you apply to your slicer will be applied to all connected pivot tables. Controlling the filter state of multiple pivot tables is a powerful feature, especially in dashboards that run on multiple pivot tables.

Creating a Timeline Slicer

The Timeline slicer works in the same way a standard slicer does, in that it lets you filter a pivot table using a visual selection mechanism instead of the old Filter fields. The difference is the Timeline slicer is designed to work exclusively with date fields, providing an excellent visual method to filter and group the dates in your pivot table.

To create a Timeline slicer, your pivot table must contain a field where all data is formatted as a date. It’s not enough to have a column of data that contains a few dates. All values in the date field must be a valid date and formatted as such.

To create a Timeline slicer, follow these steps:

1. Place the cursor anywhere inside the pivot table and then click the Analyze tab on the Ribbon.

2. Click the tab’s Insert Timeline command, shown in Figure 12-20.

   The Insert Timelines dialog box shown in Figure 12-21 appears, showing you all available date fields in the chosen pivot table.

3. In the Insert Timelines dialog box, select the date fields for which you want to create the timeline.

After your Timeline slicer is created, you can filter the data in the pivot table and pivot chart using this dynamic data selection mechanism. Figure 12-22 demonstrates how selecting Mar, Apr, and May in the Timeline slicer automatically filters the pivot chart.

Figure 12-23 illustrates how you can expand the slicer range with the mouse to include a wider range of dates in your filtered numbers.

Want to quickly filter your pivot table by quarters? Well, that’s easy with a Timeline slicer. Simply click the time period drop-down menu and select Quarters. As you can see in Figure 12-24, you can also switch to Years or Days, if needed.

Timeline slicers are not backward compatible: They are usable only in Excel 2013 and Excel 2016. If you open a workbook with Timeline slicers in Excel 2010 or previous versions, the Timeline slicers will be disabled.

Using Slicers as Form Controls

In Chapter 11, you see how to add interactivity to a dashboard using data modeling techniques and Form controls. Although the techniques in that chapter are powerful, the one drawback is that Excel Form controls are starting to look a bit dated, especially when paired with the modern-looking charts that come with Excel 2016.

One clever way to alleviate this problem is to hijack the Slicer feature for use as a proxy Form control of sorts. Figure 12-25 demonstrates this with a chart that responds to the slicer on the left. When you click the Income selection, the chart fills with income data. When you click Expense, the chart fills with expense data. Keep in mind that the chart itself is in no way connected to a pivot table.

To build this basic model, follow these steps:

1. Create a simple table that holds the names you want for your controls, along with some index numbering.

   In this case, the table should contain three rows under a field called Metric. Each row should contain a metric name and an index number for each metric (Income, Expense, and Net).

2. Create a pivot table using that simple table, as illustrated in Figure 12-26.
3. Place the cursor anywhere inside your newly created pivot table, click the Analyze tab, and then click the Insert Slicer icon.

4. In the Insert Slicers dialog box that appears, create a slicer for the Metric field.

   At this point, you have a slicer with the three metric names.

5. Right-click the slicer and choose Slicer Settings from the menu that appears, in order to activate the Slicer Settings dialog box.

6. In the Slicer Settings dialog box, deselect the Display Header check box, shown in Figure 12-27.

   Each time you click the Metric slicer, the associated pivot table is filtered to show only the selected metric. Figure 12-28 demonstrates that this also filters the index number for that metric. The filtered index number will always show up in the same cell (N8, in this case). So this cell can now be used as a trigger cell for VLOOKUP formulas, index formulas, IF statements, and so on.

7. Use the slicer-fed trigger cell (N8) to drive the formulas in your staging area, as demonstrated in Figure 12-29.

   This formula tells Excel to check the value of cell N8. If the value of cell N8 is 1, which represents the value of the Income option, the formula returns the value in the Income dataset (cell G9). If the value of cell N8 is 2, which represents the value of the Expense option, the formula returns the value in the Expense dataset (cell G13). If the value of cell N8 is not 1 or 2, the value in cell G17 is returned.

8. Copy the formula down and across to build out the full staging table; see Figure 12-30.

9. The final step is to simply create a chart using the staging table as the source.

   With this simple technique, you can provide your customers with an attractive interactive menu that more effectively adheres to the look and feel of their dashboards.