A custom calculation is a formula that you define yourself to produce PivotTable values that would not otherwise appear in the report if you used only the source data fields and Excel's built-in summary calculations. Custom calculations enable you to extend your data analysis to include results that are specific to your needs.

For example, suppose your PivotTable shows employee sales by quarter and you want to award a 10 percent bonus to each employee with sales of more than $25,000 in any quarter. You can create a custom calculation that checks for sales greater than $25,000 and then multiplies those by 0.1 to get the bonus number.

A custom calculation is simply an Excel formula that gets applied to your source data to produce a summary result. In other words, in most cases the custom calculation is just like Excel's built-in PivotTable summary calculations, except that you define the specifics of the calculation yourself. Because you are creating a formula, you can use most of Excel's formula power, which gives you tremendous flexibility to create custom calculations that suit your data analysis needs. And by placing these calculations within the PivotTable itself — as opposed to, for example, adding the calculations to your source data — you can easily update the calculation as needed and refresh the report results.

Custom calculations — whether they are calculated fields or calculated items — are powerful additions to your PivotTable analysis toolbox. However, although custom calculation formulas look like regular worksheet formulas, you cannot assume that everything you do with a worksheet formula you can also do with a custom PivotTable formula. In fact, there are a number of limitations that Excel imposes on custom formulas, such as not being able to reference data outside the pivot cache, and not being able to use custom items in conjunction with grouping.

=UnitsInStock * UnitPrice

=UnitsInStock * UnitPrice

If your data analysis requires PivotTable results that are not available using just the data source fields and Excel's built-in summary calculations, you can insert a calculated field that uses a custom formula to derive the results you need.

A custom calculated field is based on a formula that looks much like an Excel worksheet formula; see the section "Introducing Custom Calculations," earlier in this chapter. However, you do not enter the formula for a calculated field into a worksheet cell. Instead, Excel offers the Calculated Field feature which provides a dialog box for you to name the field and construct the formula. Excel then stores the formula along with the rest of the PivotTable data in the pivot cache.

Having the calculated field stored in the pivot cache is handy because the pivot cache is often shared by other PivotTables that you have built using the same source data. When you build a second PivotTable based on the same source data as the first, Excel asks if you want to reuse the original data in the new PivotTable. Therefore, you can reuse calculated fields in other PivotTable reports, which can save time.

Insert a Custom Calculated Field

Note

This chapter uses the PivotTables.xls spreadsheet, available at www.wiley.com/go/pivottablesvb, or you can create your own sample database.

You can also click Insert→Calculated Field.

The Insert Calculated Field dialog box appears.

Extra

As pointed out above, the new calculated field appears in the PivotTable Field List. However, the calculated field also appears in the PivotTable Field List for every PivotTable that uses the same pivot cache. Therefore, you can reuse calculated fields in other PivotTable reports where the calculation makes sense. For example, you would not reuse a sales commission calculation in a PivotTable that provides an inventory summary. To do this, drag the calculated field from the PivotTable Field List and drop it inside the PivotTable's data area.

When you add a calculated field to the PivotTable, Excel also adds a new row with the label Total Sum of FieldName, where FieldName is the name you provided for the calculated field, such as Total Sum of Commission. Unfortunately, this total is often inaccurate and you should be careful not to assume that it is correct. The problem is that it is not a sum of the values in the calculated field. Instead, Excel applies the calculated field's formula to the sum of whatever field or fields you referenced in the formula. In the example used in this task, Excel applies the formula to the Total Sum of Extended Price value, which is not the correct way to calculate the total commission. To work around this problem, you need to set up a formula outside the PivotTable that sums the commission values.

If your data analysis requires PivotTable results that are not available using just the data source fields and Excel's built-in summary calculations, you can insert a calculated item that uses a custom formula to derive the results you need.

As with a calculated field, a calculated item uses a formula much like an Excel worksheet formula; see the section "Introducing Custom Calculations," earlier in this chapter. Again, however, you do not enter the formula for a calculated item into a worksheet cell. Instead, Excel offers the Calculated Item command that displays a dialog box where you name the item and construct the formula. Excel then stores the formula along with the rest of the PivotTable data in the pivot cache.

Remember that the Calculated Item feature creates just a single item in a field. However, you are free to add as many calculated items as you need. For example, suppose you want to compare the performance of male and female sales representatives. One way to do that would be to create one calculated item that returns the average sales of the men, and a second calculated item that returns the average sales of the women.

