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:
Summary Tables Revisited
Life Without Pivot Tables
Life With Pivot Tables
Building Pivot Tables
Preparing a Pivot Table
Understanding Pivot Table Regions
Laying Out a Pivot Table
Rearranging a Pivot Table
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.
Related Reading
Excel 2003: The Missing Manual
By MatthewMacDonald
