Calculated fields

You'll find both calculated fields and calculated items on the Pivot Table Tools Options contextual tab (Analyze in 2013). Creating a calculated field actually adds a field to your field list that you can select or unselect. If you have several Pivot Tables built from this data, the new calculated field will be added to all of your Pivot Table field lists.

Example: Say you have 12 columns of quarterly data and want to show quarterly totals. In the Calculations group, click the Fields, Items, & Sets button. Choose Calculated Field. Name this new field First Quarter. The formula is built similarly to any other formula in Excel, except that you use the Insert Field button to build the expression. It is similar to the way expressions are built in Access. In this case, use the SUM function and separate each inserted field by a comma. Doing this for all four quarters yields quarterly rather than monthly totals.

Calculated items

Calculated items solve a different problem. If you have several text fields that should generate a single row field instead of separate ones, calculated items will help. So, if you have several spelling variations for a single item and adjusting the source data isn't practical, you can create a single calculated item that includes all of the variations.

Again, choose the Options tab, Fields, Items & Sets. But this time, choose Calculated Item. Select the variants from the list on the right, and build the expression similarly to how it was done above.

Example: Say you create a calculated item called Alice Mutton Products that combines three different spellings of the product name. Keep two adjustments in mind. First, you need to filter out the variants and leave only the combined field. Otherwise, you will double count the values. Second, your new combined field will initially appear at the bottom of the list. With this sample data, a simple ascending sort brings it back up to the top.

Get Pivot Data

Another way to use Pivot Table report values in calculations is GetPivotData. By default, if you refer to a value in a finished Pivot Table, it will yield a rather long function beginning with =GETPIVOTDATA. Rather than referring to a particular cell reference, it refers to the summarized value that cell represents.

Example: Say you want to use the report total in a calculation with a multiplication factor, like 15%. You would type in the formula =15%* and click on the total cell.

If your Pivot Table should change, the GETPIVOTDATA reference will always refer to the total, no matter where it shows up. If it doesn't automatically generate a GETPIVOTDATA statement, on the Pivot Table Tools Options tab, click the drop-down arrow to the right of the Options button and toggle Generate GetPivotData on (checked).