Microsoft Office/Create a Pivot Table

A pivot table is a great reporting tool that sorts and sums independent of the original data layout in the spreadsheet. It is an interactive representation of a data table. One can rearrange the data and choose what to display and what to hide. The best way to find out what a pivot table can do is to create one.

First, set up categories and create some data (See Table 1):

Table 1: Original Data

Who	Week	What	Amount Spent
Joe	3	Activities	$18
Beth	4	Food	$17
Janet	5	Activities	$14
Joe	3	Food	$12
Joe	4	Activities	$19
Janet	5	Car	$12

Now choose any cell in this table and choose Data > Pivot Table wizard. Pivot Table should be selected. Click Next. Excel asks for the data source and suggests this table. Click Next. Finally Excel asks if the table should be placed in a new worksheet. Select new sheet. Click Finish.

Arranging data on the table

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.) To change the placement of an item, drag the header title to the desired area. If the Amount tag does not show "Sum of Amount", double-click it and choose the Sum option. Double-clicking the headers gives options of showing/hiding specific data (like Empty and Activities, 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. This table is also very useful for graphing.

Pivot tables are very flexible. The one being built here has four variables: Who, Week, What, and Amount, all of which can be dropped in header or data areas represented by the blue borders.