Creating a Pivot Table

 

Introduction.  PivotTables provide an interactive way to summarize and present data.  It seems that Excel is the only spreadsheet program that provides this very useful tool.  Using PivotTables gives the user several advatages when they are implemented.
  • Summarize data from several sources
    • Excel spreadsheets
    • multiple spreadsheets
    • external databases
    • other PivotTables
  • Change the calculation functions for a field
  • Show the details underlying a summarized item
  • Pivot the table to change its orientation
  • Use it as the basis for charts that adjust readily to changes in the table.
  • Format the table to improve its readability

First we will take you through the development of a relatively simple PivotTable and then use this to define a fair amount of terminology.

Creating a Pivot Table.  Now you should type the following sales data into a new workbook on the first spreadsheet.  You only need to format the title row and the sales column at present.  Remember you can copy some cells to fill in other cells.

Now you want to invoke the PivotTable Wizard to help you create the initial PivotTable using the following steps.
  1. Highlight cells A1 ... D17.
  2. Select the Data menu.
  3. Click on the PivotTable menu item to start the wizard.  You should see a dialog form like the following.

 

  1. Make sure you select the Microsoft Excel radio button as the location for the data.
  2. At present we are only going to work with PivotTables so select that radio button.
  3. Click on the Next button to get the following form and verify the range.

 

 

  1. Now click on the Next button to get the form for the third step.
  2. Select the New worksheet radio button.

 

  1. Click on the Layout button to get the following form.

 

  1. Now you need to drag the little command button tabs into the appropriate regions so that you get the following image.
    1. Drag the SalesRep button into Row area.
    2. Drag the Month button into the Column area.
    3. Drag the Region button into the Page area.
    4. Drag the Sales button into the Data area, after which its title will be changed to Sum of Sales.

 

 

  1. Now you should click on the OK button
  2. Click on the Finish button to get the following image on what is likely to be sheet4.

 

 

PivotTable Terminology.  The following table contains some jargon that will be defined in terms of the preceding example.

 

Term Definition Illustration
Row Field A field with data displayed in rows. Each SalesRep name is a row field
Column Field A field with data displayed in columns. Each Month is a column field.
Page Field A field that displays one item at a time.  This determines which "page" is displayed in the PivotTable based on the values for the field. Each Region determines a page where one page shows all regions.
Item A specific row or column heading.  
Data Area The part of the PivotTable where data and calculations are displayed. The sales figures and the totals.

 

Some Quick Modifications.  One of the major advantages of a PivotTable is the ability to change the view.  Let's say, for example, that rather than showing total sales figures you want to compute averages.
  1. Select the PivotTable.
  2. Click on the Field Settings button in the PivotTable toolbar.

You should see an image like the following.

 

 

  1. Select the Average option in the Summarize by: listbox.

This will change the nature of the calculations.  It is obviously easy to change them to many other things.  You can easily change the data source range, or the row, column and page fields to quickly get some other informative views.  We will leave this to your own exploration.