Excel Pivot Tables

Let’s imagine that you’ve compiled a large list of data – for example, sales information for certain products that stores carry. Currently, the list is difficult to summarize, extract data from, or make any type of comparisons between. For example, you might want to answer the following questions from the data:

  What are the total number of units sold for each product?   Which products are generating the highest income?   What are the total number of units and income per year?

For these types of questions, you can create a PivotTable report. A PivotTable report is an interactive table that automatically extracts, organizes, and summarizes your data. You can then use the report to analyze the data to perhaps compare, detect patterns, or compute sums, averages, and counts.

For example you can use these data:

…to create a PivotTable report that shows the total units and income for each year:

Before you begin your first PivotTable report, it’s a good idea to make sure your data is well organized and ready to go. Neatness counts! For example you’ll want to be sure that the first row of the list contains column labels, since Excel will use these labels for field names in the report. If you haven't realized it yet, you are truly working with a "database" of information. A database is a collection of information that's related to a particular subject or purpose, such as tracking students or maintaining a music collection. Microsoft Access is used for working with large databases, but for smaller lists, Excel's sorting, filtering, and PivotTable functionality usually meets the needs of the user.

To create a PivotTable report, you first use the PivotTable and PivotChart Wizard to specify the data you want to use and to create the report framework. You can then use the PivotTable Toolbar to arrange the data within that framework.

PivotTable Toolbar

For example, you can choose categories of data to include (shaded in yellow) and the corresponding data values (shaded in green) that you would like to summarize or compare. In the example below, I am using the year and month as categories to summarize the total number of units.

After arranging the categories into rows and columns, Excel creates the finished report and automatically summarizes and totals data values.

Follow these steps to create this PivotTable:

Let's get started! From the main menu, select Data, and click on PivotTable and PivotChart Report.

In Step1 of the PivotTable Wizard, you will probably accept the default, 'Excel list or database' but keep in mind that you can use external sources like a database such as PeopleSoft, a text file, or even data from an Internet site.



In Step 2 of the Wizard, Excel will attempt to locate the list you want to Pivot. If necessary, you can either type or select a different range.



NOTE: When you base a PivotTable on an Excel list, be sure to click a cell in that list before you begin. If your cell pointer is not “inside” the list, Excel won’t know what data to pivot, and the window below will appear. At that point you could select the appropriate range, but it is much simpler to just place your cursor within the list in the first place.



In Step 3 of the Wizard, you can choose to create the PivotTable in a new worksheet or the current worksheet. Next, click on Layout…



From the PivotTable Field List located along the right side, drag field buttons to the labeled areas on the PivotTable diagram. If you want to rearrange the fields, drag them from one area of the diagram to another. If you want to remove a field, simply drag the field outside the PivotTable report.



Click OK to complete the process. The resulting PivotTable is shown below.

There are many ways to further customize a pivot table with calculations and groupings. For more information about PivotTable reports, use Microsoft Excel's Help menu.