Microsoft Office Tutorials and References
In Depth Information
A Pivot Table Example
h The opening amount
h The account type: CD, checking, savings, or IRA (Individual Retirement Account)
h Who opened the account: a teller or a new-account representative
h The branch at which it was opened: Central, Westside, or North County
h The type of customer: An existing customer or a new customer
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:
h What is the daily total new deposit amount for each branch?
h Which day of the week accounts for the most deposits?
h How many accounts were opened at each branch, broken down by account type?
h What’s the dollar distribution of the different account types?
h What types of accounts do tellers open most often?
h How does the Central branch compare to the other two branches?
h 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. Keep in mind that no formulas are
involved. 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-3 shows another pivot table generated from the bank data. This pivot table uses the
drop-down Report Filter for the Customer field (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
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.