# Changing PivotTable Calculations

## Changing PivotTable Calculations

By default, Excel populates the Values area of your PivotTable by applying the SUM function to any numeric field you put there or by applying the COUNT function to any nonnumeric field. But you can choose from many alternative forms of calculation, and you can add your own calculated fields to the table.

### Using a Different Summary Function

To switch to a different summary function, right-click any cell in the Values area of your PivotTable, and then click Value Field Settings. (Alternatively, click the Options tab under PivotTable Tools, and then click Field Settings in the Active Field group.) Excel displays the Value Field Settings dialog box, shown in Figure. Select the function you want from the Summarize Value Field By list, and then click OK.

Excel fills in the Custom Name line in this dialog box according to your selection in the Summarize Value Field By list. If you switch from SUM to AVERAGE, for example, the Custom Name line changes to include the word Average. You can type whatever you like there, though.

### Applying Multiple Summary Functions to the Same Field

You can apply as many summary functions as you want to a value field. To use a second or subsequent function with a field that's already in the Values area of your PivotTable, drag another copy of the field from the PivotTable Field List window into the Values box. Then select a Values area cell, return to the Value Field Settings dialog box, and select the function you want to use. The available functions are SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT NUMBERS, STDDEV, STDDEVP, VAR, and VARP.

### Using Custom Calculations

In addition to the standard summary functions enumerated in the previous paragraph, Excel also offers a set of custom calculations. With these you can have each item in the Values area of your table report its value as a percentage of the total values in the same row or column, create running totals, or show each value as a percentage of some base value.

To apply a custom calculation, right-click a cell in the Values area, and then click Value Field Settings. Click the Show Values As tab in the Value Field Settings dialog box. Then select a calculation from the Show Values As list. Figure lists the available options.

 Difference From Displays data as a difference from a specified base field and base item % Of Displays data as a percentage of the value of a specified base field and base item % Difference From Displays data as a percentage difference from a specified base field and base item Running Total In Displays data as a running total % Of Row Displays each data item as a percentage of the total of the items in its row % Of Total Displays each data item as a percentage of the grand total of all items in its field Index Uses this formula: ((value in cell) * Grand Total of Grand Totals)) / ((Grand Row Total) * (Grand Column Total))

When you select a calculation in the Show Values As list, the Base Field and Base Item boxes display choices that are relevant to your calculation. For example, as Figure shows, if you select Difference From in our books example, the Base Field box displays Quarter, Category, Channel, and so on. If you select Quarter in this list, the Base Item box presents the four quarters, along with the self-explanatory items (Previous) and (Next).

Figure and Figure illustrate some ways you can modify default calculations and Values field names. The table in Figure lists 2006–2007 performances at major opera houses around the world by theater, country, opera, composer, and performance date. The PivotTable in Figure includes the Date field twice in the Values box. The default summary calculation for date data is Count, and that's fine because we want the number of performances and counting dates is a way to get that. But we used the Custom Name box in the Value Field Settings dialog box (refer to Figure) to change the name from Count of Date to No. of Performances. When we dragged the second instance of the Date field into the Values box, we used the Value Field Settings dialog box to make the field report the percentage of total. You could use similar techniques with other kinds of polling or survey applications.

On the CD You'll find the OperaSked. xlsx file in the Sample Files section of the companion CD.

 Note If you filter a field, percentage-of-total calculations are based on the data that meets the filter criterion, not the unfiltered data set.

### Using Calculated Fields and Items

In case custom calculations don't meet all your analytic needs, Excel lets you add calculated fields and calculated items to your PivotTables. A calculated field is a new field, derived from calculations performed on existing fields in your table. A calculated item is a new item in an existing field, derived from calculations performed on other items that are already in the field. After you create a custom field or item, Excel makes it available to your table, as though it were part of your data source.

Custom fields and items can apply arithmetic operations to any data already in your PivotTable (including data generated by other custom fields or items), but they cannot reference worksheet data outside the PivotTable.

#### Creating a Calculated Field

To create a calculated field, select any cell in the PivotTable. Then click the Options tab under PivotTable Tools, and click Formulas in the Tools group. On the Tools menu, click Calculated Field. Figure shows the Insert Calculated Field dialog box.

Type a name for your calculated field in the Name box. Then type a formula in the Formula box. To enter a field in the formula, select it from the Fields list, and click Insert Field. Figure shows an example of a calculated field.

Excel adds a new calculated field to your PivotTable when you click either Add or OK. You can then work with the new field using the same techniques you use to work with existing fields.

#### Creating a Calculated Item

To create a calculated item for a field, select any existing item in the field or the field heading. Then click the Options tab under PivotTable Tools, and click Formulas in the Tools group. On the Formulas menu, click Calculated Item. Excel displays a dialog box comparable to the one in Figure.

To create a calculated item, type a unique name for the item in the Name box. Then enter a formula in the Formula box. You can select from the Fields and Items lists and click Insert Field and Insert Item to enter field and item names in the formula.

 Note You cannot create calculated items in fields that have custom subtotals.

Figure shows an example of a calculated item. In this case the new item represents domestic sales divided by the sum of international and mail order sales.

#### Displaying a List of Calculated Fields and Items

To display a list of your calculated fields and items, along with their formulas, click the Options tab under PivotTable Tools, and then click Formulas in the Tools group. On the Formulas menu, click List Formulas. Excel displays the list on a new worksheet, as shown in Figure.

As the note in Figure indicates, you need to be careful when a cell in your table is affected by more than one calculated field or item. In such cases, the value is set by the formula that's executed last. The Solve Order information in the list of calculated fields and items tells you which formula that is. If you need to change the solve order, select the worksheet that contains the PivotTable, click the Options tab under PivotTable Tools, and then click Formulas in the Tools group. On the Formulas menu, click Solve Order.