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.
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.
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:
Click the new standard calculation field from the ” Values box, and then choose Value Field Settings from the shortcut menu that appears.
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.
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.
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.
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.
Click OK.
Excel adds the new custom calculation to your pivot table.