Before you create a calculated item, be sure to remove all groupings from your PivotTable; see the section "Understanding Custom Calculation Limitations," earlier in this chapter. Note that it is not enough to simply remove the grouped field from the PivotTable. Instead, you must run the Ungroup command on the field, as described in the Chapter 4 task "Ungroup Values."

Insert a Custom Calculated Item

The Insert Calculated Item dialog box appears.

You can also double-click the field.

You can also double-click the item.

Extra

When you insert an item into a field, that item becomes part of the field within the pivot cache. For example, when you select the field in the Calculated Item dialog box, the calculated item appears in the Items list along with the regular field items. This is handy because it enables you to use the calculated item's result in other formulas.

The downside to having the calculated item become part of the field is that Excel includes the calculated item's result in the PivotTable subtotals, row or column totals, and Grand Total. This almost always causes the totals for the affected field to be inaccurate, so double-check field totals when you use a calculated item.

One way to work around this problem is to create a calculated item that uses the SUM() function to add the regular field items. Another workaround would be to hide the calculated items; see the Chapter 4 task "Hide Items in a Row or Column Field."

If you notice an error in a custom calculation, or if your data analysis needs changed, you can modify the formula used by a calculated field or calculated item.

When you add a custom calculation to a PivotTable, Excel first checks the formula to make sure that it contains no syntax errors — such as a missing comma or parenthesis — or illegal operands — such as cell addresses, unknown field or item names, or functions not supported by custom calculations. If Excel finds an error, it displays a dialog box to let you know and does not add the custom calculation to the PivotTable.

However, just because a formula contains no syntax errors or illegal operands does not necessarily mean that the formula's results are correct. In a calculated field, you may have used the wrong function for the result you are seeking. In a calculated item involving several field items, you may have accidentally missed an item.

Alternatively, your formula may be working perfectly, but it may no longer be the result you need if your data analysis needs have changed. For example, you might have a calculated field that determines whether employees get paid a bonus by looking for sales greater than $50,000. If that threshold changes to $75,000, then your calculated field will no longer produce the results you want.

Whether your custom calculation contains an error or your data analysis needs have changed, Excel enables you to edit the formula to produce the result you want.

Edit a Custom Calculation

EDIT A CALCULATED FIELD

You can also click Insert→Calculated Field.

The Insert Calculated Field dialog box appears.

Excel updates the calculated field's results.

EDIT A CALCULATED ITEM

The Insert Calculated Item dialog box appears.

You can also edit a calculated item by clicking the item's result. The formula appears in Excel's formula bar, and you can edit it from there.

Excel updates the calculated item's results.

Apply It

If you use VBA to insert a calculated field or item, then you can modify the custom calculation by editing the macro.

For calculated fields, first note that each PivotTable object has a CalculatedFields collection. To insert a calculated field, use the CalculatedField object's Add method. The following statement inserts a calculated field named Commission:

Example:

objPT.CalculatedFields.Add _
    Name:="Commission", _
    Formula:="= IF(ExtendedPrice> 50000,ExtendedPrice* 0.1, 0)"
objPT.PivotFields("Commission").Orientation = xlDataField

For calculated items, note that each row and column PivotField object has a CalculatedItems collection. To insert a new calculated item, use the CalculatedItems object's Add method. The following statement inserts a calculated item named Average Sales (Men) into the Salesperson field:

Example:

objPT.PivotFields("Salesperson").CalculatedItems.Add _
    Name:="Average Sales (Men)", _
    Formula:="=AVERAGE('Andrew Fuller','Michael Suyama'," & _
             "'Robert King','Steven Buchanan')"

You can ensure that your calculated items return the correct results by adjusting the order in which Excel solves the items.

If you have multiple calculated items in a PivotTable, you may end up with cells that have values that rely on two or more formulas. For example, you may have one calculated item in a row field and another in a column field. In the PivotTable cell that lies at the intersection of these two items, the value will be the result of Excel applying one formula and then the other. The default order is the order in which you added the items to the PivotTable. Most of the time, this order does not matter. However, the order that Excel solves calculated items can make a difference.

