Chapter 29: Introducing PivotTables
29
If you were handed a strange object and asked to identify it, you’d probably look at it from several
different angles in an attempt to figure it out. Working with a PivotTable is similar to investigating a
strange object. In this case, the object happens to be your data. A PivotTable invites experimenta-
tion, so feel free to rotate and manipulate the PivotTable until you’re satisfied. You may be surprised
at what you discover.Data appropriate for a PivotTable
A PivotTable requires that your data be in the form of a rectangular table. You can store
the database in either a worksheet range (which can be a table or just a normal range) or
an external database file. And although Excel can generate a PivotTable from any database,
not all databases benefit.Generally speaking, fields in a table consist of two types of information:Data Contains a value or data to be summarized. For the bank account example, the
Amount field is a data field.
Category Describes the data. For the bank account data, the Date, Weekday, AcctType,
OpenedBy, Branch, and Customer fields are category fields because they describe the data in
the Amount field.A database table that’s appropriate for a PivotTable is said to be “normalized.” In other words, each record (or row)
contains information that describes the data.
A single table can have any number of data fields and category fields. When you create a
PivotTable, you usually want to summarize one or more of the data fields. Conversely, the
values in the category fields appear in the PivotTable as rows, columns, or filters.Exceptions exist, however, and you may find the Excel PivotTable feature useful even for
databases that don’t contain actual numerical data fields.Chapter 30 has an example of a PivotTable created from non-numeric data.Figure 29.4 shows an example of an Excel range that is not appropriate for a PivotTable.
You might recognize this data from the outline example in Chapter 27, “Creating and