Excel 2016: File Management—Pivot Tables

Updated 2017.07.30

Objectives:

·         Learn how to use Pivot tables to summarize data.

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.

·       Click on the Insert tab.

·       Then 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:

ahttps://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image003.jpg

Example 1. Determine total revenue for each product:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image004.jpg

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image005.jpg 

Example 2. Determine the total revenue by month:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image006.jpg

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image007.jpg

Example 3. Display revenue by product and month:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image008.jpg

 

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 Rows area to the Columns area.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image009.jpg     https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image010.jpg

 

Excel will reformat the pivot table to look like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image011.jpg

Example 4. Sort the data by Product Revenue:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image012.jpg

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

·       On the Data 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 Product Total field as its sort key.

Example 4b: Sort by Monthly Revenue

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

·       On the Data 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 Monthly Revenue 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 by Monthly Revenue, 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…

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image014.jpg

 

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

·       Click on OK.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image015.jpg

·       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, copy it to the clipboard, and paste it into Excel using the Paste Special option and clicking on Text.

Example 1. Display the total payroll by department:

Example 2. Display counts instead of sums.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image016.jpg

·       Excel will display the Value Field Settings dialog box:

SNAGHTMLca51640

·       Click on the Summarize Values By tab if necessary.

·       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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image018.jpg

Example 3. Display counts by department and job code.

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

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

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image019.jpg

·       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 Fields List, and drag Sum of Job Code from the Values box to the Columns list box.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image020.jpg

·       Your pivot table will now look like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image021.jpg

·       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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image023.jpg

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 Rows box at the bottom of the spreadsheet.

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image024.jpg

This will create the following pivot table:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image025.jpg

 

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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image026.jpg

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.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image027.jpg

·       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 Columns box. So drag the Job Code field from the Pivot Table Field List to the Columns box.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image028.jpg

·       This will give us the correct pivot table:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image029.jpg

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

·       Clear the existing pivot table.

·       Click on Dept, Job Code, and Current Salary.

·       Drag the Job Code field from the Values box up to the Columns box. Your pivot table should look like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image030.jpg

·       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

·       https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image031.jpg

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

·       Then click on OK.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image032.jpg

·       Your pivot table will now look like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image033.jpg

·       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:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image035.jpg

Example 6. Display salary counts by intervals.

·       Clear the existing pivot table.

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

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

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image036.jpg

·       Your spreadsheet will look like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image037.jpg

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

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image038.jpg

·       The Grouping dialog box will appear:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image039.jpg

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

·       Change the By value to 10,000.

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image040.jpg

·       Click on OK.

·       Your pivot table will look like this:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image041.jpg

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. We will use our existing pivot table. Make sure the cursor is in the pivot table somewhere.

·       On the PivotTable Tools, click on the Analyze tab.

·       In the Tools group, click on the PivotChart button.

·       In the Insert Chart dialog box, click on the chart type (on the left) and then click on the chart sub-type (on the top). In this example, I chose a chart type of Column and a chart sub-type of Clustered Column. Excel will produce the following chart:

https://p3plcpnl0895.prod.phx3.secureserver.net:2078/public_html/Excel/2013/009-Pivot-Tables_files/image043.jpg

·       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 Change 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.