Excel 2010: File Management—Pivot Tables

Updated 2012.02.16

Objectives:

Pivot Tables

Pivot tables are awesome! A pivot table extends the capability of individual database functions by presenting the data in summary form. It divides the records in a list into categories and computes summary statistics for those categories. And, it does all of it with just a few clicks of the mouse!

Coffee Sales Pivot Tables

The first group of examples on this page use the file Volcano Coffee Company Sales.txt. Read it into the browser, select all of the file (ctrl+a), copy it to the clipboard (ctrl+c), then open Excel and do a "Paste Special" (click on the Home tab, in the Clipboard group, click on the down-arrow on the Paste button, and click on Paste Special, then click on Text).

To create a pivot table:

·         Place the cursor somewhere in the data you want to analyze. Note that the columns must have a header row with column names in it.

·         From the Insert tab, click on the Pivot Table button.

·         The following dialog box will appear:

·         In the top section, click on the Select a table or range button. If you placed the cursor in the data before you began creating the pivot table, Excel will already have the range selected for you. If not, drag the mouse over your data, including the column headings.

·         In the bottom section, choose either New Worksheet or Existing Worksheet. In all of my examples, I will choose New Worksheet.

·         Click on OK.

·         Excel will create a new worksheet with an empty pivot table on it:

Example 1. Determine total revenue for each product:

 

Example 2. Determine the total revenue by month:

Example 3. Display revenue by product and month:

 

Excel will summarize the data in a list format (above). However, we can also get Excel to display the data in a table format. At bottom of the PivotTable Field List, drag the Month label from the Row Labels area to the Column Labels

 

Excel will reformat the pivot table to look like this:

Example 4. To sort the data:

·         Click on any of the non-bolded numbers in the Sum of Revenue column.

·         On the Options tab, in the Sort group, click on the Z-to-A button. Excel will sort the data within each product in descending order using the Revenue field as its sort key.

 

·         Now, click on any one of the bolded numbers in the Sum of Revenue column. These are the totals for each product.

·         On the Options tab, in the Sort group, click on the Z-to-A button again. Excel will sort the data by product total in descending order using the Revenue total field as its sort key.

Example 5. Displaying the “Top 10”

·         Continue to use the pivot table from Example 4.

·         The data is now sorted, but we want to see only the top three months for each product. Right-click on any month name in column A.

·         From the popup menu, click on Filter.

·         From the sub-menu, click on Top 10…

 

·         The Top 10 Filter (Month) dialog box will appear. In the second text box, change the 10 to a 3.

·         Click on OK.

·         Excel will filter your data so that it only displays the top three Revenue values for each month.

 

Employee Data Pivot Tables

The next few examples use the file Employee List.txt. Read it into the browser, save it on your computer, start Excel, and open (import) the file using the file import wizard.

Example 1. Display the total payroll by department:

Example 2. Display counts instead of sums.

·         Excel will display the Value Field Settings dialog box:

·         In the Summarize value field by list box, click on Count.

·         Click on the OK button.

·         Excel will change the totals to counts. The resulting pivot table will look like this:

Example 3. Display counts by department and job code.

·         This continues using the pivot table from the previous example.

·         In the PivotTable Field List, click on the Job Code check box.

·         Excel will modify the pivot table to look like this:

·         It sums the Job Code values because Job Code is a numeric field. However, we do not want it summed; we want it at the top of the columns so we can see how many employees are in each Department with each Job Code. To fix this look in the PivotTable Field List, and drag Sum of Job Code from the Values box to the Column Labels list box.

·         Your pivot table will now look like this:

·         This displays the correct data, but it is not very easy to read because the field names (Dept and Job Class) are not displayed. To get Excel to display the field names, from the Design tab, click on the Report Layout button.

·         From the sub-menu, click on Show in Tabular Form.

·         The pivot table now looks like this, which is much easier to read:

Example 4. Determine a count of the number of employees in each Department or in each Job Code category.

It is possible to have a field as both a label (Row Label or Column Label) and a Value. To count the number of employees in each department:

·         Click on the Dept field to make it appear in the Row Labels box at the bottom of the spreadsheet.

·         Then drag the Dept field to the Values box at the bottom of the spreadsheet:

This will create the following pivot table:

 

If we try to do the same thing with the Job Code field, it will try to sum the Job Codes.

·         Click on the Job Codes check box. The Job Code field will move to the Values box at the bottom of the worksheet, and Excel will sum the Job Codes:

We don’t want to sum them, we want to count them.

·         Click on the Sum of Job Codes button in the Values box.

·         From the popup menu, click on Value Field Settings.

·         In the dialog box, click on Count.

·         Then click on OK.

·         This only gives us a count of the number of job codes in the table. If we want the number of employees in each job code category, we need to also put the Job Code field in the Column Labels box. So drag the Job Code field from the Pivot Table Field List to the Column Labels box.

·         This will give us the correct pivot table:

Example 5. Determine salary averages by department and job code.

·         This continues using the pivot table from the previous example.

·         Right-click on any one of the values in the values area (center) of the pivot table. From the popup menu, click on Value Field Settings

·        

·         The Value Field Settings dialog box will appear. In the Summarize value field by area, click on Average.

·         Then click on OK.

·         Your pivot table will now look like this:

·         Note that the data doesn’t look very good because some of the numbers have 0 decimal places while others have 5 decimal places. This can be easily fixed by applying a formatting command. Select the numbers in the data area (but not the 1, 2, and 3 column headers in row 4).

·         From the Home tab, in the Number group, click on the comma button:

·         Your pivot table will now look like this:

Example 6. Display salary counts by intervals.

·         Create a new pivot table.

·         In the PivotTable Field List, click on the Dept check box.

·         In the PivotTable Field List, click on the Current Salary check box.

·         In the PivotTable Field List, click on Current Salary (not the check box, but the text) and drag it down to the Row Labels box at the bottom.

·         Your spreadsheet will look like this:

·         Right click on any number in the Current Salary column (column B, not column C). From the pop-up menu, click on Group…

·         The Grouping dialog box will appear:

·         Change the Starting at value to 20,000 and the Ending at value to 50,000.

·         Change the By value to 10,000.

·         Click on OK.

·         Your pivot table will look like this:

Example 7: Pivot Charts

A pivot chart is a chart that is based on a pivot table.

Creating a pivot chart

·         Create the pivot table that you want to base your chart on. Make sure the cursor is in the pivot table somewhere.

·         Click on the Insert tab.

·         In the Charts group, click on the chart type and then click on the chart sub-type. In this example, I chose a chart type of Column and a chart sub-type of 2D Column/Clustered Column. Excel will produce the following chart:

·         This chart can be formatted the same as any other Excel chart.

Changing the type of chart

·         To change the type of chart: Right-click anywhere on the chart and choose Chart Type.

·         Choose the type of chart you want. Note that if you choose a 3D type of chart, Excel will rotate the chart (and it usually doesn't look too good). To change the rotation, right-click anywhere on the chart and choose "3-D View". It usually looks best with a "Rotation" of 10 degrees and the "Right Angle Axes" option turned on.