Using Pivot Tables In Excel

Using Pivot Tables in Excel What is an Excel Pivot Table? • A pivot table is an interactive worksheet table that provides a powerful tool for summarizing large amounts of tabular data. • Similar to a cross-tabulation table, a pivot table classifies numeric data in a list based on other fields in the list. What can you do with Pivot Tables? • You can quickly summarize data from a worksheet or from an external source. • You can calculate totals, averages, counts, etc. based on any numeric fields in your table. • You can generate charts from your pivot tables. What are the advantages of using Pivot Tables? • Pivot tables are interactive, which means you can easily rearrange them by moving, adding, or deleting fields. • Pivot tables are dynamic, which means results are automatically recalculated whenever fields are added or dropped, or whenever categories are hidden or displayed. • Pivot tables are easy to update if the original worksheet data are changed. The best way to learn about Pivot Tables is through hands-on experience!! Part 1: Constructing a Pivot Table 1. Open the Excel file we’re going to use. It’s fake data I took from a Pivot Tables workshop given by Robert Lee of the University of Victoria (Note that much of this presentation steals material from this workshop). Take a few seconds to familiarize yourself with the data in this worksheet. What is each column telling us? 2. Under the “Data” menu, select “Pivot Table and Pivot Chart Report.” This will bring up the Pivot Table Wizard, which consists of three steps. a. Step 1: Since the data resides in a worksheet, simply click “Next.” b. Step 2: Excel will automatically select the data we are currently working with. Click “Next” again. c. Step 3: I usually open the pivot table in a new worksheet (again, this is the default). Note that you can save a few seconds simply by clicking “Finish” on step 1 if you don’t plan to do anything unusual (which, in my experience, is almost every time). 3. We now have a new worksheet with a blank pivot table, and a pivot table box menu thingy consisting of menus, buttons, and a list of all the data fields. Suppose we want to know the total

sales for each employee. Drag the “Employee” field into the “Row” area, then drag the “Order Amount” field into the “Data” area. This results in a table of total sales by employee. Part 2: Modifying a Pivot Table 1. We want to change the format of the amount to dollar format. Right click on any number in the table under “Total” and select “Field Settings.” Click on the “Number” button. Select “Currency” format and click OK. 2. Pretend that we want to know how much each employee sold in different product categories (refer back to the data if you need to remember what these are). Drag over the “Categories” field from the Pivot Table box and put it in the blank space above the “Total” column. This produces a cross-tabulation table of total sales by employee and by category. 3. Suppose we’re more interested in average sales instead of total sales. Right click on any data field cell (i.e. any dollar amount) and select “Field Settings.” Select “Average” and click OK. This produces a cross-tabulation table of average sales by employee and by category. 4. Suppose we want number of sales instead. Right click on any data field cell and select “Field Settings.” Select “Count.” Now, because we don’t want this to be displayed as a dollar amount, click the “Number” button, then select “Number” and reduce the decimal places to 0. Click OK and OK. Now we have a cross-tabulation table of the number of sales by employee and by category. 5. Suppose we aren’t interested in every product category. In this case, we’re not really interested in Beverages and Dairy Products. Click on the down arrow on the pivot table next to “Category.” In the drop down menu, uncheck “Beverages” and “Dairy Products” and click OK. This eliminates these two categories from the table. 6. Suppose we want to know the percentage of sales each employee had in each category. Return the data back to the sums in dollar format: Right click on a cell, select “Field Settings,” select “Sum,” click on the “Number” Box, select “Currency,” select 2 decimal places. Now right click and select “Field Settings” again. Select “Options.” Under “show data as” select “% of Row” and click OK. 7. Suppose we want to know monthly sales by category for each employee. a. First, change the row % back to sums in dollar format (Field settingsàChange % of Row to “Normal”àSelect “Number” then “Currency”). b. Next, drag the “Shipping Date” field from the Pivot Table box into the Row area. Note that you can put it to the left or right of the employee field. We want to put it to the right, because we want to know shipping date by employee, not employee by shipping date. c. The summaries are too detailed! We want to group them at the month level. Right click on a cell in the “Shipping Date” column. Select “Group and Outline,” then select “Group.” Select “Months” and click OK. 2. To format the report in a way that changes it’s appearance, we can use the “Format Report” feature. Right click on a cell in the pivot table and select “Format Report.” Experiment with different styles to see what you like best. To go back to the original look, simply select “Undo” (Control-Z) from the Edit menu at the top of the screen. [Note: if you save the data after you change the format, you can’t undo the format]. 3. Now move the “Employee” field from the pivot table up into the “Page Fields” area. By using the drop-down menu, we can look at each employee separately, or all of them combined. 4. Suppose we want a complicated table with lots of information. We want to know, for each employee, monthly sales within product categories to each country. We want to know the total amount of these sales, the number of sales, and the sum of the freight costs, all displayed at the same time. a. Drag “Shipping Country” into the Row area, to the right of the month.