For example, suppose you have a PivotTable that shows the number of units that customers ordered based on two different promotional offers — 1 Free with 10 and Extra Discount — broken down by advertisement — Direct Mail, Magazine, and Newspaper. Suppose further that you have added a calculated item in the row field that returns the percentage of units ordered for each promotion. For example, with the Magazine advertisement, you might find that 52.5% of units were ordered via the 1 Free with 10 promotion, and the other 47.5% were ordered via the Extra Discount promotion.

If you also want to know the overall percentages of each promotion, you run into a problem because Excel's default Grand Total calculation will add the percentages. To work around this problem, you could turn off the Grand Total calculation and create a new calculated item in the column field that adds the various Advertisement items together. If this calculated item is solved after the first one, you end up with the same problem: Excel adds the percentages. To fix this, you need to change the solve order so that Excel adds the Advertisement items first, and then calculates the percentages.

Change the Solve Order of Calculated Items

The Calculated Item Solve Order dialog box appears.

Excel moves the item up.

Excel adjusts the solve order and recalculates the results.

Apply It

There is another method you can often use to fix solve order problems in a PivotTable. This method relies on the fact that, in a cell that relies on multiple calculated item formulas, Excel applies the last formula in the solve order to the cell. In this task, examine the Excel formula bar in the first screen shot. This is the formula that Excel is applying to cell E7:

='Direct mail' + Magazine + Newspaper

Now examine the formula bar in the last screen shot. You can see that the formula Excel is now applying to cell E7 is the following:

='1 Free with 10' / ('1 Free with 10' + 'Extra Discount')

Therefore, in most cases you can fix the solve order problem by copying the correct formula from another cell (such as D7 in the example) and then pasting it into the problem cell.

Note, however, that Excel does not allow you to simply copy the cell and then paste it because Excel does not allow a PivotTable to be changed in this way. Instead, you must open the cell that has the formula you want, copy the cell text, open the destination cell, delete the existing formula, and then paste the copied formula.

You can document your PivotTable's custom calculations by displaying the formulas for each calculated field and calculated item on a separate worksheet.

When you add several custom calculations to a PivotTable, the report can become difficult to decipher. This is particularly true with calculated fields because there is nothing in the PivotTable that shows the field's underlying formula to the reader. Calculated fields are more transparent because you can see the formula by selecting the cell, but you have no way of knowing whether there are multiple calculations that determine the cell's value.

To help the reader decipher a PivotTable's custom calculations — whether that reader is another person not familiar with the PivotTable or yourself a few months from now — Excel offers a feature that enables you to document all the custom calculations, including the formulas and solve order, in a separate worksheet.

You can use a VBA macro to list the custom calculations for a PivotTable. To do this, run the PivotTable object's ListFormulas method, as shown in the following macro:

Sub ListPivotTableFormulas()
     Dim objPT As PivotTable
     '
     ' Work with the first PivotTable
     Set objPT = ActiveSheet.PivotTables(1)
     '
     ' List the PivotTable's formulas
     objPT.ListFormulas
End Sub

List Your Custom Calculations

Excel inserts a new worksheet and displays the solve order, name, and formula for each calculated field and item.

When you no longer need a calculated field or calculated item, you can delete the calculation from the PivotTable.

Custom calculations do not always remain a permanent part of a PivotTable report. For example, it is common to add a calculated field or item temporarily to the PivotTable to test the data or get a number to use elsewhere. Similarly, you may find that you create several versions of a custom calculation and you only want to keep the final version. Finally, although custom calculations are a powerful tool, they cannot do everything, so you may find that a calculation does not provide the answer you seek or help you with your data analysis.

For all these situations, Excel enables you to delete those calculated fields or items that you no longer need.

The following VBA macro deletes all the custom calculations in a PivotTable:

Sub DeleteAllCustomCalculations()
Dim objPF As PivotField, objCF As PivotField
Dim objCI As PivotItem
With ActiveSheet.PivotTables(1)
    For Each objCF In .CalculatedFields
        objCF.Delete
    Next 'objCF
    For Each objPF In .PivotFields
        For Each objCI In objPF.CalculatedItems
            objCI.Delete
        Next 'objCI
    Next 'objPF
End With
End Sub

Delete a Custom Calculation

To delete a calculated item, instead, click PivotTable→Formulas→Calculated Item.

The Insert Calculated Field dialog box appears.

Excel removes the custom calculation.