Now that you know how to create a PivotTable and how to pivot data, it is time to learn about some of the other options that Excel provides for manipulating your PivotTable data. In the following pages you will learn about filtering, sorting, grouping, and refreshing your PivotTable data. We will also explore how to expand and collapse data, and how to edit a data source.
Expanding and Collapsing Data
Have a close look at the following PivotTable.
You can see that there are multiple rows that show data for different months, different
salespeople, and different product types. There are also profit figures in the column on the far right, as well as profit subtotals (in bold in the Sum of Profit column).
To help make the table more succinct and easier to interpret, we can use the expand and collapse buttons.
When you see a button in your table with a minus (-) sign, it means that the field associated with that button is fully expanded. This button is commonly called a collapse button, because when you click it the expanded data will collapse. In the previous example, all fields in the table are expanded. To simplify the presentation of the table and summarize the data, we can click on the collapse buttons of our choice.
In the following image, we can see the profit generated by each salesman broken down by product type.
Perhaps this is a little too much information. For a clearer view, we can click the buttons next to the names of each salesman. We will lose the details about what product types were sold to generate what profits, but the resulting table becomes less cluttered:
We now have a succinct view of the profit for each salesman, as well as the total profits for Month 1. To show the hidden data, simply click the expansion button (+ sign).
Here is what the table looks like if we expand the data for salesman A. Smith:
Now you can see the product type data for only A Smith, as well as a subtotal for A Smith.
Note that there are also collapse and expand tools on the PivotTable Tools – Options tab:
To use these tools, just select a field in your PivotTable that has multiple rows of data associated with it, and click the Expand (+) or Collapse (-) button as required.
By expanding and collapsing your PivotTable data intelligently, you can create a view of the table data that best suits your needs.
Filtering Data
Even after you create your PivotTable and then pivot the data the way you want, you may still find that the information isn’t summarized just the way you need it to be. If you end up in this situation, the Excel filtering tools may be able to help you find exactly what you are looking for.
Like other Excel tables, a PivotTable can contain AutoFilters. In the following PivotTable, for example, you will see an AutoFilter arrow next to the Row Labels column heading.
Click the pull-down arrow to display a list of filtering commands:
The available filtering commands are shown in the red box. The list of checkmarks represents each of the unique identifiers for the table rows; names of salesmen in this case. There is also a checkbox that allows you to select or deselect all items.
To filter out the data for a given salesman, just clear the corresponding checkbox:
In the example below, B. Doe has been filtered out.
Take note that when a row is filtered from the table, the grand total is adjusted accordingly.
The appearance of the pull-down arrow button will also change when a filter has been applied.
The button now has a funnel icon indicating that a filter has been applied. If you click the pull-down button after a filter is applied, you will see that the Clear Filter option has become available:
Clicking this option will remove the filter and the corresponding data will once again appear in the table. You can also manually re-check items to show the corresponding data.
There are two additional options below the Clear Filter option: Label Filters and Value Filters.
Clicking on either of these options will provide you with another menu that enables the creation of filters with user specified criteria. Here is what the Label Filters menu looks like:
If you choose an option from the Label Filters menu, a dialog box will appear that prompts you to enter the criteria for your filter:
In this instance, combining the letter “M” for the “begins with” filter will filter out all rows that do not have a label (salesman’s name) that begins with M.
Note that you can click the pull-down arrow to see a drop list with more criteria options:
When you click OK, the filter will be applied.
The Value Filter works in much the same way as the Label Filters, except that they will filter based on criteria that you apply to the numerical data in your table:
The criteria in this dialog should filter all table entries where the Sum of Sales value is greater than 650. Here is the PivotTable before and after the filter has been applied:
Before After
A PivotTable can contain hundreds or even thousands of rows of data. If you want to filter everything out except for a particular set of data that you specify, use the search box at the bottom of the AutoFilter menu:
Searching for a particular row label will filter out all of the data associated with the labels that do not match, essentially finding the label that you are searching for.
Finally, it is common to have a PivotTable in which there are multiple fields as row labels. For example, take the following PivotTable where the filtering options under the pull-down arrow are associated with the salesmen’s names rather than the product type:
What would you do if you wanted your filters to be based on product type instead of salesmen?
In a PivotTable, the filter options will be associated with the row labels that are in bold font (the names of salesmen in this case).
To change this, just rearrange the fields in the Row Labels box in the PivotTable Field List:
Because Salesman is above Product in the Row Labels box, the salesmen’s names are in bold.
Drag the product field up so it is in top position in the box, and the table will display product type labels in bold font:
Now the filter options under the pull-down arrow will apply to product type:
You can also choose which row label to filter on by using the Select Field command in the AutoFilter menu:
This option will only be available if there is more than one row label available in the PivotTable.
Sorting Data
If you know how to filter data in PivotTables, sorting data is just as easy. If you click the same pull-down arrow that you used for filtering, you will see a few sorting options on the menu as well:
Choosing the “Sort A to Z” option will sort the table rows in ascending order. Choosing “Sort Z to A” will sort the table rows in descending order. If there are multiple row fields in the table, specify which field to sort by using the “Select Field” drop list.
If you want to sort your table on something other than just the row labels, choose More Sort Options from the menu.
When you choose this option, a Sort dialog will appear.
Once again, options to sort in ascending or descending order are available with radio buttons.
In addition, once you select a radio button, you can click the corresponding pull-down arrow to choose a field:
Here is the resulting table.
Notice that when you have applied a sort option to the table, the pull-down button will display a tiny arrow: . An upward arrow indicates an ascending sort, while a downward arrow indicates a descending sort.
There is also a button on the bottom of the Sort dialog labeled More Options. If you choose this button, another dialog will appear with an AutoSort option. If this option is selected, the data will be sorted automatically every time the PivotTable is updated:
There are also sort options available on the PivotTable Tools – Options tab:
To sort data based on a specific row label in ascending order, click on data in that row label, and then click the sort ascending (A to Z) button. For example, if we click on the name of a salesman in the table, and then click on the sort ascending button (smallest to largest):
The table will then look like this:
There is one more sort tool available in the Sort and Filter group of the Options tab:
Choosing this button will display another dialog with more options for sorting:
This dialog lets you specify a smallest to largest sort, or largest to smallest. It also lets you specify direction the data will be sorted in (top to bottom or left to right).
Note that the dialog is called Sort by Value. This is because a numerical value was selected in the PivotTable before invoking the dialog. If we first select a row label like salesman or product, the dialog will look a bit different:
Grouping Data
When you are working with PivotTables, there are also grouping tools available on the Options tab:
These tools are very similar to the grouping tools discussed in Lesson 1.1. To use them, first select a range of data that you want to group or ungroup, and then click the appropriate button.
For example, here is a PivotTable that is somewhat awkward due to its length.
To make the table more manageable, select the range of cells that contain the region labels and then click Options → Group Selection:
As a result, the data will be grouped by region in the PivotTable and there will be a “Group 1”
option with a corresponding (-) collapse button available above the row of region labels.
To collapse the group, just click the collapse button and the PivotTable will look like this:
If you want to see all of the detail of the table again, just click the expand button (+).
If you want to remove the grouping, click on the Group1 label to select it and then choose Options → Ungroup:
The grouping will be removed and the PivotTable will appear as it did originally.
Refreshing Data
PivotTables are always based on some underlying source data. That data may be in another workbook, another worksheet, or perhaps a totally different location like an external database.
The point is that the data that your PivotTable is based on can be easily changed by anyone who has access to the spreadsheet. By using the Refresh command, you can make sure your PivotTable changes accordingly when the source data has been altered.
Here is a sample table of source data.
Here is a PivotTable based on this data:
Now suppose that we remove the information for Month 3 in the source table:
At this point, the PivotTable will still display the Month 3 data exactly as it did before. To update the table, click Options → Refresh:
After performing this action, the PivotTable will reflect the changes in the source data:
This technique also works if you change the values in the source data. Study the PivotTable above for a moment, and then note the following changes to the source data.
At first, the PivotTable will not reflect the changes, but after you click the Refresh button, the PivotTable will update:
The resulting table changes look like this:
Another way to refresh is to right-click the PivotTable and click Refresh:
Editing the Data Source
It might also be the case that the actual underlying cell range of the source data has been expanded:
In this kind of situation, you should update your PivotTable by choosing Options → Change Data Source:
Performing this action will display the source data, with a dashed line indicating the range of cells that are currently included in the PivotTable. There will also be a dialog that will allow you to select a new cell range:
Now, re-select the entire range of data with your mouse, and check to make sure that it is correctly entered into the dialog:
As soon as you have successfully selected the new range, click OK. The PivotTable will be automatically updated to accommodate the new range and additional data: