2007 MicrosoftВ® Office System Inside Out (Bpg-Inside Out)

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 15–13. Select the function you want from the Summarize Value Field By list, and then click OK.

Figure 15–13: Using this dialog box, you can change the function applied to a field in the Values area of your PivotTable.

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. Table 15–1 lists the available options.

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 15–14 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 15–14: When you choose a calculation such as Difference From, the Base Field and Base Item boxes display relevant options.

Figure 15–15 and Figure 15–16 illustrate some ways you can modify default calculations and Values field names. The table in Figure 15–15 lists 2006–2007 performances at major opera houses around the world by theater, country, opera, composer, and performance date. The PivotTable in Figure 15–16 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 15–13) 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.

Figure 15–15: From this table, a PivotTable will apply the COUNT function to the Date field to count performances.

Figure 15–16: The PivotTable uses the Date field from Figure 15–15 twice-once to count performances, a second time to calculate percentage of total.

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

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 15–17 shows the Insert Calculated Field dialog box.

Figure 15–17: Create a calculated field in this 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 15–18 shows an example of a calculated field.

Figure 15–18: This calculated field multiples an existing field by a constant.

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 15–19.

Figure 15–19: Use this dialog box to create a calculated item for a field.

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.

Figure 15–20 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.

Figure 15–20: This calculated item will appear by default whenever you include the Channel field in the PivotTable.

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 15–21.

Figure 15–21: Excel lists calculated fields and items on a new worksheet.

As the note in Figure 15–21 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.