Excel 2016: File Management—Pivot Tables

Updated 2017.07.30

· Learn how to use Pivot tables to summarize data.

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!

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**).

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

a

- Use the instructions above to
create an empty pivot table.
- In the
**PivotTable Fields List**(on the right), click on the**Product**check box. Excel will create a list of products in the pivot table area (on the left):

- In the
**PivotTable Fields List**, click on the**Revenue**check box. Excel will recognize that this is numeric data and will try to summarize it for you:

** **

- That’s it! You now have a
pivot table.

- Create a new pivot table by turning
off both the
**Product**check box and the**Revenue**check box. - In the
**PivotTable Field List,**click on the**Month**check box. Excel will create a list of months in the pivot table area:

- In the
**PivotTable Field List,**click on the**Revenue**check box. Excel will recognize that this is numeric data and will try to summarize it for you:

- You now have a summary of the
revenue broken down by month.

- Create
a new pivot table by turning off both the
**Month**check box and the**Revenue**check box. - In the
**PivotTable Fields List,**click on the**Product**check box. Excel will create a list of products in the pivot table area. - In the
**PivotTable Fields List,**click on the**Month**check box. Excel will create a list of months for__each__product in the pivot table area. - In the
**PivotTable Fields List,**click on the**Revenue**check box. Excel will recognize this as numeric data and will try to summarize it first by**Product**, then by**Month**within each product:

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.

Excel will reformat the pivot table to look like this:

- Clear the existing pivot table
by clearing all of the check boxes in the
**PivotTable Fields**list. - In the
**PivotTables Field List,**click on the**Product**check box, the**Month**check box, and the**Revenue**check box (in that order). Excel will create the following pivot table. By default, the data is sorted by**Product**(the first field selected):

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

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

· 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…**

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

- Create an empty pivot table
using the instructions at the top of this page.
- In the
**PivotTable Fields List**(at the right), click on the**Department**check box and the**Current Salary**check box. - By default, Excel will display
the
**sum**of the Current Salary for each department. - Excel can display more than
just totals. It can display counts, averages, etc. The next example shows
how to get a count.

- To tell Excel which value to
calculate, click on the
**Sum of Current Salary**(at the bottom of the**PivotTable Fields**list). From the popup menu, click on**Value Field Settings…**

·
Excel will display the **Value
Field Settings** dialog box:

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

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

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

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

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:

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

· This will give us the correct pivot table:

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

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

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

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

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

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

· This chart can be formatted the same as any other Excel 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.