Chapter 14 Pivot Table and Charts

14.1 14.2 14.3 14.4 Introduction What Is a Pivot Table? Why Should You Use a Pivot Table? The Anatomy of a Pivot Table Values Area Row Area Column Area Report Filter Area 14.5 14.6 Creating dynamic charts by using PivotCharts Subtotals

Chapter 14 Pivot Table and Charts

14.1 Introduction
Imagine that it is 1992.You are using Lotus 12-3 or Excel 4.You have thousands of rows of transactional data, as shown in Figure. Your manager asks you to prepare a summary report showing revenue by region and product. In 1992, preparing this report was a daunting task. It required superhuman spreadsheet skills that few could master. The concept that led to todays pivot table came from the halls of the Lotus Development Corporation with a revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salas of the Advanced Technology Group at Lotus. Realizing that spreadsheets often have patterns of data, Pito concluded that if a user could build a tool that could recognize these patterns, then he could build enhanced data models. Lotus ran with the concept and started developing the nextgeneration spreadsheet. Throughout 1987, Lotus demonstrated its new program to a few companies. In 1988, Steve Jobs saw the program and immediately wanted it developed for his upcoming NeXT computer platform. The program, finally named Lotus Improv, was eventually shipped in 1991 for the NeXT platform. A version for Windows was introduced in 1993. The core concept behind Improv was that data, data views, and formulas should be encapsulated as separate entities and treated as different animals. For the first time in a spreadsheet program, a dataset was given a name that could be grouped into larger categories. This naming and grouping capability paved the way for the most powerful feature in Improv: rearranging data. With Improv, a user could define and store a set of categories and then change the view by simply dragging the category names with the mouse. The user could also create totals and group summaries. Microsoft eventually picked up on this concept in its pivot table functionality in Excel 5. Years later, with the release of Excel 97, Microsoft offered users an enhanced pivot table wizard and key improvements to pivot table functionality, such as the capability to add calculated fields. Excel 97 also opened the pivot cache to developers, fundamentally changing the way pivot tables are created and managed. Microsoft introduced the pivot chart with Excel 2000, providing users a way to represent pivot tables graphically. Since Excel 2000, changes made to pivot tables have been mainly cosmetic, much to the chagrin of pivot table fans everywhere.

14.2 What Is a Pivot Table?

Imagine that Excel is a large toolbox that contains different tools at your disposal. The pivot table is essentially one tool in your Excel toolbox. If a pivot table were indeed a physical tool that you could hold in your hand, a kaleidoscope would most accurately represent it. When you look through a kaleidoscope at an object, you see that object in a different way. You can turn the kaleidoscope to move around the details of the object. The object itself doesnt change, and its not connected to the kaleidoscope. The kaleidoscope is simply a tool you use to create a unique perspective on an ordinary object. Think of a pivot table as a kaleidoscope that is pointed at your dataset. When you look at your dataset through a pivot table, you have the opportunity to see details in your data you may not have noticed before. Furthermore, you can turn your pivot table to see your data from different perspectives. The dataset itself doesnt change, and its not connected to the pivot table. The pivot table is simply a tool you are using to create a unique perspective on your data. A pivot table allows you to create an interactive view of your dataset. We call this view a pivot table report. With a pivot table report, you can quickly and easily categorize your data into groups, summarize large amounts of data into meaningful information, and perform a wide variety of calculations in a fraction of the time it takes by hand. But the real power of a pivot table report is that you can interactively drag and drop fields within your report, dynamically changing your perspective and recalculating totals to fit your current view.
What Is a Pivot Table? . . . . . . . . . . . . . . . . . . . .11

14.3 Why Should You Use a Pivot Table?

As a rule, your dealings in Excel can be split into two categories: calculating data and shaping (formatting) data. Although many built-in tools and formulas facilitate both of these tasks, the pivot table is often the fastest and most efficient way to calculate and shape data. Lets look at one simple scenario that illustrates this point. You have just given your manager some revenue information by month, and he has predictably asked for more information. He adds a note to the worksheet and emails it back to you. He would like you to add a line that shows credits by month.

