Excel 2010: File
Management—Pivot Tables

Updated 2012.02.16

- 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!

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

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

- Use the instructions above to create an empty
pivot table.
- In the
**PivotTable Field 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 Field 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 either (1)
following the instructions above, or (2) re-using the previous 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.

- Continue to use the same pivot table as we used in the previous example.
- In
the
**PivotTable Field List,**click on the**Product**check box. Excel will create a list of products in the pivot table area. - In
the
**PivotTable Field 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 Field 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 **Row Labels** area to the **Column Labels**

Excel will reformat the pivot table to look like
this:

- Create a new pivot table or clear the existing
one.
- In the
**PivotTable Field List,**click on the**Product**check box, the**Month**check box, and the**Revenue**check box. Excel will create the following pivot table:

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

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

- Create an empty pivot table using the
instructions at the top of this page.
- In the
**PivotTable Field 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. - Excel can display more than just totals. It
can display counts, averages, etc. The next example shows how to get a
count.
- From the "Pivot Table Field" dialog
box, select the desired calculation and then click on "OK".

- To tell Excel which value to calculate,
right-click on the
**Sum of Current Salary**cell (B3 in this case). From the popup menu, click on**Value Field Settings…**

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

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

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:

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

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

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

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