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
composed
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
next
chapter,
let's prepare by taking a closer look at the four areas and the
functionality around them.
Data Area
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
field.
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.
Row Area
The row area is shown in Figure 1.6. It is
composed of the headings that go down the left side 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
group
and categorizefor example, Products,
Names, and Locations.
Column Area
The column area is composed of headings that
stretch across the top of
columns
in a pivot table. The pivot table
in Figure 1.7 has the month field in the column area.
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.
Page Area
The page area is an optional set of one or more
drop-
downs
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
all
regions
.
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.
|
