Excel 2007: The Missing Manual

Chapter 21. 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. 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. 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 ...