Excel® 2007 PivotTables and PivotCharts

Chapter 8. Creating Custom PivotTable Calculations

Introducing Custom Calculations

A custom calculation is a formula that you define yourself to produce PivotTable values that would not otherwise appear in the report if you used only the source data fields and Excel's built‐in summary calculations. Custom calculations enable you to extend your data analysis to include results that are specific to your needs.

For example, suppose your PivotTable shows employee sales by quarter and you want to award a 10 percent bonus to each employee with sales of more than $25,000 in any quarter. You can create a custom calculation that checks for sales greater than $25,000 and then multiplies those by 0.1 to get the bonus number.

A custom calculation is simply an Excel formula that gets applied to your source data to produce a summary result. In other words, in most cases the custom calculation is just like Excel's built‐in PivotTable summary calculations, except that you define the specifics of the calculation yourself. Because you are creating a formula, you can use most of Excel's formula power, which gives you tremendous flexibility to create custom calculations that suit your data analysis needs. And by placing these calculations within the PivotTable itself — as opposed to, for example, adding them to your source data — you can easily update the calculations as needed and refresh the report results.

Formula Basics

Custom calculations are formulas with certain restrictions imposed; see the next ...