To meet this new requirement, you run a query from your legacy system that will provide the needed data. As usual, the data is formatted specifically to make you suffer. Instead of data by month, the legacy system provides detailed transactional data by day, as shown in Figure.

Your challenge is to calculate the total dollar amount of credits by month and shape the results into an extract that fits the format of the original report. The final extract should look like the data shown in Figure.

Creating the extract manually would take 18 mouse clicks and three keystrokes: Format dates to month: three clicks Create subtotals: four clicks Extract subtotals: six clicks, three keystrokes Transpose vertical to horizontal: five clicks

In contrast, creating the extract with a pivot table would take nine mouse clicks: Create the pivot table report: five clicks Group dates into months: three clicks Transpose vertical to horizontal: one click Both methods give you the same extract, which can be pasted into the final report, as shown in Figure.

Using a pivot table to accomplish this task not only cuts down the number of actions by more than half, but also reduces the possibility of human error. Over and above that, using a pivot

table allows for the quick and easy shaping and formatting of the data. What this example shows is that using a pivot table is not just about calculating and summarizing your data. Pivot tables can often help you do a number of tasks faster and better than conventional functions and formulas. For example, you can use pivot tables to instantly transpose large groups of data vertically or horizontally. You can use pivot tables to quickly find and count the unique values in your data. You can also use pivot tables to prepare your data to be used in charts. The bottom line is that pivot tables can help you dramatically increase your efficiency and decrease your errors on a number of tasks you may have to accomplish with Excel. Pivot tables cant do everything for you, but knowing how to use just the basics of pivot table functionality can take your data analysis and productivity to a new level.

14.4 The Anatomy of a Pivot Table

Because the anatomy of a pivot table is what gives it its flexibility and, indeed, its ultimate functionality, truly understanding pivot tables would be difficult without understanding their basic structure. A pivot table is composed of four areas. The data you place in these areas defines both the utility and appearance of the pivot table. Keeping in mind that you will go through the process of creating a pivot table in the next section, lets prepare by taking a closer look at the four areas and the functionality around them. Values Area The values area is shown in Figure. It is a large rectangular area below and to the right of the headings. In this example, the values area contains a sum of the revenue field. 1

Values area The values area is the area that calculates. This area is required to have at least one field and one calculation on that field in it. The data fields that you drop here are those that you want to measure or calculate. The values area might include Sum of Revenue, Count of Units, and Average of Price. It is also possible to have the same field dropped in the values area twice, but with different calculations. A marketing manager might want to see Minimum of Price, Average Price, and Maximum of Price.

Row Area The row area is shown in Figure. It is composed of the headings that go down the left side of the pivot table.

Dropping a field into the row area displays the unique values from that field down the rows of the left side of the pivot table. The row area typically has at least one field, although it is possible to have no fields. The example earlier in the chapter where you needed to produce a one-line report of credits is an example where there are no row fields. The types of data fields that you would drop here include those that you want to group and categorizefor example, Products, Names, and Locations. Column Area The column area is composed of headings that stretch across the top of columns in the pivot table. In the pivot table in Figure, the month field is in the column area. Dropping fields into the column area would display your items in column-oriented perspective. The column area is ideal to show trending over time. The types of data fields that you would drop here include those you want to trend or show side by sidefor example, Months, Periods, and Years. Report Filter Area The report filter area is an optional set of one or more drop-downs at the top of the pivot table. In Figure, the report filter area contains the Region field, and the pivot table is set to show all regions. Dropping fields into the report filter area would allow you to filter the data items in your fields. The report filter area is optional and comes in handy when you need to filter your results dynamically. The types of data fields that you would drop here include those that you want to isolate and focus onfor example, Regions, Line of Business, and Employees.

Exercise 1: Create a PivotTable by using data from a table, add fields to the PivotTable, and then pivot the PivotTable. Files required : Creating workbook 1. Click any cell in the Excel table. 2. On the Insert tab, in the Tables group, click Recommended PivotTables to open the Recommended PivotTables dialog box. 3. Click the second icon, labeled Sum of Volume by Weekday. 4. Click OK to create the PivotTable.

