What Is a Pivot Tableby Matthew MacDonald, author of Excel: The Missing Manual
- 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:
- Summary Tables Revisited
- 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.