Computer Training - Using Excel Pivot Tables

How To Easily Summarise Data In Microsoft Office Spreadsheets

© Martin Bell

Jul 17, 2009

Many computer training providers have the facility to deliver Microsoft Excel tutorials. After the basics of how to enter Excel data have been covered, it is easy to move on to methods of summarising the data. One of the most popular ways to summarise data is by using a pivot table, and then graph it using a pivot chart.

What is a Pivot Table?

A pivot table is a summary of a larger set of data or information. It is a look-up table that contains a summary statistic such as COUNT, SUM, AVERAGE, or others. It also usually contains a parameter that the summary is broken down by, such as day of week, gender, product type etc: without this parameter, the pivot table would produce one number as the output – a grand total or grand summary.

Why Use A Pivot Table?

Pivot tables are useful to summarise large amounts of data, to get useful information. For example, if a police database contains the information about all reported crimes, it is possible to use a Microsoft Excel pivot table to see the total crimes (COUNT) committed by county, street, age of victim, or type of crime. It is also possible to extend this to “two dimensions”, to get a summary of crimes committed by gender and county, for example.

How To Create a Pivot Table

This section uses the spreadsheet shown in Figure 1, and contains data showing commission earned by a Financial Advisor who sells Auto Insurance, Endowment Mortgages, Repayment Mortgages, and Health Insurance. The date and day of week are also included.

1. From the main menu, click on “Data” and choose the “PivotTable and PivotChart report”. From the menu that appears, click on “Microsoft Excel list or database”, and “PivotTable”. Then click “Next”. (See Figure 1 – Pivot Table Options).
2. Select the area on the spreadsheet to be summarised. It is essential that every column within this range has a title, even if it will not be used in the pivot table. Then select the “New Worksheet” option, to place the pivot table in a separate sheet. Then click on “Layout”. (See Figure 2 – Select Data and Pivot Table Location.
3. The layout consists of three sections – Data, Row and Column. Choose the data to be summarised ("Commission" in this example), and click-and-drag it to the “Data” section. Then drop it there. To change the summary operation (e.g. from Sum to Average), double-click on the “Sum” box. Then choose the parameter to analyse the data by, and click-and-drag it to the “Row” section. (“Type” in this example). Then click “Ok”, and then “Finish" when the “Layout window appears. (See Figure 3 – Choose Summary Type and Parameters).

The Pivot Table is created as shown in Figure 4.

Summary of Microsoft Excel Pivot Tables

Pivot Tables are a very easy and quick way to produce useful summary data from a larger data set. The summary data produced may then be analysed and used to make decisions, or to help decide on a course of actions. An Excel graph may be produced at the same time as the Pivot Table, and is called a Pivot Chart. This is done by choosing the "Pivot Chart (With Pivot Table)" option in Step 1.

Pivot Tables Give a Quick Summary

Figure 1 - Pivot Table Options

Figure 2 - Select data and Pivot Table Location

Figure 3 - Choose Summary Type and Layout

Figure 4 - Pivot Table Result

Print Article

View All Articles

How to subscribe to feeds