39 6.2 Sort & Filter PivotTable Data
Emese Felvegi; Noreen Brown; Barbara Lave; Julie Romey; Mary Schatz; Diane Shingledecker; and Robert McCarn
Learning Objectives
- Sort and filter data in a PivotTable.
- Manipulate Value Field Settings.
- Use slicers.
- Interpret outputs.
In the previous part of this chapter, we learned how to create a PivotTable from a range of data and enable Fields to output useful information. In this part, we will be diving deeper into how to make the most of this data and learning how to manipulate the output in various ways to get more and more impactful information.
By placing the “STABBR” Field in the Rows Area of the Pivot Table Fields Tab, we were able to get a list of all unique State abbreviations in the STABBR column. Subsequently, by placing the “INSTNM” Field in the Value Area of the PivotTable Fields Tab, we were able to derive the number of institutions in each State. Using these two Fields in conjunction, we can see valuable information not easily derived from a large data-set. (Consider much faster using a PivotTable is than using an Excel Table and Subtotals.)
Sort and Filter Data in a PivotTable
Like with normal Excel Tables, PivotTables share the ability to sort data in columns. We can use this function to easily visualize information from smallest to largest or in alphabetical order (in ascending or descending order based on number, dates, or text). The easiest way to do this is shown in Figure 6.8: by right-clicking on the column of data that you wish to sort by and hovering over the sort tab and selecting how you wish to sort.
Filtering
There are instances in which we will not want to see the results of the entire data-set at one time. In these cases, we are usually looking for information restricted by one or more variables to see how the information changes with those variables. For now, try for yourself to see how the PivotTable changes when you add a Field to the Filters Area.
Start by dragging the Field “DISTANCEONLY” into the Filters Field. At the top of the PivotTable, you will see a new box with a dropdown menu that says “(All)”. This means that no data is being restricted yet. Click on the downward facing arrow and look at the parameters it shows you. You should see (All), 1, 0, and NULL. (All) is the current selection of every option the column contains. NULL is an indicator that Excel uses to state that there is no information in a cell – Sometimes in large datasets the creators do not or cannot collect any relevant information. In such cases, they show they have made an attempt and have come up with a value that would translate to Not Applicable or Unavailable rather than leave that entry blank. For meanings of 1 and 0 refer to the Data Dictionary or last Chapter Part for the definition of a Boolean.
Key Takeaways
1 – A Boolean parameter meaning TRUE
0 – A Boolean parameter meaning FALSE
- DISTANCEONLY: Set the Filter to only display TRUE values. This means that ONLY institutions classifie