Search Contextures Sites

Excel Pivot Table Calculated FieldIn a pivot table, you can create calculated fields, using formulas that work with the sum of other pivot fields. There are some limitations to what a calculated field can do, but they let you add more power to your pivot tables.

Video: Add a Simple Calculated Field

Add a Complex Calculated Field

Problem With Calculated Field Totals

Remove a Pivot Table Calculated Field

Programmatically Remove Pivot Table Calculated Field

Create List of Pivot Table Formulas

Video: Create List of Pivot Table Formulas

List All Formulas For All Pivot Tables

Pivot Table Tutorials and Videos

## Pivot Table Calculated Field

In a pivot table, you can create a new field that performs a calculation on the sum of other pivot fields. For example, in the screen shot below, a calculated field, named Bonus, has been created, and it will calculate 3% of the Total, if the sum of Units is greater than 100.

## About Calculated Fields

Here are some of the features and limitations of calculated fields:

## Features

- For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.
- Calculated fields are automatically available in all pivot tables that are based on the same pivot cache
## Limitations

- Calculated field formulas cannot refer to the pivot table totals or subtotals
- Calculated field formulas cannot refer to worksheet cells by address or by name.
- Sum is the only function available for a calculated field.
- Calculated fields are not available in an OLAP-based pivot table.
## Video: Add a Simple Calculated Field

Watch this video to see the steps for creating a simple calculated field. The written instructtions are below the video.

You can download the sample file for this video, in the download section, below.

## Add a Simple Calculated Field

In this example, the pivot table shows the total sales for each sales representative per product, and the Units field summarizes the number of units sold.

You can download the sample file for this video, in the download section, below.

The sales reps will earn a 3 percent bonus on their Total Sales. To show the bonuses, you can add a calculated field to the pivot table. In this example, the formula will multiply the Total field by 3%.

## To add a calculated field:

- Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Options tab (Analyze tab in Excel 2013).
- In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
- Type a name for the calculated field, for example, RepBonus.
- In the Formula box, type
=Total * 3%- Click Add to save the calculated field, and click Close.
- The RepBonus field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List.
## Add a Complex Calculated Field

In this example, the pivot table shows the total sales for each sales representative per product, and the Units field summarizes the number of units sold. We'll create a calculated field that uses these two fields, and the IF function.

The sales reps will earn a 3 percent bonus if they have sold more than 100 units of any product. To show the bonuses, you can add a calculated field to the pivot table. In this example, the formula will test the Units field, to see if more than 100 units were sold, and multiply the Total field by 3%.

You can download the sample file for this tutorial, in the download section, below.

## To add a calculated field:

- Follow the steps above, to open the Calculated Field dialog box
- Type a name for the calculated field, for example, Bonus.
- In the Formula box, type the IFformula that refers to the Units and Total fields:

=IF(Units>100,Total*3%,0)- Click Add to save the calculated field, then click Close.
- The Bonus field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List.
## Problem With Calculated Field Totals

When you create a calculated field, you might expect to see a sum of the calculated amounts, in the pivot table's subtotal and grand total rows. However, the calculated field uses the same calculation in the subtotal and grand total rows, instead of showing a sum.

For example, in the Bonus calculated field, shown above, the total bonus for Andrews is 164.23, instead of 70.13 (23.65+33.83+12.65). This happens because Excel uses the same calculation in the Total and Subtotal rows, instead of summing.

The total Units for Andrews is greater than 100, so the total Bonus is calculated as 3% of Andrews' Total.

You can download the sample file for this tutorial, in the download section, below.

There isn't a pivot table setting that you can change, so it will sum the calculated fields, instead of using the calculated field formula on the totals. However, you could use one of the following workarounds:

## Apply a Filter

To hide the rows that don't qualify for a bonus, filter the product field for Units > 100, to match the Bonus calculation. With the filter applied, the subtotals and Grand Total are correct.

## Hide the Subtotals and Grand Totals

