The Anatomy of a Pivot Table
Because the anatomy of a pivot table is what
gives it its flexibility and, indeed, its ultimate functionality,
it would be difficult to truly understand pivot tables without
understanding their basic structure.
A pivot table is
of four areas. The
data you place in these areas define both the utility and the
appearance of the pivot table. Keeping in mind that you will go
through the process of creating a pivot table in the
let's prepare by taking a closer look at the four areas and the
functionality around them.
The data area is shown in Figure 1.5. It is a
large rectangular area below and to the right of the headings. In
this example, the data area contains a sum of the Revenue
Figure 1.5. The heart of the pivot table
is the data area. This area will typically include a total of one
or more numeric fields.
The data area is the area that calculates. This
area is required to have at least one field and one calculation on
that field in it. The data fields that you would drop here would be
things you would want to measure or calculate. The data area might
include Sum of Revenue, Count of Units, or Average of Price.
It is possible to have many fields in the data
area. You might include Sum of Quantity, Sum of Revenue, and
Average of Price.
It is also possible to have the same field
dropped in the data area twice, but with different calculations. A
marketing manager might want to see Minimum of Price, Average
Price, and Maximum of Price.
The row area is shown in Figure 1.6. It is
composed of the headings that go down the left side of the pivot
Figure 1.6. The headings down the left
side make up the row area of the pivot table.
Dropping a field into the row area will display
the unique values from that field down the rows of the left side of
the pivot table. The row area typically has at least one field,
although it is possible to have no fields. The example earlier in
the chapter where you needed to produce a one-line report of
credits is an example where there are no row fields.
The types of data fields you would drop here are
things you want to
and categorizefor example, Products,
Names, and Locations.
The column area is composed of headings that
stretch across the top of
in a pivot table. The pivot table
in Figure 1.7 has the month field in the column area.
Figure 1.7. The column area stretches
across the top of the columns. In this example, it contains the
unique list of months in your dataset.
Dropping fields into the column area would
display your items in a column-oriented perspective. The column
area is ideal to show trending over time. The types of data fields
you would drop here are things you want to trend or show side by
sidefor example, Months, Periods, and Years.
The page area is an optional set of one or more
at the top of the pivot table. In Figure 1.8, the page
area contains the Region field, and the pivot table is set to show
Figure 1.8. Page fields are great for
quickly filtering a report. The Region drop-down in
you to print this report for one particular region manager.
Dropping fields into the page area would allow
you to filter the data items in your fields. The page area is
optional and comes in handy when you need to filter your results
dynamically. The types of data fields you would drop here are
things you would want to isolate and focus onfor example, Regions,
Line of Business, and Employees.