Using Pivot Tables in Excel

Many Excel users have heard of a pivot table but are not sure what this powerful tool does.

Essentially it is a summarising or aggregating tool, able to take reams of tabular data as input, and produce a different table as output, one that shows more clearly the character of the data you started with.

It is rather hard to explain, but much easier to demonstrate, so take a look.

Let's say you run a large company based in several states and want to examine the way in which money is being spent. The data in the following table might be collected to help with this problem:

The following is some sample output from a pivot operation in Excel.

Aggregation by State

Grand Total 314589

To produce this report, do the following:

•  Select the original table, including its headings. If no blanks appear in the table, then it is ok to just put the cursor anywhere in the table. The program will work out the table boundaries automatically.

•  Select ‘Pivot Table and Pivot Chart Report' from the ‘Data' menu option and choose ‘Next' from the ensuing dialog box.

•  Confirm that the address of the table is correct and press ‘Next'

•  From the dialog box that appears, click the ‘Layout' option to see the following:

•  The buttons along the right show the column or field headings in your original table. The white rectangular areas represent positions in the output table to accommodate the fields and quantities. Click and drag a numerical field into the ‘Data' area of the display. In the example above, the ‘Costs' button would be moved. Once you have done this, you will see the ‘Sum of Costs' appear, indicating the nature of the aggregation about to be performed. This can be modified to other aggregating functions.

•  Next drag the ‘State' button to the ‘Row' area of the display. Click OK and then ‘Finish' to see the results shown above.

•  To remove a field, drag it away from the white rectangular areas.

By dragging the ‘Dept' and ‘State' buttons to the ‘Row' and ‘Column' areas respectively, you can produce the following output.

Aggregation by Department and State

To modify the nature of the calculation performed, double-click the ‘Sum of Costs' button now located in the ‘Data' area and select an alternative, such as ‘Average'. Try dragging both City and State fields to the Row area to produce the following report.

Average by City and State

Notice that a pivot table toolbar appears. To make changes to a pivot report, place your cursor anywhere in the report and select the ‘PivotTable' dropdown option from this toolbar and from the list that appears, choose PivotTable Wizard.

In essence, a pivot table needs to be given a quantity (Costs in the above example), one or more criteria or attributes (State, City, Department) and an aggregating operation (sum, average above but Max, min, count and others are available).

The output will then use that operation on the quantity and show the results against the attributes as well as in total. Try other variations to see the full power of the method.

The pivot results above are of a two-dimensional nature. Drag a field name to the ‘Page' area to produce a three dimensional report.

There are many interesting and useful extensions that can be made to this idea. We will explore some of these in future articles.