Excel: Pivot Tables

 

Objectives:

 

Using Excel help (F1):

In the Search for box, type Pivot tables

In Search Results: click on About PivotTable reports and read the topic.

In Search Results: click on Create a PivotTable report and read the topic.

In Search Results: click on Ways to customize PivotTable reports and read the topic.

 

Excel Bible: Chapter 21

 

Pivot Tables

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. An Excel workbook that explains pivot tables can be downloaded (1.5 MB). Note: If you are prompted to enter a network password, just click on the “Cancel” button.

 

Using the pivot table wizard:

0. "Data" | "Pivot table"

1. Choose data source, usually "Microsoft Excel List or Database"

2. Choose data range, including headings

3. Drag categories to column or row headers, put the data to be summarized in the "data" part.

4. Click on "Finish"

 

To look at the data in different ways: 

•          Drag column headers to row header area and vice versa.

•          Drag more than one field to the "data" area.

•          Drag more than one field to the column or row header.

•          Change the summary function by clicking on the "Pivot table field" on the pivot table toolbar.

•          Group numbers into ranges: "Data" | "Group and Outline" | "Group"

•          Filter the data by dragging a field to the "Page" field

 

 

Pivot table example:

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

 

Put the cursor in the data range.

Then choose "Data" | "PivotTable and PivotChart Report". This will invoke the Pivot Table Wizard:

 

Step 1:

Where is the data that you want to analyze?

If the data is from Excel, choose the Microsoft Excel list or database option.

Note that you can choose an External data source such as data in a database table (like Access).

 

What kind of report do you want to create?

You can choose either PivotTable or PivotChart. Usually you will choose PivotTable. You can always create a chart from the pivot table data.

 

After selecting your data source and selecting the type of report/chart you want to create, click on the Next button.

This takes you to step 2 of the PivotTable and PivotChart Wizard

 

Step 2:

You can change (or visually verify) the data selection by clicking on the  button.

After you have selected the range that you want to use as the source of your chart, you can just click on the Next button.

This will take you to step 3 of the Wizard.

 

Step 3:

Choose where you want the pivot table to go: either on a new worksheet or on an existing worksheet. Then click on the Finish button. You now have a template for a pivot table set up. All you have to do is tell Excel how to group the data.

 

 

The next few examples continue to use the file Volcano Coffee Company Sales.txt.

 

Example 1. To get total revenue for each product:

Product

 Revenue

 Brazilian Coffee

 $     1,001,740

 French Coffee

 $     1,175,800

 Turkish Coffee

 $        652,500

 Grand Total

 $     2,830,040

 

Example 2. To get total revenue by month:

Month

 Revenue

January

 $   462,280

February

 $   480,760

March

 $   455,340

April

 $   469,400

May

 $   479,260

June

 $   483,000

Grand Total

 $2,830,040

 

Example 3. To get revenue by product and region:

Revenue

 Region

 

 

 

 

 Product

 East

 North

 South

 West

 Grand Total

 Brazilian Coffee

 $255,150

 $242,640

 $255,150

 $248,800

 $     1,001,740

 French Coffee

 $299,700

 $284,500

 $299,700

 $291,900

 $     1,175,800

 Turkish Coffee

 $143,800

 $216,000

 $151,300

 $141,400

 $        652,500

 Grand Total

 $698,650

 $743,140

 $706,150

 $682,100

 $     2,830,040

 

Example 4. To get the top 3 months (by revenue) for each product:

Product

Month

 Revenue

Brazilian Coffee

June

 $   172,200

 

February

 $   171,360

 

May

 $   169,260

Brazilian Coffee Total

 $   512,820

 

 

 

French Coffee

June

 $   202,300

 

February

 $   201,300

 

May

 $   198,800

French Coffee Total

 $   602,400

 

 

 

Turkish Coffee

May

 $   111,200

 

March

 $   109,400

 

April

 $   108,600

Turkish Coffee Total

 $   329,200

 

 

 

Grand Total

 

 $1,444,420

 

 

 

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. To get the total payroll by department:

 

Dept

 Current Salary

Admin

 $       150,750

Mktg

 $       115,250

Prod

 $       131,300

Grand Total

 $       397,300

 

Example 2. This continues using the pivot table from the previous example. To get salary counts (number of employees) by department:

·         From the "Pivot Table" toolbar, choose the "Pivot Table" button, and select "Field Settings".

·         From the "Pivot Table Field" dialog box, in the "Summarize by" list box, select "Count" and then click on "OK".

·         Your pivot table should look like this:

Dept

Count of  Current Salary

Admin

 $                              5

Mktg

 $                              3

Prod

 $                              4

Grand Total

 $                            12

 

Example 3. This continues using the pivot table from the previous example. To get salary counts by department and job code:

·         From the "Pivot Table Field List" drag the "Job Code" icon to the top of the existing pivot table.

·         Your pivot table should look like this:

Count of  Current Salary

Job Code

 

 

 

Dept

1

2

3

Grand Total

Admin

               2

               2

               1

              5

Mktg

               1

               1

               1

              3

Prod

               1

               2

               1

              4