Another workaround is to remove the Subtotals and Grand Totals from the Sales Rep field.

- To turn off the Subtotals, right-click on one of the Sales Rep names, and click Subtotal "Rep", to remove the check mark.
- To turn off the Grand Total, right-click on the Grand Total label, and click Remove Grand Total.
## Calculate Outside of the Pivot Table

If your pivot table layout won't change, another workaround is to calculate the Subtotals and Totals, outside of the pivot table, in columns to the right. For this technique to work correctly, change the pivot table layout from Compact to Outline Form.

In the screen shot below, the bonus is a calculated field, in column P. Formulas have been added in columns Q, R and S, and column Q has conditional formatting, so it matches the pivot table style.

- Formula in cell R12:
=IF(L12="",R11,SUM(R11,1))- Formula in cell S12:
=IF(M12="",0,P12)- Formula in cell Q12:
=IF(L12="Grand Total",SUM(S:S),IF(L12<>"",SUMIF(R:R,R12,S:S),S12))Copy the formulas down to row 22, where the Grand Total is located.

Then, columns P, R and S could be hidden, leaving only the calculated Bonus in column Q.

## Remove a Pivot Table Calculated Field

In this example, the pivot table has a calculated field named Bonus. It appears in the Values area as Sum of Bonus. You could temporarily hide the Bonus calculated field, or permanently delete it from the pivot table.

## Temporarily Remove a Calculated Field

To temporarily remove a calculated field from a pivot table, follow these steps:

- In the pivot table, right-click a cell in the calculated field. In this example, we’ll right-click the Bonus field.
- In the popup menu, click the Remove command that shows the name of the calculated field.

The calculated field is removed from the pivot table layout, but remains in the PivotTable Field List.

Later, you can add a check mark to the calculated field in the PivotTable Field List, to return it to the pivot table layout.

## Permanently Remove a Calculated Field

To permanently remove a calculated field, follow these steps to delete it:

- Select any cell in the pivot table.
- On the Ribbon, under the PivotTable Tools tab, click the Options tab (Analyze tab in Excel 2013).
- In the Tools group, click Formulas, and then click Calculated Field.
- From the Name drop down list, select the name of the calculated field you want to delete.

- Click Delete, and then click OK to close the dialog box.
## Programmatically Remove Pivot Table Calculated Field

In Excel VBA, if you try to change the Orientation for a calculated field, Excel displays the error message "Run-time error '1004': Unable to set the Orientation property of the PivotField class"

You can manually uncheck the calculated field boxes, and remove them from the pivot table, then check the box again, to put it back into the layout. However, if you record code while removing the calculated field, that recorded code shows the same error message when you try to run it.

So, I wrote the following code that deletes each calculated field, then immediately adds it back to the pivot table field list, but not into the pivot table layout. If you've been having the same trouble with calculated fields, I hope this helps!

Sub RemoveCalculatedFields() Dim pt As PivotTable Dim pf As PivotField Dim pfNew As PivotField Dim strSource As String Dim strFormula As String Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.CalculatedFields strSource = pf.SourceName strFormula = pf.Formula pf.Delete Set pfNew = pt.CalculatedFields.Add(strSource, strFormula) Next pf End Sub## Create List of Pivot Table Formulas

With a built-in pivot table command, you can quickly create a list of the calculated fields and calculated items in the selected pivot table.

NOTE: All pivot tables that share the same pivot cache will also share the same calculated fields and calculated items.

## List the Pivot Table Formulas in Excel 2010 and Excel 2013

- Select any cell in the pivot table.
- On the Ribbon, under the PivotTable Tools tab, click the Options tab (Analyze tab in Excel 2013).
- In the Calculations group, click Fields, Items & Sets
- Click List Formulas.

## List the Pivot Table Formulas in Excel 2007

- Select any cell in the pivot table.
- On the Ribbon, under the PivotTable Tools tab, click the Options tab.
- In the Tools group, click Formulas
- Click List Formulas.

