16. Using Slicers and Filtering a Pivot Table
Pivot table filters have been quietly evolving over the past several versions of Excel. Excel 2010 pivot tables introduced a visual filter called a slicer. Slicers enable you to perform ad-hoc analysis by choosing various items from various fields in the pivot table. Excel 2013 added a new date-centric visual filter called a timeline.
Filtering Using the Row Label Filter
To follow along, create a new pivot table from the 16-Slicers.xlsx file. Check the Customer, Date, Quantity, Revenue, COGS, and Profit fields. On the Design tab, open the Report Layout drop-down. Choose Tabular form and then choose Repeat All Item Labels. Choose the Banded Rows check box on the Design tab. You will end up with the pivot table shown in Figure 16.1. Drop-downs in cells A3 and B3 lead to the row filter menus.
Figure 16.2 shows the Filter menu for the Customer field. This drop-down contains four separate filter mechanisms:
The Label Filters fly-out menu appears for fields that contain text values. You can use this fly-out to select customer names that contain certain words, begin with, end with, or fall between certain letters.
The Value Filters fly-out menu enables you to filter the customers based on values elsewhere in the pivot table. If you want only orders over $20,000, or if you want to see the Top 10 customers, use the Values Filter fly-out.
The Search box was added in Excel 2010 and is similar to using Label Filters, but faster.
The check boxes enable you to exclude individual customers, or you can clear or select all customers by using Select All.
Figure 16.3 shows the detail of the Value Filter fly-out. All these filters, except Top 10, were new in Excel 2007.
When you access the filter drop-down for a field that contains 100% dates, the Label Filters fly-out is replaced by a Date Filters fly-out, as shown in Figure 16.4. This fly-out offers conceptual filters, such as Last Month, Next Quarter, and This Year. The All Dates in Period choice leads to a second fly-out where you can choose based on month or quarter.
Clearing a Filter
To clear all filters in the pivot table, use the Clear icon in the Sort & Filter group of the Data tab. To clear filters from one field in the pivot table, open the filter drop-down for that field and select Clear Filter from “Field.”
Filtering Using the Check Boxes
The Customer drop-down includes a list of all the customers in the database. If you need to exclude a few specific customers, you can clear their check boxes in the filter list.
The (Select All) item restores any cleared boxes. If all the boxes are already selected, clicking (Select All) clears all the boxes.
Because it is easier to select three customers than to clear 27, if you need to remove most of the items from the list of customers, you can follow these steps:
1. If any customers are cleared, select (Select All) to reselect all customers.
2. Select (Select All) to clear all customers.
3. Select the particular customers you want to view, as shown in Figure 16.5.
Filtering Using the Label Filter Fly-Out
All the Label Filters choices shown previously in Figure 16.2 lead to the same dialog. Suppose that you are interested in finding all customers whose name includes “Excel.” Follow these steps:
1. Open the Customer filter drop-down.
2. Open the Label Filters fly-out.
3. Select Contains. Excel displays the Label Filter dialog.
4. Type Excel. Click OK. The pivot table is filtered to customers whose name includes “Excel.”
If you open the first drop-down in the Label Filter dialog, you see the following choices:
equals
does not equal
is greater than
is greater than or equal to
is less than
less than or equal to
begins with
does not begin with
ends with
does not end with
contains
does not contain
is between
is not between
You can use the wildcards * and ?. Whereas * represents any character(s), the ? wildcard represents one single character.
Filtering Using the Date Filters
When a field in the original data set contains only values formatted as dates, Excel offers the Date Filters fly-out shown previously in Figure 16.4.
Many of the date filters contain conceptual filters. If you filter a pivot table to Yesterday and then refresh the data set a week later, the dates returned by the filter will change.
The list of conceptual filters feels like it was borrowed from QuickBooks, but it is not quite as complete as those from QuickBooks. It would be nice to have choices such as Last 30 Days, Month to Date, and so on.
The penultimate choice in the first fly-out is All Dates in the Period, which leads to a second fly-out. Choosing January or Quarter 1 is great when you have dates from several years and you want to compare January from each year.
The last choice in the first fly-out is Custom Filter. As shown in Figure 16.6, you can use this filter to build a custom date range. Change the first drop-down to Is Between. Then use the date icons to choose your selected dates. The Whole Days check box was new in Excel 2013. Use this to truncate times from fields that contain date and time.
Filtering to the Top 10
Pivot tables offer a feature called Top 10. Despite the name, the filter is not just for finding the top 10 values. You can use the filter to find top or bottom items. You can specify 5, 7, 10, or any number of items.
To start the filter, open the Customer filter drop-down. Open the Value Filters fly-out and select Top 10. Excel displays the Top 10 Filter dialog. In Figure 16.7, the report has been filtered to show the top five customers based on revenue.
The Top 10 filter offers these options:
The first drop-down in the dialog offers a choice between Top and Bottom.
The second field is a spin button and a text box. You can use the spin button to change from 5 to 10. If you need to get to 1,000,000, you should type that value into the text box instead of trying to hit the spin button 999,990 times.
The next field is a drop-down with choices Items, Percent, and Sum. These three choices are discussed in the next sections.
The final drop-down offers all the numeric fields in the VALUES area of the pivot table.
The Items/Percent/Sum drop-down offers a lot of flexibility. If you select Percent, the pivot table shows you enough customers so that you see n% of the value field. For example, you might ask for the top 80% of profit.
If you choose Sum, you can specify a large number as the second field in the dialog. For example, you might want to see enough customers to reach $5 million in sales.
Filtering Using Slicers
Slicers are visual filters that make it easy to run various ad-hoc analyses. While Slicers are easier to use than the Report Filter, they offer the added benefit that a slicer can filter multiple pivot tables and pivot charts created from the same data set.
Adding Slicers
To add default slicers, follow these steps:
1. Select one cell in your pivot table.
2. On the Analyze tab, select the Insert Slicer icon. Excel shows the Insert Slicers dialog.
3. Choose any fields that would make suitable filter fields. In Figure 16.8, Region, Product, and Years are selected. Months, Quarters, and Date would also be effective, but you see how they can be filtered using a timeline later in this chapter. Click OK.
Excel adds default filters, tiled in the center of your screen (see Figure 16.9).
Arranging the Slicers
You can reposition and resize the slicers. Choose a logical arrangement for the slicers. Following are some examples.
The Region and Product slicers contain short entries. Make each slicer wider and then use the Columns setting in the Slicer Tools Options tab to increase each slicer to three columns. See Figure 16.10 for the setting.
Figure 16.10 The Slicer Tools Options tab allows you to control the number of columns in a slicer. Further settings are in the Slicer Settings dialog.
The Year slicer is wider than it needs to be. There are also two extra items (<1/1/2018 and >12/31/2019) in the slicer that are remnants of Auto Group. You can turn these off in the Slicer Settings dialog. Select the slicer and choose Slicer Settings. In Figure 16.10, you can see that Years has been changed to Year. Also, Hide Items with No Data is checked.
Using the Slicers in Excel 2016
To select a single item from a slicer, choose that item. To multiselect in Excel 2016, first choose the icon at the top of the slicer that has the three check marks. You can now click each item. Prior to Excel 2016, you would have to use the Ctrl key to select multiple nonadjacent items or drag the mouse to select adjacent items.
Selections in one slicer might cause items in other slicers to gray out. In this case, those items move to the end of the list. This gives you a visual indication that the item is not available based on the current filters.
To clear a filter from a slicer, click the Funnel-X icon in the top right of the slicer.
Filtering Dates
Excel 2013 added a Timeline control for filtering date fields. It is difficult to use. Instead of the Timeline, you could achieve more flexibility by arranging three slicers for Year, Quarter, and Month, as shown in Figure 16.11
Figure 16.11 A timeline control lets you filter daily dates by month, quarter, or year without grouping.
Figure 16.11 shows a timeline. The timeline has been set to filter by quarter.
Filtering Oddities
The next sections discuss a few additional features available for filtering pivot tables.
AutoFiltering a Pivot Table
I was doing a Power Excel seminar in Philadelphia when someone in the audience asked whether it is possible to AutoFilter a pivot table. The answer is no; the Filter field is grayed out when you are inside a pivot table.
There is a surprising bug, however. If you put the cell pointer to the right of the last heading of a data set and click the Filter icon, Excel turns on the AutoFilter drop-downs. I call this cell the magic cell.
The guy at Microsoft in charge of graying out the AutoFilter icon when you are in a pivot table evidently forgot about that magic cell to the right of the headings. If you put the cell pointer in cell D1 in Figure 16.12, the Filter icon is not grayed out.
What is the advantage of using the AutoFilters? The Top 10 AutoFilter works differently from the Top 10 PivotTable filter. In Figure 16.13, the Top 10 AutoFilter for the top six items returns the top five customers and the true grand total.
If you try this method, remember that you have to go back to the magic cell to toggle off the AutoFilter. Also, if you change the underlying data and refresh the pivot table, the AutoFilter is not updated. After all, the Excel team believes that you can’t AutoFilter a pivot table.
The AutoFilter lets you filter by one item along the Column field. In Figure 16.14, the report is showing the top five customers for product XYZ in column D. A regular pivot table filter would always be based on the Grand Total in column G.
Replicating a Pivot Table for Every Customer
This technique makes many copies of the pivot table, with a different Report Filter value in each copy. To use the feature, you have to move the field to the FILTERS drop zone in the PivotTable Fields list. To create a report for every customer, move the Customer field to the FILTERS drop zone. Select the Options drop-down from the Analyze tab. Select Show Report Filter Pages from the drop-down menu, as shown in Figure 16.15. Confirm which field should be used. Excel adds worksheets to your workbook. Each worksheet contains the original pivot table, with a different value chosen for the selected filter field.
Caution
Slicers are not visible on the copied pivot tables when you use this technique.
Sorting a Pivot Table
In all the pivot tables so far in this chapter, the customers are presented in alphabetical sequence. In each case, the report would be more interesting if it were presented sorted by revenue instead of by customer name.
Starting in Excel 2010, if you use the AZ or ZA icons on the Data tab, Excel automatically sets up rules in the Sort and More Sort Options dialogs.
To access these settings later, open a row field drop-down and choose More Sort Options. This opens the Sort (Customer) dialog. Click the More icon to access More Sort Options (Customer).