Home / Articles

Using Excel PivotTables

One of the most powerful features of Microsoft Excel is the "PivotTable" feature. PivotTable reports allow you to summarize your numerical data and analyze it in depth. These tables are interactive making it easy to quickly change the way the data is presented. For example, you can move (or pivot) the rows to columns and the columns to rows in order to view the data from a different perspective.

PivotTables are best used for querying large amounts of data, subtotaling numerical data, creating calculations, summarizing data by categories and sub-categories, drilling down data, grouping data, and presenting data in easy-to-understand reports.

Microsoft Excel Training goes into great detail on using PivotTable reports. Here's how to create a simple one. First, create a basic list containing data such as the list show below.

 

Next, click the PivotTable icon (found on the Insert tab in Excel 2007). The Create PivotTable dialog box prompts you to create it based on the list found. Click OK to create the PivotTable in a new worksheet.

 

Microsoft product screen shot(s) reprinted with permission from Microsoft Corporation

Next, add the fields you want to summarize. They will be added to the table. For example, you can look at the total value of products sold:

 

By rearranging, or "pivoting" the rows and columns, you can look at the data differently such as by sales person.

 

You can take it further and show the products sold by each sales person.

 

Simply drag and drop the files within the PivotTable Field list to view the information in different ways. This example was done in Excel 2007.

 

Microsoft product screen shot(s) reprinted with permission from Microsoft Corporation

The process is slightly different when using Microsoft Excel 2003 Training using the version that you have is essential. For example, Microsoft Excel 2007 Training covers how to do the steps using the features and layout of the 2007 version of Excel