Excel 2013: The Missing Manual

Chapter 26. 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 create the perfect summary that compares, say, sales in different regions, you may want to compare sales across different product lines or different customers. But for that, you need to start from scratch and build a whole new report.

Fortunately, Excel has a feature called pivot tables that provides a solution. Pivot tables quickly summarize long lists of data, without requiring you to write a single formula or copy 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. The 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 ...