b. Drag “Order Amount” into the Data area. Right click on a cell giving data for the new Order Amount row, select Field Settings, and change it from Sum to Count. c. Drag “Freight Costs” into the data area. Right click on a cell giving this new data, select Field Settings, Number, and Currency. Part 3: Using the Pivot Chart Feature First, simplify the current pivot table by removing the shipping country field and all data fields except “Sum of Order Amount.” 1. If we want to create a chart for Callahan’s monthly sales by category, select “Callahan” from the Page Fields menu. Then right click on a cell in the Pivot Table and select “Pivot Chart.” A chart is automatically generated. Note that you can view the data for any employee simply by using the drop down menu at the top of the chart, and you can modify the chart by either of the other two variables by using the filter menus on the bottom and side. 2. If you don’t like the type of chart Excel chooses, you can modify it by right clicking anywhere on the chart and selecting “Chart Type.” 3. Note that if you modify the pivot table in any way, the chart is automatically updated. Part 4: Pivot Tables and Datatel When you go back to your office you’ll likely be using a different dataset than the one we used here, and you’ll be trying to do something very different than figuring out sales per employee. The following are answers to anticipated problems that you might face. If run a query that gives me a list of students with ethnicity, major, and class level, how can I turn that into a pivot table? There is no numeric field to use for the data. • One solution is to use Student ID as the data field. If all you are doing is counting students (i.e. number of students in each major by class level), put student ID in the data field and use the field settings menu to change it from “Sum” to “Count.” • Another solution is to create a new column that consists entirely of the number 1. You can then use this column as a data field to count or sum (but be sure this is appropriate to the analysis you are doing). The Pivot Table won’t automatically select all of my data. What’s wrong? • Make sure you don’t have any blank rows or columns in your dataset. I changed the data in my original dataset, but the pivot table stayed the same. How do I update the pivot table? • Simply click on the “Refresh” button (the !) on the pivot table box and your table will reflect any changes in the data. I added new rows of data to my original dataset, but when I refreshed the pivot table, nothing changed! Help! • You need to change the location of the pivot table data. It is likely that the pivot table is still only working with the original lines of data. If your original dataset went from row 1 to row 200, and then you added rows 201-225, the pivot table will still only look at rows 1-200, no matter how many times you push the “Refresh” button. On the drop-down menu in the pivot table box, select the pivot table Wizard. Push the “Back” button until you reach step 2, and change the row number (i.e. change the number “200” to a 225).

The pivot table doesn’t work right when I have multi-valued fields. • Note that if you want to use a pivot table, you will need to be sure that every line of data has every bit of information you will want in the pivot table. For example, sometimes after running a query with multi-valued fields into Excel, you have something like this: ID 54604

Name Ima Student

52902

John Doe

Major Sociology Biology Politics

Graduation Date 05/03

Gender F

05/04

M

Ima Student has two majors and this is reflected in her taking up two lines in the “Major” column. The others are blank because they only have one value. However, if you want to have an accurate pivot table with number of majors by gender and graduation date, you will need to fill in the blank data so it looks like this: ID 54604 54604 52902

Name Ima Student Ima Student John Doe

Major Sociology Biology Politics

Graduation Date 05/03 05/03 05/04

Gender F F M

Note that Datatel will do this for you with the “Repeat Values” command in query builder. In general, it is important to know what you want from a pivot table when you are setting up your data!

 Copyright 2002