Chapter 9. Working with Excel Pivot Tables
A pivot table is an analysis tool that allows you to create an interactive view of your dataset. We call this view a pivot table report. With a pivot table report, you can quickly and easily categorize your data into groups, summarize large amounts of data into meaningful information, and perform a wide variety of calculations in a fraction of the time it takes by hand. But the real power of a pivot table report is that you can interactively drag and drop fields within your report, dynamically changing your perspective and recalculating totals to fit your current view.
A pivot table is composed of four areas, the Values area, the Row Area, the Column Area, and the Filter Area. The values area is the area that calculates and counts data. The data fields that you drag and drop here are typically those that you want to measure, such as Sum of Revenue, Count of Units, or Average of Price.
The row area displays the unique field values down the rows of the left side of the pivot table. The row area typically has at least one field, although it’s possible to have no fields. The types of data fields that you would drop here include those that you want to group and categorize, such as, Products, Names, and Locations.
The column area displays the unique field values across the top of the pivot table. The column area is ideal for creating a data matrix or showing trends over time.
The filter area is an optional set of one or more drop-downs at the top of the pivot table. Placing data fields into the filter area allows you to filter the entire pivot table based on your selections. The types of data fields that you’d drop here include those that you want to isolate and focus on; for example, Region, Line of Business, and Employees.
The Anatomy of a Pivot Table
Creating a Pivot Table
Before you create a pivot table, you should ask yourself two questions; “What am I measuring?” and “How do I want to see it?” The answer to these questions gives you some guidance when determining which areas you will place your data fields.
In this scenario, we want to see dollar sales by market. This means we need a Sales field and a Market field. How do you want to see that? We want markets to go down the left side of the report and dollar sales to be calculated next to each market.
Click any single cell inside your data table.
Select the Insert tab in the Ribbon, and then click the PivotTable command.
This activates the Create PivotTable dialog box. Specify the location of your source data, and then click the OK button.
Observe that you have an empty pivot table report on a new worksheet. Next to the pivot table, you will see the PivotTable Field List.
Note: Pivot Table Default Location
Note that in the Create PivotTable dialog box, the default location for a new pivot table is New Worksheet. You can change this by selecting the Existing Worksheet option and specifying the worksheet where you want the pivot table placed.
Find the Market field in field selector and place a check next to it. Note that the Market field is automatically placed in the Row area of the pivot table.
Scroll down the PivotTable Field List and find the Sales Amount field. Place a check next to it and note the Sales Amount field is automatically placed in the Values area.
Click off your pivot table to make the PivotTable Field List disappear. Observe your first pivot table.
Note: How Excel Knows where to Place Data
Placing a check next to any field that is non-numeric (text or date) automatically places that field into the row area of the pivot table. Placing a check next to any field that is numeric automatically places that field in the values area of the pivot table.
Tip: Activating the PivotTable Field List
The PivotTable Field List typically activates when you click anywhere on your pivot table. If clicking the pivot table doesn’t activate the PivotTable Field List dialog box, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Field List.
Rearranging a Pivot Table
The nifty thing about pivot tables is that you can add as many layers of analysis as made possible by the fields in your source data table. For instance, if we wanted to show the dollar sales each market earned by product category, we could simply drag the Product Category field to the Column area.
Click anywhere on your pivot table to reactivate the PivotTable Field List.
Find the Product Category field, click it and drag it to the Column area in the Pivot Table Field List.
Click off your pivot table to make the PivotTable Field List disappear, and note how your pivot table now shows a matrix style view.
Tip: Dragging Fields from One Area to Another
You’re not limited to only dragging fields from the field list. You can drag fields from one area to another. For example, you can drag the product category field from the column area to the row area. This is a fairly powerful benefit in that it lets you experiment with the look and feel of your pivot table reports.
Refreshing Pivot Table Data
When you create a pivot table, Excel takes a snapshot of your data source and stores it in a pivot cache. A pivot cache is a special memory container. This is what your pivot table is connected to. This means that your pivot table report and your data source are disconnected.
The benefit of this is optimization. Any changes you make to the pivot table report, such as rearranging fields, adding new fields, or hiding items, are made rapidly and with minimal overhead. However, because your pivot table is working from a snapshot of your data source, any changes you make to your data are not picked up by your pivot table report until you take another snapshot. This is called “refreshing” your pivot table.
Right-click anywhere in your pivot table.
Select the Refresh option.
Note: The PivotTable Tools Contextual Tab
When you click on your pivot table, Excel activates the PivotTable Tools contextual tab. There, you see an Options tab and a Design tab. Click the Options tab and choose the Refresh command. These tabs expose a variety of commands you can use to manage and work with pivot tables.
Adding a Report Filter
Often times, you’re asked to produce reports for one particular region, market, product, and so on. Instead of working hours and hours building separate reports for every possible analysis scenario, you can leverage pivot tables to help create multiple views of the same data. For example, you can do so by creating a region filter in your pivot table.
Click anywhere on your pivot table to reactivate the PivotTable Field List.
Find the Region field, click it and drag it to the Report Filter area in the Pivot Table Field List.
Note that your pivot table now has a filter drop-down for Region.
Tip: Using a Field’s Context Menu to Move It
In addition to dragging, you can also move a field into the different areas of the pivot table by clicking the black triangle next to the field name and then selecting the desired area.
Once you have a report filter on your pivot table, you can click the filter drop-down and select the desired data item.
Note how the pivot table responds by showing you only the data for the selected item.
Tip: Selecting Multiple Data Items
When you click the filter drop-down to select a data item, you will notice a check box labeled Select Multiple Items. Clicking this check box allows you to select more than one data item by which you can filter your report.
Adding Pivot Table Data
Sometimes, the data source that feeds your pivot table changes in structure. For example, you may have added or deleted rows or columns from your data table. These types of changes affect the range of your data source, not just a few data items in the table. In these cases, performing a simple Refresh of your pivot table won’t do. You have to update the range being captured by the pivot table.
Click anywhere in your pivot table to activate the PivotTable Tools contextual tab.
Select the Change Data Source command in the Options tab.
The Change PivotTable Data Source dialog box activates. Here, you supply the new data range for the pivot table.
Click the OK button to confirm the change. Your pivot table automatically refreshes to show the newly referenced data.
Changing the Pivot Table Layout
Excel 2010 gives you a choice of three layouts for your pivot tables. The three layouts are the Compact Form, Outline Form, and Tabular Form. Although no layout stands out as being better than the others, the most popular layout is, by far, the Tabular Form. This is mainly because the tabular layout was the one that existed in previous versions of Excel, so most people who have seen pivot tables in the past are used to that layout.
Show your pivot table in the Compact layout. Go to the Design tab, click on the Report Layout drop-down and select Show in Compact Form.
Show your pivot table in Outline layout. Go to the Design tab, click on the Report Layout drop-down and select Show in Outline Form.
Show your pivot table in Tabular layout. Go to the Design tab, click on the Report Layout drop-down and select Show in Tabular Form.
Customizing Field Names
Notice that every field in your pivot table has a name. The fields in the row, column, and filter areas inherit their names from the data labels in your source table. The fields in the Values area are given a name, such as Sum of Sales Amount. There will often be times when you might prefer another name for your fields. For instance, you may want your Value field called Total Sales instead of Sum of Sales Amount.
Right-click the field Sum of Sales Amount.
Select Value Field Settings.
Enter the new name in the Custom Name input box and click OK to confirm.
Note that the name of your pivot field changed.
Note: Pivot Field Naming Restriction
You cannot rename a pivot field to the same name used in your source table. To get around this, you can name the field and add a space to the end of the name. For example, Excel considers Sales Amount (followed by a space) to be different from Sales Amount. This way you can use the name you want, and no one will notice it’s any different.
Applying Numeric Formats to Data Fields
Numbers in pivot tables can be formatted to fit your needs (that is, formatted as currency, percentage, or number). You can easily control the numeric formatting of a field using the Value Field Settings dialog box.
Right-click any value within the target field. For example, if you want to change the format of the values in the Sales Amount field, right-click any value under that field.
Select Value Field Settings.
The Value Field Settings dialog box appears. Click the Number Format button.
The Format Cells dialog box activates. Apply the number format you desire, just as you normally would on your spreadsheet. Click OK to apply the changes.
After you set the formatting for a field, the applied formatting will persist even if you refresh or rearrange your pivot table.
Tip: Getting to Field Settings from the Ribbon
You can also get to the Value Field Settings dialog box via the Ribbon. Click the Field Settings command under the Options tab.
Changing Summary Calculations
When creating your pivot table report, Excel will, by default, summarize your data by either counting or summing the items. Instead of Sum or Count, you might want to choose functions, such as Average, Min, Max, and so on. You can easily change the summary calculation for any given field by following these steps.
Right-click any value within the target field.
Select Value Field Settings.
The Value Field Settings dialog box appears. Choose the type of calculation you want to use from the list of calculations, then click OK to confirm.
Note that the pivot table now shows your chosen calculation.
Note: How Excel Chooses Sum or Count
When you click on a numeric field in the PivotTable Field List, Excel automatically places that field in the Values area. However, Excel doesn’t necessarily apply a Sum to that field. If all the cells in a column contain numeric data, Excel chooses a Sum calculation by default. However, if just one cell in that same column is either blank or contains text, Excel chooses the Count calculation.
Showing and Hiding Data Items
A pivot table summarizes and displays all the records in your source data table. There may, however, be situations when you want to inhibit certain data items from being included in your pivot table summary. In these situations, you can choose to hide a data item. In terms of pivot tables, hiding doesn’t just mean preventing the data item from being shown on the report, hiding a data item also prevents it from being factored into the summary calculations. For example, you can hide the Canada market to see only sales for U.S. markets.
Click on the drop-down for the field you are filtering—in this case, the Market field.
Remove the check from the data item you want hidden. Here, Canada is being removed so that only U.S. sales are calculated.
After the filter has been applied, you’ll note that not only is the Canada market hidden, but the grand total has recalculated to show the total of U.S. markets only.
Tip: Clear Applied Filters
To return a pivot field to its normal unfiltered state, right click on any value for that field and select Filter -> Clear Filter from [field name]. To clear all the filters in the pivot table at one time, go to the Options tab, click the Clear command, and then click Clear All.
Sorting Your Pivot Table
By default, items in each pivot field are sorted in ascending sequence based on the item name. Excel gives you the freedom to change the sort order of the items in your pivot table. Like many actions you can perform in Excel, there are dozens of different ways to sort data within a pivot table. The easiest way is to apply the sort directly in the pivot table.
Right-click any value within the target field.
Select Sort followed by the sort direction. In this case, the data is sorted on Sales Amount with the largest numbers at the top.
Note that the pivot table now sorts the values per your instructions.
Note: Sorting Persists in a Pivot Table
When you sort data in a standard worksheet, it’s really a one-time event. That is to say, if you add data to your data table after sorting, you will need to sort again. In a pivot table, however, the sorting persists. If new data is introduced to a sorted pivot table, the new value is automatically sorted and based on the sort rules you implement. There is no need to reapply the sort.