A new sheet is inserted in the workbook, with a list of the calculated fields and a list of the calculated items.

## List the Pivot Table Formulas in Excel 2003

- Select any cell in the pivot table.
- On the Pivot toolbar, click PivotTable.
- Click Formulas, then click List Formulas.

A new sheet is inserted in the workbook, with a list of the calculated fields and calculated items (see the Excel 2007 example above).

## Video: Create a List of Pivot Table Formulas

With a built-in command, you can quickly create a list of the calculated fields and calculated items in the selected pivot table. To see the steps, please watch this short video tutorial.

## List All Formulas For All Pivot Tables

To create a list of all the formulas in a specific pivot table, you can use the List Formulas command, as shown above.

There is no built-in command that will list the formulas for all of the pivot tables in a workbook, but you can use programming to do that.

In the sample code shown below, a new worksheet is added to the active workbook, with a list of all the calculated items and calculated fields, in all of the pivot tables.

To download the sample file, which contains the code, go to the Download section, below.

Sub ListAllPivotFormulas() 'www.contextures.com 'print all the pivot table formulas 'in the active workbook Dim lRow As Long Dim wb As Workbook Dim ws As Worksheet Dim wsFP As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim cf As Variant 'calculated field Dim ci As Variant 'calculated item Dim strSh As String Dim lPI As Long On Error Resume Next Application.DisplayAlerts = False Set wb = ActiveWorkbook strSh = "FP_" & Format(Date, "yyyymmdd") On Error Resume Next Worksheets(strSh).Delete On Error GoTo exitHandler Set wsFP = Worksheets.Add With wsFP .Name = strSh .Columns("A:E").NumberFormat = "@" 'text format .Range(.Cells(1, 1), .Cells(1, 7)).Value _ = Array("ID", "Sheet", "PivotTable", _ "Type", "Field", "Name", "Formula") .Rows(1).Font.Bold = True End With lRow = 2 For Each ws In wb.Worksheets If ws.PivotTables.Count > 0 Then For Each pt In ws.PivotTables For Each cf In pt.CalculatedFields wsFP.Range(wsFP.Cells(lRow, 1), _ wsFP.Cells(lRow, 7)).Value _ = Array(lRow - 1, _ ws.Name, pt.Name, _ "Calc Field", , cf.Name, _ " " & cf.Formula) lRow = lRow + 1 Next cf For Each pf In pt.PivotFields On Error Resume Next lPI = 0 lPI = pf.CalculatedItems.Count On Error GoTo errHandler If lPI > 0 Then For Each ci In pf.CalculatedItems wsFP.Range(wsFP.Cells(lRow, 1), _ wsFP.Cells(lRow, 7)).Value _ = Array(lRow - 1, _ ws.Name, pt.Name, _ "Calc Item", pf.Name, _ ci.Name, " " & ci.Formula) lRow = lRow + 1 Next ci End If Next pf Next pt End If Next ws wsFP.Columns("A:G").EntireColumn.AutoFit exitHandler: Application.DisplayAlerts = True Exit Sub errHandler: MsgBox "Could not list formulas" Resume exitHandler End Sub## Download the Sample File

- Sample file for the Simple Calculated field and Complex Calculated Field tutorials: Calculated Field Samples
- Sample file with the code to create a list of all pivot table formulas. The file is zipped, and is in Excel 2007 / 2010 format (xlsm). The file contains macros, so enable them to test the code: Calculated Field List programming sample file
## More Pivot Table Resources

Tutorials:

- FAQs - Pivot Tables
- Pivot Table Introduction
- Grouping Data
- Multiple Consolidation Ranges
- Running Totals
- Summary Functions
- Clear Old Items in Pivot Table
## Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

## Search Contextures

Search Contextures Sites

More Pivot Table Tutorials

Contextures Inc., Copyright ©2015

All rights reserved.

Last updated: March 17, 2015