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.

Image from book
Figure: 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. Figure lists the available options.

Figure: Custom Calculation Options
Open table as spreadsheet

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).

Image from book
Figure: When you choose a calculation such as Difference From, the Base Field and Base Item boxes display relevant options.

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.

Image from book
Figure: From this table, a PivotTable will apply the COUNT function to the Date field to count performances.
Image from book
Figure: The PivotTable uses the Date field from Figure twice-once to count performances, a second time to calculate percentage of total.

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.

Image from book
Figure: 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 shows an example of a calculated field.

Image from book
Figure: 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.

Image from book
Figure: 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.

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.

Image from book
Figure: 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.

Image from book
Figure: Excel lists calculated fields and items on a new worksheet.

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.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows