My Kiva

Skydrive




Contact
Ed Ferrero

Excel 2003
Pivot Table Tutorial
Part 1

This tutorial has been designed for beginning to intermediate users of Excel. If you have not used pivot tables before, you are about to discover one of the most powerful data analysis tools of Excel. If you already know how to use pivot tables, this tutorial will introduce you to some of the more advanced techniques for getting the best out of pivot tables.

This tutorial is for use with the Excel workbooks and Access database provided. Click on the links provided throughout the tutorial to download these.

If you have any comments, please e-mail me at Ed Ferrero

Contents

(1) A simple Pivot Table

  1. Using the Wizard to set up a simple table
  2. Adding Row Fields
  3. Once you have a table, pivot it!
  4. Creating another Pivot Table based on an existing Table

(2) A more complex example

  1. Using Page Fields
  2. Hiding data
  3. Grouping data
  4. Field calculations

(3) Pivot Tables based on external data

  1. Setting up
  2. Some cautions

(4) Using Pivot Tables to consolidate data

  1. A forecasting example

(5) Using Calculated Fields

(6) Some Useful VBA Routines for Pivot Tables

  1. Aligning Multiple Data Fields
  2. Formatting a Pivot Table
  3. Toggling Data Fields
  4. Grouping and Ungrouping

1) A simple Pivot Table

a) Using the Wizard to set up a simple table

Say you have a list of data as given in the worksheet EG1.XLS. Open the worksheet now, the data looks like this;

To create a Pivot Table, first highlight any cell in the data, then click on the menu Data - PivotTable and Pivot Chart Report… You should see the following dialog.

Pivot Table Wizard - Step1

The Pivot Table Wizard dialog appears. For now, you want to analyse the data contained in the Excel list, so click the Next button. This sends you to the next dialog, which shows the range containing the data you wish to analyse. Excel automatically selects all the data in a contiguous range about the highlighted cell, that means this step is all done for you so just click Next.

Pivot Table Wizard - Step2

The third dialog asks whether you want to put the pivot table on the existing worksheet ar on a new sheet. Do not click "Finish" yet - select "New Worksheet" then click the Layout button.

Pivot Table Wizard - Step3

The Layout dialog shows the headings across the top of your Excel list as buttons, which you can use in your Pivot Table.

Pivot Table Wizard - Step3

You can click-and-drag the buttons on the right of the form to any of four areas on the Pivot Table. The Page area will be explained later. For now, drag "State" to the Row area, "Month" to the Column area, and "Sales" to the Data area. Your dialog should look like this;

Pivot Table Wizard - Step3 modified

Click Ok, then click Finish. You now have a Pivot Table that summarises your data.

Finished Pivot Table

Note that that was a fast operation. The list you just summarised contains 305 records. This is not a lot of data compared to some Pivot Tables that you will prepare at work. It is enough to show you that Pivot Tables are fast. You are limited only by memory in the number of records that you can summarise in a Pivot Table. They are well suited to analysing large amounts of data.

Pivot tables have been used successfully with data sets containing over 800,000 records. It is recommended that, for large data sets, you use at least a 133MHz Pentium with 32MB of RAM (shows how long ago this tutorial was written - Ed). The amount of physical RAM is more important than processor speed when working with large pivot tables. So, if you use a large pivot table and the system seems slow, try adding more memory.


Continue...