Excel® 2007 PivotTables and PivotCharts

Appendix 1. Building Formulas for PivotTables

Introducing Formulas

One of the most powerful techniques you can use to enhance PivotTable‐based data analysis is custom calculations in your reports. Whether you create a new calculated field or one or more new calculated items within a field, custom calculations enable you to interrogate your data and return the exact information that you require.

To get the most out of custom calculations, you need to understand formulas: their components, types, and how to build them. In this appendix, you learn how to understand and work with formulas and functions. However, the formulas you use with a PivotTable differ in important ways from regular Excel worksheet formulas. This appendix highlights the differences and focuses on formula ideas and techniques that apply to PivotTable calculations. This section gets you started by showing you the basics of the two major formula components: operands and operators.

For the specifics of implementing custom calculations in your PivotTable reports, see Chapter 8.

Operands

Operands are the values that the formula uses as the raw material for the calculation. In a custom PivotTable calculation, the operands can be constants, worksheet functions, fields from your data source, or items within a data source field. Note that you cannot use cell references or defined names as operands in the PivotTable formula.

Constants

A constant is a fixed value that you insert into a formula and use as is. For example, suppose ...