Microsoft Office Tutorials and References
In Depth Information
Tip 77: Identifying Data Appropriate for a Pivot Table
Identifying Data Appropriate for a Pivot
A pivot table requires that your data is in the form of a rectangular database 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 pivot table from any database, not all databases benefit.
Figure 77-1 shows part of a simple database table that has five columns and 3,144 rows (one row for
each county). This data is appropriate for a pivot table. For example, a pivot table can instantly
calculate the total population by state or by region and display the values in a nicely formatted table.
Figure 77-1: This data is appropriate for a pivot table.
Generally speaking, fields in a database table consist of two types of information:
➤ Data: Contains a value or data to be summarized. For this example, the 1990 Population and
the 2000 Population fields are data fields.
➤ Category: Describes the data. For this example, the Country, State, and Region fields are
category fields because they describe the two data fields.
The data versus category distinction can be blurry at times. Often a pivot table will display counts of
items within a category. In such a case, a category is serving as a data field.
A database table that’s appropriate for a pivot table is said to be normalized. In other
words, each row contains information that describes the data in the row.