5. Click the Sheet1 worksheets sheet tab to display that worksheet. 6. If necessary, click any cell in the Excel table. 7. On the Insert tab, in the Tables group, click the PivotTable button to open the PivotTable dialog box. 8. Verify that the DailyVolumes table name appears in the Table/Range field and that the New Worksheet option is selected. 9. Click OK to create a PivotTable on a new worksheet. 10. In the PivotTable Fields pane, drag the Center field header to the Rows area. Excel adds the Center field values to the PivotTable row area.

11. In the PivotTable Fields pane, drag the Year field header to the Columns area. Excel adds the Year field values to the PivotTable column area. 12. In the PivotTable Fields pane, drag the Volume field header to the Values area. Excel fills in the body of the PivotTable with the Volume field values. 13. In the PivotTable Fields pane, in the Columns area, drag the Year field header to the Rows area, and drop it beneath the Center field header. Excel changes the PivotTable to reflect the new organization.

Exercise 2: Create a pivot table by focusing on the data displayed in a PivotTable by creating a filter, by filtering a PivotTable based on the contents of a field in the Filters area, by showing and hiding levels of detail within the body of the PivotTable, and by using the Search box.

File required : Focusing workbook 1. On the Sheet2 worksheet, click any cell in the PivotTable. 2. In the Choose fields to add to report area of the PivotTable Fields pane, click the Center field header, click the Center field filter arrow, and then clear the (Select All) check box. Excel clears all the check boxes in the filter menu. 3. Select the Northwest check box, and then click OK. Excel filters the PivotTable.

4. On the Quick Access Toolbar, click the Undo button to remove the filter. 5. In the PivotTable Fields pane, drag the Weekday field header from the Choose fields to add to report area to the Filters area in the Drag fields between areas below area. 6. In the PivotTable Fields pane, click the Close button to close it.

7. In the body of the worksheet, click the Weekday filter arrow, and then, if necessary, select the Select Multiple Items check box. Excel adds check boxes beside the items in the Weekday field filter list. 8. Clear the All check box to clear every check box in the list. 9. Select the Tuesday and Thursday check boxes, and then click OK. Excel filters the PivotTable, summarizing only those values from Tuesdays and Thursdays. 10. In cell A5, click the Hide Detail button. Excel collapses rows that contain data from the year 2012, leaving only the subtotal row that summarizes that years data.

11. In cell A5, click the Show Detail button to redisplay the collapsed rows. 12. If the PivotTable Fields pane isnt displayed, click the Analyze tool tab, click Show, and then click Field List. 13. In the PivotTable Fields pane, click the Month field header arrow to open the filter menu. 14. In the Search box, enter Ju. Excel displays the months June and July in the filter list.

15. Click OK to apply the filter. 16. On the Analyze tool tab, click the Actions button, click Clear, and then click Clear Filters to remove all filters from the PivotTable. Exercise 3: Rename a PivotTable, specify whether subtotal and grand total rows will appear, change the PivotTable summary function, display each cells contribution to its rows total, and create a formula that incorporates a value in a PivotTable cell. Files required: Editing workbook

1. On the PivotTable worksheet, click any cell in the PivotTable. 2. On the Analyze tool tab, click the PivotTable button and then, in the PivotTable Name field, enter VolumeSummary and press Enter to rename the PivotTable. 3. On the Design tool tab, in the Layout group, click Subtotals, and then click Do Not Show Subtotals. Excel removes the subtotal rows from the PivotTable. 4. On the Design tool tab, in the Layout group, click Grand Totals, and then click On for columns only. Excel removes the cells that calculate each rows grand total.

5. On the Quick Access Toolbar, click the Undo button to reverse the last change. 6. Right-click any data cell in the PivotTable, point to Summarize Values By, and then click Average to change the Value field summary operation.

