Microsoft Office Tutorials and References

In Depth Information

Data Appropriate for a Pivot Table

A pivot table requires that your data be in the form of a rectangular table. You can store the data in either a

worksheet range (which can either be a normal range, or a table created by choosing Insert
⇒
Tables
⇒
Table) or

an external database file. Although Excel can generate a pivot table from any table, not all tables are appropri-

ate.

Generally speaking, fields in the database table consist of two types of information:

•
Data:
Contains a value or data that you want to summarize. 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 single table can have any number of data fields and category fields. When you create a pivot table, you usu-

ally want to summarize one or more of the data fields. Conversely, the values in the category fields appear in

the pivot table as row labels, column labels, or filters.

Exceptions exist, however, and you may find Excel's pivot table feature useful even for a table that doesn't con-

tain numerical data fields. In such a case, the pivot table provides counts rather than sums.

Figure 18-4 shows an example of an Excel range that is
not
appropriate for a pivot table. Although the range

contains descriptive information about each value, it does
not
consist of normalized data. In fact, this range ac-

tually resembles a pivot table summary, but it is much less flexible.

This workbook, named normalized data.xlsx, is available at this topic's website.