Microsoft Office Tutorials and References
In Depth Information
• The person 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
This workbook, named bank accounts.xlsx, is available at this topic's website.
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 pro-
cess of answering questions about the data. Following are a few questions that may be of interest to the bank's
• What is the daily total new deposit amount for each branch?
• Which day of the week accounts for the most deposits?
• 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 with 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
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 sum-
mary represents one of dozens of summaries that you can produce from this data.
Figure 18-3 shows another pivot table generated from the bank account data. This pivot table uses the drop-
down filter for the Customer field (in row 2). 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.