A Pivot Table Example

The best way to understand the concept of a pivot table is to see one. Start with Figure 18-1, which shows a portion of the data used in creating the pivot table in this chapter.

Figure 18-1: This table is used to create a pivot table.

This table comprises 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 when the account was opened

• The opening amount

• The account type: CD, checking, savings, or IRA (Individual Retirement Account)

• 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

On the CD 

This workbook, named  bank accounts.xlsx, is available on the companion CD-ROM.

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:

• What is the daily total new deposit amount for each branch?

• How many accounts were opened at each branch, broken down by account type?

• What's the dollar distribution of the different account types?

• What types of accounts do tellers open most often?

• How does the Central branch compare to the other two branches?

• In which branch do tellers open the most checking accounts for new customers?

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.

By the way, I provide answers to these questions later in the chapter by presenting several additional pivot tables created from the data.

Figure 18-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 18-2: A simple pivot table.

Figure 18-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.

Figure 18-3: A pivot table that uses a report filter.