1. Using Filters
As we mentioned, pivot tables can help you organize and sort through your data. One of the most effective ways to do this is with filters. Pivot tables offer several ways to filter your data, let's look at a few now.
2. Selecting row filters within a pivot table
We'll use the same pivot table, containing the average rainfall across 50 major US cities. Right now, the pivot table shows the cities in the Rows section and the total annual rainfall in millimeters in the Values section. Let's go to the pivot table editor, and scroll down to the Filters section. Click ADD, and then select which category you want to filter the table by. For now, let's choose Month. You'll notice that nothing happens immediately. We have enabled the monthly filter, but we haven't actually applied any filters yet. Next, we'll click on the arrow where it says "Showing all items". You can see that all of the months are currently checked. Let's clear them all by clicking on CLEAR at the top, and then, let's select January. Then hit OK. Now you'll see the pivot table has updated to only show the values for January. Now let's try selecting multiple months, and then finally we'll choose SELECT ALL, which applies the checkmark to all of them.
3. Selecting multiple filters using text strings
Let's try another example. This time we'll remove the monthly filter, and we'll add a filter for the cities. What if you want to filter the pivot table so that it only shows the data from cities in California? You could scroll through and manually select them, but there's a better way.
First, we are going to CLEAR everything. Next, we are going to start typing California into the search box of the filter field. As you type, you'll notice that the list of cities changes. The filter box will only show you the options that contain the text string you have entered. Once you have narrowed it down to only show cities in California, you can choose SELECT ALL, and hit OK.
4. Selecting minimum and maximum values using filters
Let's find the highest and lowest values within our data. Once you add millimeters to the Filters section, you can choose the highest or lowest value within this field.
For our last example, we will attempt to find the total rainfall for the cities that have 0 or 1 days of rainfall in one month, and then cities with at least 20 days of rainfall.
5. Let's practice!
Now let's try some examples.