ASQ CNY Conference, Syracuse NY November 3, 2012
Speaker: Mark Lewis lewism@alfred.edu 607.871.2226
You can find this handout online at www.lewisathome.com/ASQ
Tutorial for self-study: http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
Further Reading:
• Excel PivotTables and PivotCharts: Your visual blueprint for creating dynamic spreadsheets by Paul McFedries ISBN 978-0-470-59161-1 This is a very visual book to help you master pivot tables, and if I was only getting one book this would be it.
• Pivot Table Data Crunching: Microsoft Excel 2010 by Bill Jelen and Michael Alexander. ISBN 978-0-7897-4313-8 A more advanced book on pivot tables for the hard-core user.
Banking Examples:
The spreadsheet “bank accounts” 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)
Questions we have:
1. What is the daily total deposit amount for each branch?
2. How many accounts were opened at each branch, broken down by account type?
3. What’s the dollar distribution of the different account types?
4. What types of accounts do tellers open most often? (Display by both number of accounts and % of total. Use data bar conditional formatting in the % column to graphically show the percent.
5. How does the Central branch compare to the other two branches? Add a pivot chart to show this visually.
6. In which branch do tellers open the most checking accounts for new customers? Show the data both as a count and as a percent.
Stocks Example:
The stock spreadsheet contains company names, stock ticker symbol, market price, sector, industry, where listed (NYSE, AMEX, NASDAQ, OTC)
Questions we have:
1. What is the average price by sector and exchange for all stocks with a price of 20 cents or greater.
2. What is the average price by exchange and sector for all S&P 500 Stocks?
3. What is the average price by exchange for all Dow Jones Industrial Average (Dow) stocks?