Microsoft Office Tutorials and References
In Depth Information
Creating a More Complex Pivot Table
To add a calculated field to a pivot table manually, use the PivotTable➜Options ➜
Calculations➜Fields, Items, & Sets ➜Calculated Field command, which displays the
Insert Calculated Field dialog box.
Finally, the code makes a few cosmetic adjustments:
h Applies a number format to the DataBodyRange (which represents the entire pivot
h Applies a style.
h Hides the captions (equivalent to the PivotTable Tools➜Options➜Show ➜Field Headers
Figure 17-5: The Pivot Table Field List.
h Changes the captions displayed in the pivot table. For example, Sum of Budget is
replaced by Budget . Note that the string Budget is preceded by a space. Excel doesn’t
allow you to change a caption that corresponds to a field name, so adding a space gets
around this restriction.
While creating this procedure, I used the macro recorder extensively to learn about the
various properties. That, combined with the information in the Help system (and a fair
amount of trial and error), provided all the information I needed.