Excel: 24 Using Slicers and Filtering a Pivot Table

Filtering Oddities

For filtering pivot tables A. AutoFiltering a Pivot Table AutoFilter a pivot table despite of the grayed out Filter field when you are inside a pivot table. Select the cell located to the next of the last header. Click the Filter icon Excel turns on the AutoFilter drop-down The AutoFilter icon will gray out the cells when you are in the pivot table The AutoFilters are not calculated after a Refresh. B. Applying Row Label Filters to Fields Not in the Pivot Table Report You can apply a filter to a field that does not appear in the pivot table Go to the top of the Pivot Table Field List and hover over any field A drop-down appears on that field Open the drop-down and you can apply a filter to the field, even though it is not in the current report. C. Replicating a Pivot Table for Every Customer Excel adds worksheets to your workbook. Each worksheet contains the original pivot table, with a different value chosen for the selected filter field. Follow the steps: Select the Options drop-down from the Options tab if your pivot table contains at least one Report Filter field. Select Show Report Filter Pages from the drop-down menu Confirm which field should be used. Replicate your pivot table for every value in a Report Filter field.

Filtering Using Report Filter Fields

Report Filter as the Page Field area of the layout To add filter cells to a pivot table to do basic ad hoc analysis use the Report Filter To add filter drop-down to the top of the pivot table, you drag the fields to the Report Filter drop zone in the pivot table field list. Before adding a date field to the Report Filter, follow these steps to create your own hierarchy: 1. Add the date field temporarily to the row field area of the pivot table 2. Select the first cell containing a date in the pivot table 3. In the Options tab, click Group 4. In the Grouping dialog box, select Days, Months, Quarters, and Years. Click OK. 5. Move the Date, Months, Quarters, and Years fields to the Report Filter drop zone. A. Arranging the Filters Arrange fields in the Report Filter area The vertical arrangement means seven rows (the data with an added row) will be taken up with the noncustomer data (the data). Select one cell in your pivot table Click the Options icon in the left side of the Options tab In the PivotTable Options dialog box, the Layout & Format tab offers two settings that affect the arrangement of the Report Filter. In the Display Fields in Report Filter Area is set to Over, Then Down. The Report Filter Fields Per Row is set to BLANK A. This creates an arrangement of the total number of filter fields without the added number (BLANK B/ BLANK A = #) B. Selecting Multiple Items You are able to choose multiple items from the Report Filter field. The Select Multiple Items was an intermediate solution added to make slicers possible in order to print and view the multiple items.

Using Slicers and Filtering a Pivot Table

Slicers allow you to perform ad-hoc analysis by choosing various items from various fields in the pivot table.

Filtering Using Slicers

The Slicer arrangement invites people to start running ad hoc analyses by clicking the slicers A. Adding Slicers Add extra rows above your pivot table and extra columns on the both side in order to add slicers: Select one cell in your pivot table On the Options tab, select the Insert Slicer icon, Insert Slicers dialog Choose any fields that you would make suitable filter fields meaning the field can not contain sufficient data for printing. Excel tiles a bunch of one-column slicers B. Arranging the Slicers To choose a logical arrangement for the slicers, you can reposition and resize the slicers: The Slicer Tools Options tab allows you to control the number of columns in a slicer. By making the slicer shorter, the extra < and > items are hidden from view. To create that arrangement, click the slicer and use the Columns spin button in the Slicer Tools Options tab to specify three columns. After you adjust the number of columns, make the slicer wider to fit all the buttons. Horizontal slicer C. Formatting the Slicers Choose one of the fourteen slicers color themes in the gallery located on the Slicer Tools Options. Control the caption that appears at the top of each slicer Choose a slicer theme Click the Slicer Settings icon on the left side of the Slicer Tools Options tab You can adjust the caption in the Slicer Settings dialog Slicers provide a visual indication in the report. D. Using the Slicers To select a single item from a slicer, choose that item To multiselect from a single slicer, hold down the Ctrl key while selecting multiple items. To select adjacent items, click on the first item and drag to the last item to be selected 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 in 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 Limits the list of customers active in the slicer.

Filtering Using the Row Label Filter

The filter menu contains four separate filter mechanisms: The Label Filters flyout menu appears for the fields that contain text values. You can use this flyout to select customer names that contain certain words, begin with, end with, or fall between certain letters The Value flyout menu allows you to filter the customers based on values in the pivot table (Top 10, ect.) The Search box is similar to using Label, but faster. The check boxes allow you to exclude individual customers, or if you select Select All, you can clear or select all customers. Date Filters flyout menu offers conceptual filters (Last Month, Next Quarter, This Year, All Dates in the Period, ect.) A. Filtering Using the Search Box You can add results of the second search to the results from the first search. In doing so, you select the check box called Add Current Selection to Filter. You can do this with a third search. B. Clearing a Filter Open the filter drop-down for that field and select Clear field From "Field". C. Filtering Using the Check Boxes 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 24.8 D. Filtering Using the Label Filter Flyout If you are interested in finding all customers whose names end in Corp: 1. Open the Customer filter drop-down 2. Open the Label Filters flyout 3. Select Ends With. Excel displays the Label Filter dialog 4. Type Corp. Click OK. The pivot table is filtered to customers whose names end with "Corp". If you open the first drop-down in the Label Filter dialog, you will see the following choices: Equals Does not equal Is greater than Is greater than or equal to Is less than Is 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 * represents any character(s) ? represents one single character E. Filtering Using the Date Filters All Dates in the Period allows for 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. Custom Filters allows you to build a custom date range. Change the first drop-down to Is Between. Then use the date icons to choose your selected dates. F. Filtering Using Value Filters The Value Filters allows you to apply a criteria: Open the drop down menu of a field Select the Values Filter flyout Select the criteria (Great Than or Equal To) to display the Value Filter dialog Input the criteria of the numeric value in the third field of the dialog When the date field is added as a new row field, the pivot table continues to show daily sales. Adding new inner row fields (Multiple Entries for date per qty per revenue) does not change the Value filter for Customer Apply a filter to the outer row field (the table has added a outer row filed for the total per day for all customers in qty and revenue) Further filter by an inner row field. The inner filter does not change which values are selected by the outer field (the table has filtered out the outer row field based on a criteria). G. Filtering to the Top 10 The problem with the value filter is that you have to commit to a threshold value. You can use this to filter the top or bottom items. Filter drop-down Open the Values filter flyout Select Top 10 You can select the range in the Top 10 Filter dialog Top 10 Filter dialog: First Field drop-down: Top or Bottom Second Field: Spin button to change from 5 to 10. Text box to type the numeric value Next Field drop-down: Items (), Percent (represents the top n%), Sum (a criteria for a total of BLANK numeric value) Final drop-down: offers all the numeric fields in the values area of the pivot table.

Sorting a Pivot Table

You can sort your table with the fields in any sequence. The drop-down menu offers the ascending or descending order, including the More Sort Options from this drop-down. The Sort Dialog appears when you select the More Sort Options It initially offers to sort in any order based on the field. The summary will give you a description of the outcome. You can also have a pivot table with conceptual dates, revenue and customers with the More Options dialog and the mouse method. A. Why Not Sort Using the Data Tab? Any sort property you set will remain in effect as you add and remove fields with the sort options in the PivotTable Field List Box.