7. On the Quick Access Toolbar, click the Undo button to reverse the last change. 8. Right-click any data cell in the PivotTable, and then click Value Field Settings to open the Value Field Settings dialog box. 9. Click the Show Values As tab to display the Show Values As page. 10. In the Show Values As list, click % of Row Total. 11. Click OK to change how Excel calculates the values in the PivotTable.

12. On the Quick Access Toolbar, click the Undo button to reverse the last change. 13. On the Design tab, in the Layout group, click Subtotals, and then click Show All Subtotals at Bottom of Group to display subtotals in the workbook. 14. Click the Package Summary sheet tab to display the Package Summary worksheet. 15. In cell C4, enter =, but do not press Enter. 16. Click the PivotTable sheet tab to display that worksheet. 17. Click cell K32, and then press Enter. When you do, Excel creates the formula =GETPIVOTDATA(Volume,Sheet2!$A$3,Year,2013) in cell C4.

Exercise 4: Apply a number format to a PivotTable values field, apply a PivotTable style, create your own PivotTable style, apply banded rows to your PivotTable, and apply a conditional format to a PivotTable.

File required: Formatting workbook 1. On the Sheet2 worksheet, right-click any data cell, and then click Number Format to open the Format Cells dialog box. 2. In the Category list, click Number to display the Number page of the dialog box.

3. In the Decimal places field, enter 0. 4. Select the Use 1000 Separator (,) check box. 5. Click OK to reformat your PivotTable data.

6. If necessary, on the Design tool tab, in the PivotTable Style Options group, select the Banded Rows check box. 7. On the Design tool tab, in the PivotTable Styles group, click the More button. Then, in the top row of the gallery, click the third style from the left (Pivot Style Light 2.). Clicking the style causes Excel to apply it to your PivotTable.

8. In the lower-right corner of the PivotTable Styles menu, click the More button to display the gallery. 9. Click New PivotTable Style to open the New PivotTable Style dialog box.

10. In the Name field, enter Custom Style 1. 11. In the Table Element list, click Header Row, and then click Format to open the Format Cells dialog box. 12. On the Font page, in the Color list, click the white swatch. 13. On the Border page, in the Presets area, click Outline. 14. On the Fill page, in the Background Color area, click the purple swatch in the lowerright corner of the color palette. 15. Click OK to close the Format Cells dialog box. The style change appears in the Preview pane of the New PivotTable Quick Style dialog box. 16. In the Table Element list, click Second Row Stripe, and then click Format to open the Format Cells dialog box. 17. On the Fill page, in the middle part of the Background Color area, click the eighth swatch in the second row (its a light, dusty purple). 18. Click OK twice to close the Format Cells dialog box. Your format appears in the PivotTable Styles group.

19. Click the More button in the lower-right corner of the PivotTable Styles group, and then click your new style to reformat the PivotTable. 20. On the Design tool tab, in the PivotTable Style Options group, clear the Banded Rows check box. Excel removes the banding from your PivotTable and from the preview of the custom style.

21. Select the cell ranges K6:K17 and K19:K30.

22. On the Home tab, in the Styles group, click Conditional Formatting, point to Color Scales, and in the top row, click the second three-color scale from the left to apply the conditional format to the selected cells.

14.5 Creating dynamic charts by using PivotCharts

Exercise 5: create a PivotTable and associated PivotChart, change the underlying data and update the PivotChart to reflect that change, change the PivotCharts type, and then filter a PivotTable and PivotChart.

Files required: RevenueAnalysis workbook 1. On the Through 2012 worksheet, click any cell in the Excel table. 2. On the Insert tab, in the Charts group, click the PivotChart button to open the Create PivotChart dialog box.

3. Verify that the QuarterlyRevenue table appears in the Table/Range field and that New Worksheet is selected. 4. Click OK to create the PivotChart and associated PivotTable. 5. In the PivotChart Fields pane, drag the Center field header from the Choose fields to add to report area to the Legend (Series) area. 6. Drag the Year field header from the Choose fields to add to report area to the Axis (Category) area.

