Excel 2019/365: File Management—Pivot Tables

Updated 2019-09-17

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

- 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). It will also add the**Product**field to the*Rows*box at the bottom.

- In the
**PivotTable Fields List**, click on the**Revenue**check box. It will put**Revenue**in the**Values**box at the bottom. Whenever a numeric field is placed in the**Values**box, Excel will add all of the values and put them in column B.

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

Turn off the **Revenue** check
box (to remove **Revenue** from both column B and the **Values** box on
the bottom right). Drag the **Product** field from the field list to the **Values**
box. If a text field (like **Product**) is placed in the **Values** box, since
Excel cannot add text, it will **count** the number of rows containing this
value.

You can count the number of rows
that __any text value__ appears on by dragging it to both the **Rows**
box and the **Values** box.

- 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 put**Month**in the**Rows**box at the bottom, and create a list of months in the pivot table area on the left:

- In the
**PivotTable Field List,**click on the**Revenue**check box. Excel will recognize that this is numeric data and will put it in the**Values**text box and sum it for you in the pivot table area (column B):

- 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, start with an empty pivot table. Then:

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