Introduction To Pivot Tables

Pearson Software Consulting, LLC

Many Excel users are not familiar with, or are intimidated by Pivot Tables, one of the most powerful features in Excel. This page describes elementary Pivot Tables. This page was written by Harald Staff, and is included here with his kind permission.

A pivot table is a great reporting tool that sorts and sums independent of the original data layout in the spreadsheet. If you never used one, this demo may be of interest.

Who	Week	What	Amount
Joe	3	Beer	18
Beth	4	Food	17
Janet	5	Beer	14
Joe	3	Food	12
Joe	4	Beer	19
Janet	5	Car	12
Joe	3	Food	19
Beth	4	Beer	15
Janet	5	Beer	19
Joe	3	Car	20
Joe	4	Beer	16
Beth	5	Food	12
Beth	3	Beer	16
Joe	4	Food	17
Joe	5	Beer	14
Janet	3	Car	19
Joe	4	Food	17
Beth	5	Beer	20
Janet	3	Food	18
Joe	4	Beer	14
Joe	5	Food	12
Janet	3	Beer	18
Janet	4	Car	17
Janet	5	Food	12

First, set up a create some data, in A1:D50, like this, with 4 or 5 different names, 4 or 5 different activities and a little variety of week numbers and expenses:

Add as many rows as you can stand -- around 50 will do.

Now choose any cell in this table and choose Pivot Table wizard in the Data menu. Excel asks for the data source and suggests this table. Click OK.

Next question is the data range. Excel suggests the table. If you expect to add data in the future, set the data range to include as many rows as you think you will ever need. Rather than A1:D50, you may want to specify $A$1:$D$500.

Now comes the layout wizard, show below.

piv1.gif (10385 bytes)

Drag the headers Who, Week and What into the ROW area, and the Amount header into the Data area. (Leave the Column area blank for now.) If the Amount tag does not show "Sum of Amount", double-click it and choose the Sum option. Finally Excel asks if the table should be placed in a new worksheet. Click OK.

Now you have your table, and it looks very much like a sorted version of the original data list, except from the automatic subtotals. Now comes the cool stuff:

Grab the What header in the table and drag it all the way to the left. When you drop it here, the table re-sorts and re-sums; you have a table of beer costs by person instead. Now drag the Week header to the left and you have a weekly report.

Double-clicking the headers gives options of showing/hiding specific data (like Empty and Beer, may come in handy) and removing subtotaling for this column. Right-clicking gives other options, among them Hide and Show Detail for reading totals only.

Here comes another useful pivot, made from the same list. Select any item in the original data list and choose Pivot Table wizard again. This time, drag Who into the Row field, What into the Column field and Amount into the Data field.

Voilá. This table is -among other things- very useful for graphing.

The only tricky thing is this: You have to update the table manually from the Data menu. A Pivot table does not update itself. If this becomes boring, here is some macro code that makes the tables update on selecting the worksheet:

Sub Auto_Open()
Application.OnSheetActivate = "UpdateIt"
End Sub

Sub UpdateIt()
Dim iP As Integer
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

Created By Chip Pearson and Pearson Software Consulting, LLC
This Page:                Updated: June 15, 2000
MAIN PAGE    About This Site    Consulting    Downloads
Page Index    Search    Topic Index    What's New    Links

© Copyright 1997-2003 Charles H. Pearson