Analyzing Microsoft Excel 2010 Data with PivotTable Reports

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 23-15. 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.

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

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 to 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 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 23-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 23-16 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.

Table 23-1 Custom Calculation Options

Figure 23-16 When you choose a calculation such as Difference From, the Base Field and Base Item boxes display relevant options.

Figure 23-17 and Figure 23-18 illustrate some ways you can modify default calculations and Values field names. The table in Figure 23-17 lists 2006–2007 performances at major opera houses around the world by theater, country, opera, composer, and performance date. The PivotTable in Figure 23-18 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 23-15) to change the name from Count of Date to No. of Performances. When we dragged the second instance of the Date field to 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 23-17 From this table, a PivotTable will apply the COUNT function to the Date field to count performances.

You’ll find the OperaSked.xlsx file with the other examples on the companion Web site.

Figure 23-18 The PivotTable uses the Date field from Figure 23-17 twice—once to count performances and a second time to calculate percentage of total.

Using Calculated Fields and Items

In case custom calculations don’t meet all your analytical 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 Field, Items, & Sets in the Calculations group. On the Fields, Items, & Sets menu, click Calculated Field. Figure 23-19 shows the Insert Calculated Field dialog box.

Figure 23-19 Create a calculated field in this dialog box.

Type a name for your calculated field in the Name box, and 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 23-20 shows an example of a calculated field.

Figure 23-20 This calculated field multiplies 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 Fields, Items, & Sets in the Calculations group. On the Fields, Items, & Sets menu, click Calculated Item. Excel displays a dialog box comparable to the one in Figure 23-21.

Figure 23-21 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, and 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 23-22 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 23-22 This calculated item appears 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 Fields, Items, & Sets in the Calculations group. On the Fields, Items, & Sets menu, click List Formulas. Excel displays the list on a new worksheet, as shown in Figure 23-23.

Figure 23-23 Excel lists calculated fields and items on a new worksheet

As the note in Figure 23-23 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 Fields, Items, & Sets in the Calculations group. Then click Solve Order.