Preview
Create a Pivot Table Slicer; Practice Exercise 19
Filtering with Slicers – a Graphical Way to Filter Data
Slicers are a fairly new Excel feature, introduced with Excel 2010, and have become quite popular as a result. Basically, slicers are a graphical way to filter data, much like the way the Filter fields filter the data, but with an artistic flair.
First and foremost, it needs to be understood that slicers can be used with pivot tables AND regular tables. They are created using the same process and are used to filter data in the same way.
You can select any Pivot table field as a slicer candidate. After the slicers are created you can click the filter values to filter your pivot table. When you are using a region field as a slicer and click on it, the Region and Market fields are usually related. For example, if we have a West Coast Region and select its slicer, it will automatically highlight the markets associated with the West Coast Region, i.e. Los Angeles, San Francisco, Seattle, and Portland markets.
Another advantage of using slicers is that you can link each slicer to more than one pivot table. Any filter you apply to your slicer can be applied to several pivot tables.
The Timeline – Great for Filtering Dates
The Timeline is actually categorized as a slicer as well, although the Timeline has come to Excel a little later, with Excel 2013 being its first version. The Timeline slicer works the same way as a standard slicer in the sense that it lets you filter a pivot table using a visual selection instead of the standard filter fields. The difference with the Timeline is that it is designed to work exclusively with date fields, providing a great visual interface to filter and group the dates in a pivot table.
Keep in mind that to create a Timeline slicer, a pivot table must contain a field where all of the data is formatted as a date. You can’t just have a column of data filled in with some dates. All of the values in your date field must be formatted as a valid date. If only one value in your date column is blank or is not a valid date, Excel will not create the Timeline slicer.
The Timeline slicer allows you to quickly switch between years, quarters, months, and days, adding to its versatility.
Practice Exercise 19: Create slicers to filter a pivot table.
Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2.
STEP 1: Check the PivotTable Fields boxes as shown by the near right graphic (FIGURE 1).
STEP 2: Move the PivotTable Fields to their proper quadrant as shown by FIGURE 2.
The resulting pivot table is shown in FIGURE 3.
STEP 3: Select the Pivot Table Design tab as shown in FIGURE 4 below.
STEP 4: Select Subtotals-Do Not Show Subtotals (FIGURE 5).
STEP 5: Select Grand Totals-On For Columns Only (FIGURE 6).
STEP 6: Select Report Layout-Show in Tabular Form (FIGURE 7).
STEP 7: Select the Insert tab, and from the Ribbon Filters section, select the Slicer icon (FIGURE 8) .
STEP 8: On the Insert Slicers box, check the boxes for the pivot table fields that you want to have slicers used with (FIGURE 9).
Check the following boxes on the Insert Slicers dialog box:
- Category
- Market
- Month
STEP 9: Click the OK button when finished
The resulting pivot table with 3 slicers is shown below IN FIGURE 10.
Category Slicer: Initially, all categories are highlighted, meaning all of the categories are being displayed with no filtering action (FIGURE 11).
STEP 10: Click the Desktops button with mouse.
Desktops button is now highlighted, and the Desktops category is being displayed.
When only one category is highlighted, that means filtering action is occurring. The other two categories, Laptops and Tablets buttons are not illuminated and therefore are not being displayed (FIGURE 12).
The resulting pivot table is shown on the right in FIGURE 13.
STEP 11: To display more than one category, hold down the Control key and while it is depressed, click on the other category desired. You can also click and drag your mouse to select the second category if it is directly below the first selection. Do this procedure for selecting any additional categories.
Hold down the Control key and select the Laptop button. The Desktops and Laptops buttons are illuminated and those categories are displayed (FIGURE 14).
The resulting pivot table is shown on the right in FIGURE 15.
The other two slicers, Market and Month, work exactly the same way.
STEP 12: To remove a slicer position mouse in the top part of the slicer, right-button mouse click and from the shortcut menu select, “Remove SLICER NAME”, in this example, it’s our slicer named, “Category”, so we want to select the choice, “Remove Category” (FIGURE 16).
STEP 13: The “Category” slicer shown on the right has 3 of its buttons highlighted, meaning that the slicer is currently showing Desktops, Laptops, and Tablets categories in the pivot table (FIGURE 17).
The “Clear Filter” icon in the top right corner of the slicer is used for resetting the slicer back to its default, non-filtering mode (FIGURE 18).
STEP 14: Select Desktops button on the Category slicer (FIGURE 19).
STEP 15: Select the “Clear Filter” icon in the top-right corner of the slicer (FIGURE 20).
STEP 16: The slicer is reset back to its defaul, no-filtering configuration (FIGURE 21).
Tip: If the filter funnel has a red color it indicates filtering is currently engaged. If the filter funnel is not highlighted with red color, filtering has not been engaged.
STEP 17: Click on the “Category” slicer (do not click on a button).
When you click on any slicer, the Slicer contextual tab appears. It does not appear as a contextual tab when clicking on a pivot table cell, but only when clicking on a slicer.
STEP 18: Click on the Slicer tab.
STEP 19: Slicer Toolbar becomes visible when selecting the Slicer tab (FIGURE 22).
STEP 20: Click mouse inside the Slicer Caption window (FIGURE 22) and change the caption from “Category” (FIGURE 23) to “Type” (FIGURE 24).
Note: With Online Excel, Slicers cannot be used with data tables. They can only be used with pivot tables.
END OF LESSON
Leave a Reply