Using Pivot Tables in Excel

 
   
 

This document gives a basic understanding (beginner level) on how to create and use pivot tables in Excel.

This tutorial is based on a spreadsheet containing the following columns and corresponding fictitious data

Country,Salesperson,Order Amount,Order Amount, Order Date,Order id. User can enter own data to better understand the same. The spreadsheet would look as below. This spreadsheet is referred to as the practice session workbook in the tutorial.

 
  Sogeti USA  
     
 

Create a PivotTable report

 
     
 

Exercise 1: Create the report view

 
     
 
  • In the worksheet, select any cell that contains data. For example, click on any cell in the data region. On the Data menu, click PivotTable and PivotChart Report . The wizard appears.
  • In Step 1 of the wizard, make sure that Microsoft Excel list or database is selected as the answer to the first question.
  • Make sure that PivotTable is selected as the answer to the next question.
  • Click Finish.
 
     
 

Note: That's it. Clicking Finish tells the wizard to use its default settings to lay out an area for the PivotTable report. You could spend more time with the wizard by clicking Next instead of Finish , but it's not necessary now.

 
     
 

When you scroll down to read further practice steps, the text in the layout area will disappear. That text will reappear when you click in the worksheet.

 
     
 

A new worksheet, "Sheet1," is inserted into the practice session workbook. The new worksheet contains three items: the PivotTable Field List , the layout area for the report, which contains separate outlined drop areas onto which you'll drag and drop fields, and the PivotTable toolbar. If you can't see the field list, which looks like this:

 
  Sogeti USA  
     
 

click in the outlined area. If you still don't see the field list, click the Show Field List button Button image on the PivotTable toolbar. (The toolbar should appear either floating on the worksheet or docked to one side.)

 
     
  Sogeti USA  
     
 

Note     If the PivotTable toolbar is not visible, on the View menu, point to Toolbars , then click PivotTable .

 
     
 

If you wish, you can drag the field list and the toolbar to different locations as you complete the following exercises.

 
     
 

Exercise 2: Lay out the report

 
     
 

Now you'll lay out the report to find out how much each salesperson has sold. The report layout changes appearance as you drop fields into the layout.

 
     
  1. From the PivotTable Field List,  
     
 

Sogeti USA

 
 

drag the Salesperson field to the drop area labeled Drop Row Fields Here .

 
 

Sogeti USA

 
 

You can drag by selecting either the field name or the button in front of the field name. If you drop a field in the wrong drop area, just drag it to the correct drop area.

 
 

Note     Once field names have been dropped, they remain on the list but change to boldface.

 
 
Sogeti USA
 
     
  2. From the PivotTable Field List , drag the Order Amount field to the drop area labeled Drop Data Items Here.  
     
 

When you drop a field in the drop area for data items, the colored outline disappears and you see the report, which displays a total for each salesperson.

 
 

The first rows of your PivotTable report should look like this:

 
 
Sogeti USA
 
 

To see the report without the empty drop area for page fields at the top of the worksheet:

 
 
Sogeti USA
 
 

Click anywhere outside the layout area. Click in cell C4, for example. The page fields area disappears, and so does the PivotTable Field List.

 
 
Sogeti USA
 
     
 

Exercise 3: Pivot the report

 
     
 

In this exercise you'll swing your data from a row orientation to a column orientation.

 
 

1. Click the Salesperson field heading (in cell A4).

 
 

Sogeti USA

 
 

2. Drag and drop the Salesperson field heading to cell B3, which is the cell just above "Total." When you drag, as long as you've got the gray box cursor and you point that to your destination cell, you're OK.

 
 

Sogeti USA

 
 

The report changes appearance to show the names of the salespeople in column, rather than row, orientation.

 
 

Sogeti USA

 
 

This particular data isn't very easy to read in column format, but column format could be very useful with other data. You need to know how to move a field from one orientation to another.

 
 

Next, move the Salesperson field back to a row orientation

 
 

Here's how     Click the Salesperson field heading, drag it to cell A4, and then drop it.

 
     
     
 

Exercise 4: Create page views

 
     
 

Now you'll try something new: creating page views. In this example, you'll display the order amounts for salespeople, with totals for different countries on different pages. You'll drag and drop a field to the drop area labeled Drop Page Fields Here.

 
  Sogeti USA  
     
 

Drag and drop the Country field from the PivotTable Field List to the Drop Page Fields Here area.

Note: If you can't see the field list, click in the layout area. If you still don't see the field list, click the Show Field List button Button image on the PivotTable toolbar.

 
     
 

Now the report looks like this:

 
  Sogeti USA  
     
 

The name of the visible page appears in the cell beside the Country field. In this example, the visible page has data for all countries in the list.

 
     
 

To view a different page, click the arrow beside the page name, select a page, and click OK.

 
  Sogeti USA  
     
 

You can view three pages. The amount sold per country appears for each salesperson on that country's page, and totals for both countries together appear on the "(All)" page.

 
     
 

Exercise 5: Drag fields off the report

 
     
 

We told you that it's easy to change your mind about which information belongs in a report. Now you'll see how to remove fields. Before you start, notice that the fields now displayed in the report view are shown in bold letters on the PivotTable Field List . This helps you to keep track of which fields you've already dragged and dropped to the report.

 
     
  Sogeti USA  
     
 
  1. Click the Country field heading on the worksheet (cell A1), then drag and drop it anywhere outside the outline. For example, drop it in cell D2.
    Note: A red "X" appears beneath the insertion point after the field is dragged outside the layout area. The X means that the field is removed from the report layout.

  2. Click the Sum of Order Amount field heading on the worksheet (cell A3), then drag and drop it anywhere outside the outline.

  3. Click the Salesperson field heading on the worksheet (cell A4), then drag and drop it anywhere outside the outline.

 
     
 

The outline looks as it did before you started. None of the names on the field list is in bold letters, because none of the fields in the list is used on the PivotTable layout area.

 
     
 

So if you feel like starting all over again, it's just this simple. You can have a clean slate in a couple of seconds.

 
     
   
     
     
2009 Sogeti USA LLC. All rights reserved. HelpDesk
 
This site is best viewed in Microsoft Internet Explorer 6.0+ in 1024x768