The Pivot Table feature is perhaps the most technologically sophisticated component in Excel. With only a few mouse clicks, you can slice and dice a data table in dozens of different ways and get just about any type of summary you can think of.
If you haven’t yet discovered the power of pivot tables, this chapter provides an introduction, and Chapter 35 continues with many examples that demonstrate how easy it is to create powerful data summaries using pivot tables.
A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data.
For example, a pivot table can create frequency distributions and cross-tabulations of several different data dimensions. In addition, you can display subtotals and any level of detail that you want. Perhaps the most innovative aspect of a pivot table is its interactivity. After you create a pivot table, you can rearrange the information in almost any way imaginable and even insert special formulas that perform new calculations. You even can create post hoc groupings of summary items (for example, combine Northern Region totals with Western Region totals). And the icing on the cake: With a few mouse clicks, you can apply formatting to a pivot table to convert it into an attractive report.
One minor drawback to using a pivot table is that, unlike a formula-based summary report, a pivot table does not update automatically when you change information in the source data. This drawback doesn’t pose a serious problem, however, because a single click of the Refresh button forces a pivot table to update itself with the latest data.
Pivot tables have been around since Excel 97. Unfortunately, many users overlook this feature because they think it’s too complicated. The pivot table feature in Excel 2007 is vastly improved, and creating and working with pivot tables is easier than ever.
If you’ve used pivot tables in a previous version of Excel, be aware that the rarely used option to create a pivot table from “multiple consolidation ranges” is no longer available.
The best way to understand the concept of a pivot table is to see one. Start with Figure 34.1, which shows a portion of the data used in creating the pivot table in this chapter.
This table consists of a month’s worth of new account information for a three-branch bank. The table contains 712 rows, and each row represents a new account. The table has the following columns:
The date the account was opened
The opening amount
The account type (CD, checking, savings, or IRA)
Who opened the account (a teller or a new-account representative)
The branch at which it was opened (Central, Westside, or North County)
The type of customer (an existing customer or a new customer)
The bank accounts database contains quite a bit of information. But in its current form, the data doesn’t reveal much. To make the data more useful, you need to summarize it. Summarizing a database is essentially the process of answering questions about the data. Following are a few questions that may be of interest to the bank’s management:
You can, of course, spend time sorting the data and creating formulas to answer these questions. Often, however, a pivot table is a much better choice. Creating a pivot table takes only a few seconds, doesn’t require a single formula, and produces a nice-looking report. In addition, pivot tables are much less prone to error than creating formulas. (Later in this chapter, you’ll see several pivot tables that answer the preceding questions.)
Figure 34.2 shows a pivot table created from the bank data. This pivot table shows the amount of new deposits, broken down by branch and account type. This particular summary represents one of dozens of summaries that you can produce from this data.
Figure 34.3 shows another pivot table generated from the bank data. This pivot table uses a drop-down Report Filter for the Customer item (in row 1). In the figure, the pivot table displays the data only for Existing customers. (The user can also select New or All from the drop-down control.) Notice the change in the orientation of the table? For this pivot table, branches appear as column labels, and account types appear as row labels. This change, which took about five seconds to make, is another example of the flexibility of a pivot table.
A pivot table requires that your data is in the form of a rectangular database. You can store the database in either a worksheet range (which can be a table or just a normal range) or an external database file. Although Excel can generate a pivot table from any database, not all databases benefit.
Generally speaking, fields in a database table consist of two types:
Data: Contains a value or data to be summarized. For the bank account example, the Amount field is a data field.
Category: Describes the data. For the bank account data, the Date, AcctType, OpenedBy, Branch, and Customer fields are category fields because they describe the data in the Amount field.
A single database table can have any number of data fields and category fields. When you create a pivot table, you usually want to summarize one or more of the data fields. Conversely, the values in the category fields appear in the pivot table as rows, columns, or filters.
Exceptions exist, however, and you may find Excel’s pivot table feature useful even for databases that don’t contain actual numerical data fields. Chapter 35 has an example of a pivot table created from non-numeric data.
Figure 34.4 shows an example of an Excel range that is not appropriate for a pivot table. This range contains descriptive information about each value, but it’s not set up as a table. In fact, this range resembles a pivot table summary.
In this section, I describe the basic steps required to create a pivot table, using the bank account data. Creating a pivot table is an interactive process. It’s not at all uncommon to experiment with various layouts until you find one that you’re satisfied with.
If your data is in a worksheet range, select any cell in that range and then choose Insert Tables PivotTable, which displays the dialog box shown in Figure 34.5.
Excel attempts to guess the range, based on the location of the active cell. If you’re creating a pivot table from an external data source, you need to select that option and then click Choose Connection to specify the data source.
If you’re creating a pivot table from data in a worksheet, it’s a good idea to first create a table for the range (by choosing Insert Tables Table). Then, if you expand the table by adding new rows of data, Excel will refresh the pivot table without the need to manually indicate the new data range.
Use the bottom section of the Create PivotTable dialog box to indicate the location for your pivot table. The default location is on a new worksheet, but you can specify any range on any worksheet, including the worksheet that contains the data.
Click OK, and Excel creates an empty pivot table and displays its PivotTable Field List, as shown in Figure 34.6.
Next, set up the actual layout of the pivot table. You can do so by using either of these techniques:
Drag the field names to one of the four boxes in the PivotTable Field List.
Right-click a field name and choose its location from the shortcut menu.
In previous versions of Excel, you could drag items from the field list directly into the appropriate area of the pivot table. This feature is still available, but it’s turned off by default. To enable this feature, choose PivotTable Tools Options PivotTable Options Options to display the PivotTable Options dialog box. Click the Display tab and add a check mark next to Classic PivotTable Layout.
The following steps create the pivot table presented earlier in this chapter (see “A pivot table example”). For this example, I drag the items from the top of the PivotTable Field List to the areas in the bottom of the PivotTable Field List.
Drag the Amount field into the Values area. At this point, the pivot table displays the total of all the values in the Amount column.
Drag the AcctType field into the Row Labels area. Now the pivot table shows the total amount for each of the account types.
Drag the Branch field into the Column Labels area. The pivot table shows the amount for each account type, cross-tabulated by branch (see Figure 34.7).
Notice that the pivot table uses General number formatting. To change the number format used, select any value and choose PivotTable Tools Options Active Field Field Settings to display the Data Field Settings dialog box. Click the Number Format button and change the number format.
You can apply any of several built-in styles to a pivot table. Select any cell in the pivot table and choose PivotTable Tools Design PivotTable Styles to select a style.
You also can use the controls in the PivotTable Design Layout group to control various elements in the pivot table. For example, you can choose to hide the grand totals if you prefer.
The PivotTable Tools Options Show/Hide group contains additional options that affect the appearance of your pivot table. For example, you use the Show Field Headers button to toggle the display of the field headings.
Still more pivot table options are available in the PivotTable Options dialog box, shown in Figure 34.8. To display this dialog box, choose PivotTable Tools Options PivotTable Options Options. Or, rightclick any cell in the pivot table and choose Table Options from the shortcut menu.
Once you’ve created a pivot table, it’s easy to change it. For example, you can add further summary information by using the PivotTable Field List. Figure 34.9 shows the pivot table after I dragged a second field (OpenedBy) to the Row Labels section in the PivotTable Field List.
The following are some tips on other pivot table modifications you can make:
To remove a field from the pivot table, select it in the bottom part of the PivotTable Field List and “drag it away.”
If an area has more than one field, you can change the order in which the fields are listed by dragging the field names. Doing so affects the appearance of the pivot table.
To temporarily remove a field from the pivot table, remove the check mark from the field name in the top part of the PivotTable Field List. The pivot table is redisplayed without that field. Place the check mark back on the field name, and it appears in its previous section.
If you add a field to the Report Filter section, the field items appear in a drop-down list, which allows you to filter the displayed data by one or more items. Figure 34.10 shows an example. I dragged the Date field to the Report Filter area. The report is now showing the data only for a single day (which I selected from the drop-down list).
To demonstrate the flexibility of this feature, I’ve created some additional pivot tables. The examples use the bank account data and answer the questions posed earlier in this chapter (see “A pivot table example”).
What is the daily total new deposit amount for each branch?
Figure 34.11 shows the pivot table that answers this question.
The Branch field is in the Column Labels section.
The Date field is in the Row Labels section.
The Amount field is in the Value section and is summarized by Sum.
Note that the pivot table can also be sorted by any column. For example, you can sort the Grand Total column in descending order to find out which day of the month had the largest amount of new funds. To sort, just right-click any cell in the column to sort and choose Sort on the shortcut menu.
How many accounts were opened at each branch, broken down by account type?
Figure 34.12 shows a pivot table that answers this question.
The AcctType field is in the Column Labels section.
The Branch field is in the Row Labels section.
The Amount field is in the Value section and is summarized by Count.
The most common summary function used in pivot tables is Sum
. In this case, I changed the summary function to Count
. To change the summary function to Count, right-click any cell in the Value area and choose Summarize Data By Count from the shortcut menu.
What’s the dollar distribution of the different account types?
Figure 34.13 shows a pivot table that answer this question. For example, 253 of the new accounts were for an amount of $5,000 or less.
This pivot table is unusual because it uses only one field: Amount.
The Amount field is in the Row Labels section (grouped).
The Amount field is also in the Values section and is summarized by Count.
A third instance of the Amount field is the Values section, summarized by Percent of Total.
When I initially added the Amount field to the Row Labels section, the pivot table showed a row for each unique dollar amount. I right-clicked one of the Row Labels and selected Group. Then I used Excel’s Grouping dialog box to set up bins of $5,000 increments.
The second instance of the Amount field (in the Values section) is summarized by Count. I right-clicked a value and chose Summarize Data By Count.
I added another instance of Amount to the Values section, and I set it up to display the percentage. I used the Show Values As tab of the Data Field Settings dialog box and specified % Of Total. To display the Data Field Settings dialog box, right-click any cell and choose Summarize Data As More Options.
What types of accounts do tellers open most often?
Figure 34.14 shows that the most common account opened by tellers is a Checking account.
The AcctType field is in the Row Labels section.
The OpenedBy field is in the Report Filters section.
The Amount field is in the Values section (summarized by Count).
A second instance of the Amount field is in the Values section (summarized by % Of Total).
This pivot table uses the OpenedBy field as a Report Filter and is showing the data only for Tellers. I sorted the data so that the largest value is at the top, and I also used conditional formatting to display data bars for the percentages.
Refer to Chapter 21 for more information about conditional formatting.
How does the Central branch compare to the other two branches?
Figure 34.15 shows a pivot table that sheds some light on this rather vague question. It simply shows how the Central branch compares to the other two branches combined.
The AcctType field is in the Row Labels section.
The Branch field is in the Column Labels section.
The Amount field is in the Values section.
I grouped the North County and Westside branches together and named the group Other. The pivot table shows the amount, by account type. I also created a pivot chart for good measure.
In which branch do tellers open the most checking accounts for new customers?
Figure 34.16 shows a pivot table that answers this question. At the Central branch, tellers opened 23 checking accounts for new customers.
The Customer field is in the Report Filters section.
The OpenedBy field is in the Report Filters section.
The AcctType field is in the Report Filters section.
The Branch field is in the Row Labels section.
The Amount field is in the Values section, summarized by Count.
This pivot table uses three Report Filters. The Customer field is filtered to show only New, the OpenedBy field is filtered to show only Teller, and the AcctType field is filtered to show only Checking.