How to use a Pivot Table in Excel

One of the best kept secrets in database analysis is the Pivot Table tool in Excel. Using this tool, it is extremely easy to quickly group items and perform various summary calculations. Excel’s Pivot Table have allowed college graduate students to easily perform complex data-mining work and have allowed businesses to analyze data without the need for complex data querying software.

Difficulty: Moderate

Instructions

Things You'll Need:

• Excel
• Data

1. 
   Figure 1

   Almost any data set can be used such as that shown in Figure 1. This figure shows example data of product records from a fictitious department store. The data must be contiguous (i.e., no missing records). To initiate the Pivot Table, selected “PivotTable” from the menu as shown in Excel 2007. Other versions may have menus arranged differently.

2. 
   Figure 2

   A new sheet will be created that shows the Pivot Table template on the left and a field list on the right as shown in Figure 2. The template allows different column names like “Product Category” to be dropped into the various sections shown (e.g., “Drop Column Fields Here”).

3. 
   Figure 3

   By checking off the Product Category and Location fields, they are used as grouping elements on the template as shown in Figure 3. Rather than checking these off, they could also have been drag-dropped onto the template. Then dragging the Product Category into either the large “Drop Data Items Here” section on the template or into the values box on the right will generate the number of items categorized by Product Category and Location. For example, there are 4 Books in Aisle A. This is perhaps one of the simplest examples of how a Pivot Table could be used.

4. 
   Figure 4

   Other summary results that could be determined are total and average costs of each product. To do this, the Product Category field is drag-dropped onto the Row Labels box on the right and the Cost field is drag-dropped onto the Values box on the right as shown in Figure 4. This results in a “Sum of Cost” block to appear in the Values box. Click on the drop-down arrow and select “Value Field Settings.” A pop-up box should appear. Select “Average” and press OK. That will provide average costs for each product. For small datasets, one could calculate these manually without Pivot Tables. But imagine having hundreds, thousands, millions (!) of records. Now you can see how useful a Pivot Table could be.