Microsoft Office Tutorials and References
In Depth Information
Chapter 17: Working with Pivot Tables
Data appropriate for a pivot table
A pivot table requires that your data is in the form of a rectangular database. 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. Although Excel can generate a pivot table from any database, not all databases
Generally speaking, fields in a database table consist of two types:
● 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, AcctType, OpenedBy,
Branch, and Customer fields are category fields because they describe the data in the
A database table that’s appropriate for a pivot table is said to be normalized. In other words,
each record (or row) contains information that describes the data.
A single database table can have any number of data fields and category fields. When you
create a pivot table, you usually want to summarize one or more of the data fields. Conversely, the
values in the category fields appear in the pivot table as rows, columns, or filters.
If you’re not clear on the concept, the companion CD-ROM contains a workbook named
normalized data.xlsx . This workbook contains an example of a range of data before and after
being normalized so it’s suitable for a pivot table.
Examining the recorded code for the pivot table
VBA code that works with pivot tables can be confusing. to make any sense of the recorded macro,
you need to know about a few relevant objects, all of which are explained in the Help system.
h PivotCaches : A collection of PivotCache objects in a Workbook object (the data
used by a pivot table is stored in a pivot cache).
h PivotTables : A collection of PivotTable objects in a Worksheet object.
h PivotFields : A collection of fields in a PivotTable object.
h PivotItems : A collection of individual data items within a field category.
h CreatePivotTable : A method that creates a pivot table by using the data in a pivot
Cleaning up the recorded pivot table code
As with most recorded macros, the preceding example isn’t as efficient as it could be. And, as I
noted, it’s very likely to generate an error. You can simplify the code to make it more understandable