While creating a new pivot table in Excel, you will see that Excel automatically adds drop-down keys to the Report Filter field, along with the labels for the row and column fields. These drop-down buttons in Excel are known as filter buttons, which enables you to filter all with specific entries in the field. The column and row fields sort the entries in the table.
If more than one row or column field is added to the pivot table, then Excel adds collapse buttons that can be used to hide the subtotal values for a specific secondary field. After clicking on the collapse button in the table, it instantly becomes an expand button that can be clicked to redisplay the subtotals of that one secondary field.
Filtering Pivot Tables in Excel
In the Excel pivot table, the most significant filter buttons are added to the field/fields named as the pivot table FILTERS. On selecting a specific option from the drop-down lists that are attached to one of the filter buttons, the summary data for that selected subset displays in the pivot table.
For instance, in the Excel pivot table that plies the Gender field from the Worker Data file as the Report Filter field, one can present the sum of the women’s or men’s salaries by department and position in the pivot table by doing either of the following:
- Click on the Gender field’s filter key. Then click on M which is on the drop-down list before clicking on the OK button to see the sum total of the men’s salaries by the department.
- Click on the Gender field’s filter key. Then click on the F which is on the drop-down list before clicking on the OK button to see the sum total of the women’s salaries by the department.
Later, when you want to redisplay the salaries for all the employees, you can reselect the option All on the drop-down filter list of the Gender field before clicking OK.
Sorting Pivot Tables in Excel
The summary values in a pivot table can be instantly reordered by sorting the table on one or more than one of its row or column fields. To sort a pivot table, click on the filter button for the row or column field that you want to use in the categorizing. Then click on the Sort Z to A option or the Sort A to Z option that is at the top of the field’s drop-down list.
If you want the table to sort the labels in an alphabetical manner, or from the smallest to largest value or, from the oldest to newest date, click on the Sort A to Z option and the Sort Z to A option if you want vice-versa.
Attend the Compliance Prime webinar to learn more about the Filtering and Sorting Pivot Table Data in Excel.
