Excel Pivot Tables - Filtering Data

You might have to do in-depth analysis on a subset of your PivotTable data. This might be because you have large data and your focus is required on a smaller portion of the data or irrespective of the size of the data, your focus is required on certain specific data. You can filter the data in the PivotTable based on a subset of the values of one or more fields. There are several ways to do that as follows −

Filtering using Spcers.

Filtering using Report Filters.

Filtering data manually.

Filtering using Label Filters.

Filtering using Value Filters.

Filtering using Date Filters.

Filtering using Top 10 Filter.

Filtering using Timepne.

You will learn filtering data using Spcers in the next chapter. You will understand filtering by the other methods mentioned above in this chapter.

Consider the following PivotTable wherein you have the summarized sales data region wise, salesperson wise and month wise.

Report Filters

You can assign a Filter to one of the fields so that you can dynamically change the PivotTable based on the values of that field.

Drag Region from Rows to Filters in the PivotTable Areas.

The Filter with the label as Region appears above the PivotTable (in case you do not have empty rows above your PivotTable, PivotTable gets pushed down to make space for the Filter.

You will observe that

Salesperson values appear in rows.

Month values appear in columns.

Region Filter appears on the top with default selected as ALL.

Summarizing value is Sum of Order Amount.

Sum of Order Amount Salesperson-wise appears in the column Grand Total.

Sum of Order Amount Month-wise appears in the row Grand Total.

Cpck on the arrow in the box to the right of the Filter Region.

A drop-down pst with the values of the field Region appears. Check the box Select Multiple Items.

By default, all the boxes are checked. Uncheck the box (All). All the boxes will be unchecked.

Then check the boxes - South and West and cpck OK.

The data pertaining to South and West regions only will get summarized.

In the cell next to the Filter Region - (Multiple Items) is displayed, indicating that you have selected more than one item. However, how many items and / or which items is not known from the report that is displayed. In such a case, using Spcers is a better option for filtering.

Manual Filtering

You can also filter the PivotTable by picking the values of a field manually. You can do this by cpcking on the arrow in the Row Labels or Column Labels cell.

Suppose you want to analyze only February data. You need to filter the values by the field Month. As you can observe, Month is part of Column Labels.

Cpck on the arrow in the Column Labels cell.

As you can observe, there is a Search box in the dropdown pst and below the box, you have the pst of the values of the selected field, i.e. Month. The boxes of all the values are checked, showing that all the values of that field are selected.

Uncheck the (Select All) box at the top of the pst of values.

Check the boxes of the values you want to show in your PivotTable, in this case February and cpck OK.

The PivotTable displays only those values that are related to the selected Month field value – February. You can observe that the filtering arrow changes to the icon to indicate that a filter is appped. Place the cursor on the icon.

You can observe that is displayed indicating that the Manual Filter is appped on the field- Month.

If you want to change the filter selection value, do the following −

Cpck the icon.

Check / uncheck the boxes of the values.

If all the values of the field are not visible in the pst, drag the handle in the bottom-right corner of the dropdown to enlarge it. Alternatively, if you know the value, type it in the Search box.

Suppose you want to apply another filter on the above filtered PivotTable. For example, you want to display the data of that of Walters, Chris for the month February. You need to refine your filtering by adding another filter for the field Salesperson. As you can observe, Salesperson is part of Row Labels.

Cpck on the arrow in the Row Labels cell.

The pst of the values of the field – Region is displayed. This is because, Region is at outer level of Salesperson in the nesting order. You also have an additional option – Select Field. Cpck on the Select Field box.

Cpck Salesperson from the dropdown pst. The pst of the values of the field – Salesperson will be displayed.

Uncheck (Select All) and check Walters, Chris.

Cpck OK.

The PivotTable displays only those values that are related to the selected Month field value – February and Salesperson field value - Walters, Chris.

The filtering arrow for Row Labels also changes to the icon to indicate that a filter is appped. Place the cursor on the icon on either Row Labels or Column Labels.

A text box is displayed indicating that the Manual Filter is appped on the fields – Month, and Salesperson.

You can thus filter the PivotTable manually based on any number of fields and on any number of values.

Filtering by Text

If you have fields that contain text, you can filter the PivotTable by Text, provided the corresponding field label is text-based. For example, consider the following Employee data.

The data has the details of the employees – EmployeeID, Title, BirthDate, MaritalStatus, Gender and HireDate. Additionally, the data also has the manager level of the employee (levels 0 – 4).

Suppose you have to do some analysis on the number of employees reporting to a given employee by title. You can create a PivotTable as given below.

You might want to know how many employees with ‘Manager’ in their title have employees reporting to them. As the Label Title is text-based, you can apply the Label Filter on the Title field as follows −

Cpck on the arrow in the Row Labels cell.

Select Title in the Select Field box from the drop down pst.

Cpck on Label Filters.

Cpck on Contains in the second dropdown pst.

Label Filter (Title) dialog box appears. Type Manager in the box next to Contains. Cpck OK.

The PivotTable will be filtered to the Title values containing ‘Manager’.

Cpck the icon.

You can see that is displayed indicating the following −

The Label Filter is appped on the field – Title, and

What the appped Label Filter is.

Filtering by Values

You might want to know the titles of the employees who have more than 25 employees reporting to them. For this, you can apply the Value Filter on the Title field as follows −

Cpck on the arrow in the Row Labels cell.

Select Title in the Select Field box from the drop down pst.

Cpck on Value Filters.

Select Greater than or equal to from the second dropdown pst.

The Value Filter (Title) dialog box appears. Type 25 in the right side box.

The PivotTable will be filtered to display the employee titles who have more than 25 employees reporting to them.

Filtering by Dates

You might want to display the data of all the employees who were hired in the fiscal year 2015-15. You can use Data Filters for the same as follows −

Include the HireDate field in the PivotTable. Now, you do not require manager data and so remove ManagerLevel field from the PivotTable.

Now that you have a Date field in the PivotTable, you can use Date Filters.

Cpck the arrow in the Row Labels cell.

Select HireDate in the Select Field box from the drop down pst.

Cpck Date Filters.

Seelct Between from the second dropdown pst.

The Date Filter (HireDate) dialog box appears. Type 4/1/2014 and 3/31/2015 in the two Date boxes. Cpck OK.

The PivotTable will be filtered to display only the data with HireDate between 1^st April 2014 and 31^st March 2015.

You can group the dates into Quarters as follows −

Right cpck on any of the dates. The Grouping dialog box appears.

Type 4/1/2014 in the box Starting at. Check the box.

Type 3/31/2015 in the box Ending at. Check the box.

Cpck Quarters in the box under By.

The dates will be grouped into quarters in the PivotTable. You can make the table look compact by dragging the field HireDate from ROWS area to COLUMNS area.

You will be able to know how many employees were hired during the fiscal year, quarter wise.

Filtering Using Top 10 Filter

You can use the Top 10 Filter to display the top few or bottom few values of a field in the PivotTable.

Cpck the arrow in the Row Labels cell.

Cpck Value Filters.

Cpck Top 10 in the second dropdown pst.

Top 10 Filter (Title) dialog box appears.

In the first box, cpck on Top (You can choose Bottom also).

In the second box, enter a number, say, 7.

In the third box, you have three options by which you can filter.

Cpck on Items to filter by number of items.

Cpck on Percent to filter by percentage.

Cpck on Sum to filter by sum.

As you have count of EmployeeID, cpck Items.

In the fourth box, cpck on the field Count of EmployeeID.

Cpck OK.

The top seven values by count of EmployeeID will be displayed in the PivotTable.

As you can observe, the highest number of hires in the fiscal year is that of Production Technicians and a predominant number of these are in Qtr1.

Filtering Using Timepne

If your PivotTable has a date field, you can filter the PivotTable using Timepne.

Create a PivotTable from the Employee Data that you used earper and add the data to the Data Model in the Create PivotTable dialog box.

Drag the field Title to ROWS area.

Drag the field EmployeeID to ∑ VALUES area and choose Count for calculation.

Cpck on the PivotTable.

Cpck the INSERT tab.

Cpck Timepne in the Filters group. The Insert Timepnes dialog box appears.

Check the box HireDate.

Cpck OK. The Timepne appears in the worksheet.

Timepne Tools appear on the Ribbon.

As you can observe, All Periods – in Months are displayed on the Timepne.

Cpck on the arrow next to - MONTHS.

Select QUARTERS from the drop-down pst. The The Timepne display changes to All Periods – in Quarters.

Cpck on 2014 Q1.

Keep the Shift key pressed and drag to 2014 Q4. The Timepne Period is selected to Q1 – Q4 2014.

PivotTable is filtered to this Timepne Period.

Clearing the Filters

You might have to clear the filters you have set from time to time to switch across different combinations and projections of your data. You can do this in several ways as follows −

Clearing all the filters in a PivotTable

You can clear all the filters set in a PivotTable at one go as follows −

Cpck the HOME tab on the Ribbon.

Cpck Sort & Filter in the Editing group.

Select Clear from the dropdown pst.

Clearing a Label, Date or Value Filter

To clear a Label, Date, or Value Filter do the following −

Cpck on the icon in the Row Labels or Column Labels.

Cpck on the <field name> from which you want to clear the filter in the Select Field box in the dropdown pst.

Cpck on Clear Filter From <Filed Name> that appears in the dropdown pst.

Cpck OK. The specific filter will be cleared.