7. Drag the Quarter field header from the Choose fields to add to report area to the Axis (Category) area, positioning it below the Year field header. 8. Drag the Revenue field header from the Choose fields to add to report area to the Values area. Excel updates the PivotChart to reflect the field placements.

9. Click the 2013 sheet tab to display that worksheet. 10. Select the data in cells B2:E10, and then press Ctrl+C. Excel copies the data to the Microsoft Office Clipboard. 11. On the tab bar, click the Through 2012 sheet tab to display that worksheet. 12. Select cell B147, and then press Ctrl+V to paste the data into the worksheet and include it in the Excel table. 13. Click the tab of the worksheet that contains the PivotTable and the PivotChart. The PivotChart appears. 14. Select the PivotChart and then, on the Analyze tool tab, in the Data group, click Refresh to add the data to your PivotChart.

15. On the Design tool tab, in the Type group, click Change Chart Type to open the Change Chart Type dialog box. 16. Click Line, click the first Line chart subtype, and then click OK to change your PivotChart to a line chart. 17. In the PivotTable Fields pane, in the Choose fields to add to report area, point to the Center field header. Click the filter arrow that appears and then, in the filter menu, clear the Select All check box to remove the check boxes from the filter list items. 18. Select the Northeast check box, and then click OK to filter the PivotChart.

Exercise 1. I work for a small travel agency for which I need to mass-mail a travel brochure. My funds are limited, so I want to mail the brochure to people who spend the most money on travel. From information in a random sample of 925 people, I know the gender, the age, and the amount these people spent on travel last year. How can I use this data to determine how gender and age influence a persons travel expenditures? What can I conclude about the type of person to whom I should mail the brochure? 2. Im doing market research about Volvo Cross Country Wagons. I need to determine what factors influence the likelihood that a family will purchase a station wagon. From information in a large sample of families, I know the family size (large or small) and the family income (high or low). How can I determine how family size and income influence the likelihood that a family will purchase a station wagon? 3. I work for a manufacturer that sells microchips globally. Im given monthly actual and predicted sales for Canada, France, and the United States for Chip 1, Chip 2, and Chip 3. Im also given the variance, or difference, between actual and budgeted revenues. For each month and each combination of country and product, Id like to display the following data: actual revenue, budgeted revenue, actual variance, actual revenue as a percentage of annual revenue, and variance as a percentage of budgeted revenue. How can I display this information? 4. I often have to use specific data in a PivotTable to determine profit, such as the April sales in France of Chip 1. Unfortunately, this data moves around when new fields are added to my PivotTable. Does Excel have a function that enables me to always extract Aprils Chip 1 sales in France from the PivotTable? 5. Contoso, Ltd. produces microchips. Five types of defects (labeled 15) have been known to occur. Chips are manufactured by two operators (A and B) using four machines (14). You are given data about a sample of defective chips, including the type of defect, the operator, machine number, and day of the week the defect occurred. Use this data to chart a course of action that would lead, as quickly as possible, to improved product quality. You should use a PivotTable to stratify the defects with respect to type of defect, day of the week, machine used, and operator working. You might even want to break down the data by machine, operator, and so on. Assume that each operator and machine made an equal number of products. Youll find this data in the file Contoso.xlsx.

