Windows DevCenter    
 Published on Windows DevCenter (http://www.windowsdevcenter.com/)
 See this if you're having trouble printing code examples


What Is a Pivot Table

by Matthew MacDonald, author of Excel: The Missing Manual
08/12/2005
Pivot Table
The Pivot Tables tool is one of the most powerful yet intimidating features in Excel. Pivot tables allow you to quickly summarize and analyze large amounts of data in lists and tables--independent of the original data layout in your spreadsheet--by dragging and dropping columns to different rows, columns, or summary positions.

This article discusses the following:

  1. Summary Tables Revisited

  2. Building Pivot Tables

Creating neat, informative summaries out of huge lists of raw data is a common challenge. And while Excel gives you all the tools you need to create such summaries, the actual work of writing formulas, cutting and pasting information, and organizing your totals into a new table can be extremely tedious. Even worse, this approach isn't very flexible. For example, once you've created the perfect summary that compares, say, sales in different regions, if you want to compare sales across different product lines or different customers, you'll need to start from scratch and build a whole new report.

Fortunately, Excel has a feature called pivot tables that can solve all these problems. Pivot tables quickly summarize long lists of data. By using a pivot table, you can calculate summary information without writing a single formula or copying a single cell. But the most notable feature of pivot tables is that you can arrange them dynamically. For example, say you create a pivot table summary using raw census data. With the drag of a mouse, you can easily rearrange the pivot table so that it summarizes the data based on gender or age groupings or geographic location. This process of rearranging your table is known as pivoting your data: you're turning the same information around to examine it from different angles.

Pivot tables are a hidden gem in Excel. Many otherwise experienced spreadsheet fans avoid them because they seem too complicated at first glance. The real problem is that pivot tables are rarely explained properly. Most books and the online Excel help use no end of cryptic jargon like "cross-tabulated computations" and "n-dimensional analysis." But if you stick with this chapter, you'll discover that pivot tables are really just a convenient way to build intelligent, flexible summary tables—nothing more, and nothing less.

Summary Tables Revisited

When you analyze large amounts of data, you can look at the same information in many different ways. How you organize and group the data often determines whether you find or overlook important trends.

For example, consider the small table of information shown in Figure 1. This table lists all the customers of a small business, along with information about their gender, the city they live in, and their level of education. Looking at this table, an important question comes to mind: is there a relationship between these different pieces of information and the amount of money a customer spends?


Figure 1. In this example, there are only 10 records, so patterns aren't difficult to spot. However, if you extended this list to hundreds or thousands of rows, you would definitely need a summary table to spot any relationships that might exist.

To look for trends and patterns in the customer list, it helps to build a summary table—a table that tallies key amounts, like the average amount spent for a customer in a specific city, education level, or gender. However, there are several potentially important relationships, and, therefore, several types of summary tables that you could create. Pivot tables are the perfect tool because they give you almost unlimited flexibility when you want to figure out different relationships. But before you learn about how to build pivot tables, it first helps to understand what life is like in Excel without them—because only then can you see why pivot tables make sense and decide whether you need them in one of your own workbooks.

Excel 2003: The Missing Manual

Related Reading

Excel 2003: The Missing Manual
By Matthew MacDonald

Life Without Pivot Tables

The most basic way to calculate summary information is to use the SUMIF( ) and COUNTIF( ) functions. For example, to find the average annual purchases for an individual in New York, you first calculate the total purchases made by all New York customers. Here's the formula you need:

=SUMIF(C2:C11, "New York", E2:E11)

This formula scans the City column (C2 to C11) looking for the text. Every time it finds a match, it adds the corresponding purchase amount from the Annual Purchases column (E2 to E11).

Once you have the total purchases, you need to divide this figure by the number of New York customers. To count the number of New York customers, you use the COUNTIF( ) function.

Here's the revised formula for finding the average annual purchase amount:

=SUMIF(C2:C11, "New York", E2:E11)/COUNTIF(C2:C11, "New York")

Finally, you can make this formula more generic so that you don't need to create a new version for each city. Instead, change the formula so that it retrieves the text it should match (the city's name) from a cell just to the left of where you're going to place the formula. (As shown in Figure 2, this group of "city" formulas resides in cells B15, B16, and B17.) Then, you can copy your formula into multiple rows. The formula won't change, but, in each row, the cell on the left will have a different value, instructing the formula to search for customers in a different city.


Figure 2. These three summary tables retrieve their data from the table shown in Figure 1 and calculate average purchases by grouping the customers into different categories. The purchase-by-city summary shows that there isn't a significant amount of difference based on location (although Seattle customers tend to spend a little less). The purchase-by-education summary demonstrates a dramatic difference, with less educated individuals making smaller purchases. Finally, the purchase-by-gender comparison turns up no variance at all.

Here's the perfected formula:

=SUMIF($C$2:$C$11, A15, $E$2:$E$11)/COUNTIF($C$2:$C$11, A15)

Note that in this formula, all the search ranges are fixed as absolute references using the dollar sign ($). The search text isn't fixed—that way, when you copy the formula to a new row, the formula uses the search text on that row.

Figure 2 shows several summary tables that use this technique for calculating averages.

In this example, building each table isn't terribly difficult, but the situation clearly becomes more tedious the more ways you want to compare the same data. It's not difficult to imagine a more realistic scenario where you might want to look at customer purchases based on age bracket, income level, and the answers to different customer survey questions. To get the full picture with this information, you would need to build each table from scratch.

In fact, in some of these more complex scenarios, you might need to group and then subgroup your information. Figure 3 shows a more advanced example of a table that calculates the variance in average annual purchases by city, and then shows the subdivided totals in each city by gender.


Figure 3. This summary table is a little more advanced. It groups and then subgroups data, which lets you find hidden trends. For example, in this case it identifies New York females as the best purchasers. Of course, there are far too few rows in this example for the results to be truly persuasive. In a table with thousands of rows, however, a grouped and subgrouped breakdown becomes much more meaningful.

The summary in Figure 3 performs a two-dimensional comparison. In other words, it compares two different groupings—one by city in different rows, and one by gender in different columns. This is a step up from the one-dimensional summary tables you saw earlier, but it's also far more difficult to correctly calculate. You could use the SUMPRODUCT( ) function, or create the table in two steps (first grouping the records by city, and then totaling them by gender). In either case, life gets much more complicated, and that's when pivot tables really shine.

Life with Pivot Tables

With a pivot table, you can create summary tables like the ones shown in Figure 2 and Figure 3 just by choosing the columns you want to compare. And once you've built your summary, you don't need to stick with it—instead, you can transform a purchase-by-education table into a purchase-by-city table just by dragging and dropping. You can even drill down into the details, apply filter criteria, and apply advanced subtotaling calculations. The following sections describe all of these techniques.

Building Pivot Tables

Now that you've learned the role that pivot tables play in summarizing data, it's time to create your own. Before you begin, you need to have a long list of raw data that you want to summarize. You could use the customer list from the previous example, but it's too small to really demonstrate the benefits of pivot tables. A better example is something like the list of order information shown in Figure 4.

TIP: You can also build a pivot table based on records that you select from an external database. But before you take this step, you must configure your database as an Excel data source.

Not all data is suited for a pivot table. To work well, your data needs to meet a few criteria: