Filtering a pivot table

Pivot tables offer a lot of flexibility in viewing data. Each column and row can be filtered to show a more specific view of the data.

Let's begin by building a pivot table that answers the question, "What were the sales for each region?" We'll then add a filter that will let us see sales for individual states.

With the new worksheet created and named, we're ready to add fields to the pivot table.

This pivot table is small and basic. But it answers the question we asked of our data, "What were the sales for each region?" Before going any further with this pivot table, let's format the values to show as currency. 

Before we close the Value Field Settings dialog box, let's create a custom name to replace Sum of Gross Sales.

Adding a filter to a pivot table

Now that the pivot table has been created and formatted, we're ready to add a filter. Pivot tables can be filtered in two ways: Using the drop-down menu next to the row or column headers or adding a field to the Filters area. Multiple filters can be applied at the same time. 

To begin, let's add the State field to the Filters area. This will allow us to see the data related to specific states. 

The State field filter appears above the pivot table. 

We can use the drop-down menu to select the specific states we wish to see. 

The pivot table adjusts and now shows a single value, $9,460,359. The row is labeled Pacific because that's the region that contains California. 

The filter above the pivot table now shows the abbreviation for California, CA. The drop-down menu has also changed. It now shows a small funnel. This is a visual indicator that the pivot table has been filtered. 

NOTE: In Microsoft Excel for macOS, the filter pop-up remains open. 

Before we move on, let's remove that filter and see all of the sales totals again.

The filter is removed and all of the total sales are visible in the pivot table.

Filtering a pivot table using multiple criteria

There may be other questions we want this data to answer. How much of the total sales is each sales representative responsible for? Which sales representatives in the Southwest region had more than $2,000,000 in sales? We'll add some fields to our pivot table and use filters to get the data we need. 

The pivot table shows the sales representatives and the sales each person produced. While the total sales for each region remain, using Outline Form moved these totals to the top of each region. 

With the pivot table set up and containing the data we need, let's use filters to find specific facts. 

The sorting and filtering options appear: 

These options allow us to sort the pivot table alphabetically in ascending (A to Z) or descending (Z to A) order by the Region field. We can also filter by labels or values. The bottom of the menu gives us checkboxes to show or hide a region from the pivot table. 

We want to see only the Southwest region. To do this, we'll hide the Central and Pacific regions.

Filtering by value

With the pivot table filtered to show only the data from the Southwest region, let's use another filter to see which sales representatives had more than $2,000,000 in sales. To accomplish this, we will use a Value Filter. This type of filter will allow us to filter the data based on numeric values.

NOTE: While the processes are similar, there are some differences in how value filters work in Microsoft Excel for Windows and Microsoft Excel for macOS. These differences are explained in the following steps.

We can use these options to filter the pivot table to show data equal or not equal to a specific number, greater than or less than a value, between or not between two values, or even the top ten values. 

The pivot table now shows the Sales Reps in the Southwest region who had more than $2,000,000 in Total Sales.

Now that we've explored various types of filters, let's see how using slicers can make filtering data even easier.

Filtering a pivot table using slicers

Slicers are tools that add a visual element to the filtering process. Using slicers to filter data can make the process faster, especially if the data needs to be filtered often and by different criteria. With a slicer, it is easy to see how the data is being filtered. 

Let's add slicers for the Region and State fields. 

The Insert Slicers dialog box opens:

The possible slicers match the fields of the pivot table. We will choose the Region and State slicers.

The slicers appear as objects in the worksheet. They may be overlapping and need to be rearranged. Because the slicers are outside the pivot table area, the field list has disappeared. 

Let's move the slicers to the right of the pivot table. 

The slicers should look similar to this: 

The Region slicer has "buttons" for each of the three regions: Central, Pacific, and Southwest. The State slicer has "buttons" for all of the states included in the Sales worksheet. 

The pivot table adjusts and now shows only the sales representatives and total sales for the Pacific region. The slicers have also changed. 

In the Region slicer, the Pacific button is selected. In the upper right corner, the small funnel icon indicates that the pivot table is filtered by this field. In the State slicer, the states within the Pacific region are selected. The states not in this region are grayed out. 

We can use the State slicer to filter the pivot table even more.

The pivot table now shows the one sales representative in Oregon and the Pacific region.

When the slicers are no longer useful, they can be easily removed. The pivot table will not be affected by the loss of the slicers. 

The pivot table remains filtered by Region and State.