6. You own a fast food restaurant and have done some market research in an attempt to better understand your customers. For a random sample of customers, you are given the income, gender, and number of days per week that residents go out for fast food. Use this information to determine how gender and income influence the frequency with which a person goes out to eat fast food. The data is in the file Macdonalds.xlsx. 7. Students at the School of Fine Art apply to study either English or Science. You have been assigned to determine whether the School of Fine Art discriminates against women in admitting students to the school of their choice. You are given the following data on the School of Fine Arts students: i. Female or male ii. Major applied for: English (Eng) or Science (Sci) iii. Admit? Yes or No iv. Assuming that women are as equally qualified for each major as men, does this data indicate that the college discriminates against women? Be sure you use all available information. The data is in the file Finearts.xlsx. 8. You have been assigned to evaluate the quality of care given to heart attack patients at Emergency Room (ER) and Chicago Hope (CH). For the last month you are given the following patient data: i. Hospital (ER or CH). ii. Risk category (high or low). High-risk people are less likely to survive than low-risk people. iii. Patient outcome (live or die). iv. Use this data to determine which hospital is doing a better job of caring for heart attack patients. Hint: Use all the data. The data is in the file Hospital.xlsx. 9. You are given the monthly level of the Dow Jones Index for the years 1947 to1992. Does this data indicate any unusual seasonal patterns in stock returns? Hint: You can extract the month (January, February, and so on) by using the formula TEXT(A4,mmm) copied to any column. The data is in the file Dow.xlsx. 10. The file Makeupdb.xlsx contains information about the sales of makeup products. For each transaction, you are given the following information: i. Name of salesperson ii. Date of sale iii. Product sold iv. Units sold v. Transaction revenue vi. Create a PivotTable to compile the following information: vii. The number of sales transactions for each salesperson. viii. For each salesperson, the total revenue by product. ix. Using your answer to the previous question, create a function that always yields Jens lipstick sales.

x. Total revenue generated by each salesperson broken down by location. xi. Total revenue by salesperson and year. (Hint: You need to group the data by year.) 11. For the years 19851992, you are given monthly interest rates on bonds that pay money one year after the day theyre bought. Its often suggested that interest rates are more volatiletend to change morewhen interest rates are high. Does the data in the file Intratevol-volatility.xlsx support this statement? Hint: PivotTables can display standard deviations. 12. For the grocery example, prepare a chart that summarizes the trend over time of the sales at each store. 13. For the grocery example, create a calculate field that computes an average per unit price received for each product. 14. For the grocery example, create a PivotChart that summarizes the sales of each product at each store for the years 2005 and 2006. 15. For the data in the file Calcitemdata.xlsx, create calculated fields that summarize sales of dessert (cakes+puddings) and fruits (apples+grapes). 16. In the chip PivotTable example, create a PivotTable that summarizes monthly sales of Chips 1 and 3 in France and the U.S. 17. In the customer PivotTable example, show the top 15 customers in one table and the bottom 5 customers in another table. 18. The file Ptablepartsdata.xlsx contains sales of various parts. Each part code begins with either Part (for computer part) or Comp (for computer). Create a PivotTable that shows only sales of Parts. (Hint: Use a labels filter.) 19. For the data in Problem 14, summarize the total sales of parts and computers. 20. The file Cigarettedata.xlsx contains the age of a sample of Americans, whether they smoke cigarettes or cigars, and whether they died during the current year. What can you conclude from this data? 21. The file Collegedata.xls tells you the following information about students who applied to graduate school at Kelley University: gender, desired major, whether accepted or rejected. If you construct the appropriate PivotTable, you will find fewer women are accepted than men. Do you think Kelley discriminates against women?

14.6 Subtotals
PivotTables can be used to slice and dice data in Microsoft Excel. Often, however, youd like an easier way to summarize a list or a database within a list. In a sales database, for example, you might want to create a summary of sales revenue by region, a summary of sales revenue by product, and a summary of sales revenue by salesperson. If you sort a list by the column in which specific data is listed, the Subtotal command allows you to create a subtotal in a list on the basis of the values in the column. For example, if you sort the makeup database by location, you can calculate total revenue and units sold for each region and place the totals just below the last row for that region. As another example, after sorting the database by product, you can use

the Subtotal command to calculate total revenue and units sold for each product and display the totals below the row in which the product changes. In the next section, well look at some specific examples. Exercise: Refer to the file Makeupsubtotals.xlsx. Use the Subtotal command for the following computations: 1. 2. 3. 4. 5. Find the units sold and revenue for each salesperson. Find the number of sales transactions for each product. Find the largest transaction (in terms of revenue) for each product. Find the average dollar amount per transaction by region. Display a breakdown of units sold and revenue for each salesperson that shows the results for each product by region.