How to Create Custom Calculations for an Excel Pivot Table

By Stephen L. Nelson, E. C. Nelson

Excel pivot tables provide a feature called Custom Calculations. Custom Calculations enable you to add many semi-standard calculations to a pivot table. By using Custom Calculations, for example, you can calculate the difference between two pivot table cells, percentages, and percentage differences.

This pivot table shows coffee product sales by month for the imaginary business that you own and operate. Suppose, however, that you want to add a calculated value to this pivot table that shows the difference between two months’ sales. You may do this so that you easily see large changes between two months’ sales. Perhaps this data can help you identify new problems or important opportunities.

image0.jpg

To add a custom calculation to a pivot table, you need to complete two tasks: You need to add another standard calculation to the pivot table, and you need to then customize that standard calculation to show one of the custom calculations listed in Table 5-1.

Calculation Description
Normal You don’t want a custom calculation.
Difference From This is the difference between two pivot table cell values; for
example, the difference between this month’s and last month’s
value.
% of This is the percentage that a pivot table cell value represents
compared with a base value.
% Difference From This is the percentage difference between two pivot table cell
values; for example, the percentage difference between this
month’s and last month’s value.
Running Total In This shows cumulative or running totals of pivot table cell
values; for example, cumulative year-to-date sales or
expenses.
% of Row This is the percent that a pivot table cell value represents
compared with the total of the row values.
% of Column This is the percent that a pivot table cell value represents
compared with the total of the column values.
% of Total This is the pivot table cell value as a percent of the grand
total value.
Index Kind of complicated, dude. The index custom calculation uses
this formula: ((cell value) x (grand total of grand totals)) /
((grand total row) x (grand total column)).

To add a second standard calculation to the pivot table, add a second data item. For example, if you want to calculate the difference in sales from one month to another, you need to drag a second sales data item from the field list to the pivot table.

image1.jpg

After you add a second standard calculation to the pivot table, you must customize it by telling Excel that you want to turn the standard calculation into a custom calculation. To do so, follow these steps:

  1. Click the new standard calculation field from the ” Values box, and then choose Value Field Settings from the shortcut menu that appears.

  2. When Excel displays the Value Field Settings dialog box, click the Show Values As tab.

    The Show Values As tab provides three additional boxes: Show Values As, Base Field, and Base Item.

    The Base Field and Base Item list box options that Excel offers depend on which type of custom calculation you’re making.

    image2.jpg

  3. Select a custom calculation by clicking the down-arrow at the right side of the Show Values As list box and then selecting one of the custom calculations available in that drop-down list.

    For example, to calculate the difference between two pivot table cells, select the Difference From entry. Refer to Table 5-1 for explanation of the possible choices.

  4. Instruct Excel about how to make the custom calculation.

    After you choose the custom calculation that you want Excel to make in the pivot table, you make choices from the Base Field and Base Item list boxes to specify how Excel should make the calculation. For example, to calculate the difference in sales between the current month and the previous month, select Month from the Base Field list box and Previous from the Base Item list box.

    image3.jpg

  5. Appropriately name the new custom calculation in the Custom Name text box of the Data Field Settings dialog box.

    For example, to calculate the change between two pivot table cells and the cells supply monthly sales, you may name the custom calculation Change in Sales from Previous Month. Or, more likely, you may name the custom calculation Mthly Change.

  6. Click OK.

    Excel adds the new custom calculation to your pivot table.

    image4.jpg