Search Contextures Sites

 

Contextures
Excel news
by email

We respect your email privacy

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

 

Learn how to create Excel dashboards.

Excel Pivot Table Calculated Field

Pivot Table Calculated Field
Video: Add a Simple Calculated Field
Add a Simple Calculated Field
Add a Complex Calculated Field
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
Download the Sample File

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.

calculated field

About Calculated Fields

  • For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.
  • 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.

Click here to download the sample file for this video: Simple Calculated Field

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.

Click here to download the sample file for this tutorial: Simple Calculated Field

pivot table fields

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:

  1. Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Options tab.
  2. In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.

    Ribbon Calculated Field

  3. Type a name for the calculated field, for example, RepBonus.
  4. In the Formula box, type =Total * 3%
  5. Click Add to save the calculated field, and click Close.

    6. insert calculated field

  6. The RepBonus field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List.

calculated field in pivot table

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.

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

To add a calculated field:

  1. Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Options tab.
  2. In the Tools group, click Formulas, and then click Calculated Field.

    Ribbon Calculated Field

  3. Type a name for the calculated field, for example, Bonus.
  4. In the Formula box, type =IF(Units>100,Total*3%,0).
  5. Click Add to save the calculated field, and click Close. The Bonus field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List.

calculated field

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.

calculated field

Temporarily Remove a Calculated Field

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

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

    remove 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:

  1. Select any cell in the pivot table.
  2. On the Ribbon, under the PivotTable Tools tab,  click the Options tab.
  3. In the Tools group, click Formulas, and then click Calculated Field.
  4. From the Name drop down list, select the name of the calculated field you want to delete.

    delete calculated field
     

  5. 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"

calculated field error

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.

List the Pivot Table Formulas in Excel 2010 and Excel 2013

  1. Select any cell in the pivot table.
  2. On the Ribbon, under the PivotTable Tools tab, click the Options tab.
  3. In the Calculations group, click Fields, Items & Sets
  4. Click  List Formulas.

    List Formulas

List the Pivot Table Formulas in Excel 2007

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

    List Formulas

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

Pivot Table Formulas

List the Pivot Table Formulas in Excel 2003

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

    List Formulas 2003

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

You can download the Calculated Field sample file which has pivot tables with a calculated field and calculated items, and 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 macro.

 

Pivot Table Tutorials

Pivot Table Introduction 
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007 
Custom Calculations 
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Field Settings
Filter Source Data  
Filters, Top 10 
Filters, Report Filters
GetPivotData Function
Grand Totals
Grouping Data
Layout, Excel 2007
Multiple Consolidation Ranges
Pivot Cache   
PivotTable Style
Printing a Pivot Table   
Protection  
Running Totals  
Show and Hide Items 
Sorting
Subtotals 
Summary Functions
Unique Item Count

Pivot Table Books

Beginning Pivot Tables (Excel 2007) 
Pivot Tables Recipe Book (Excel 2003) 
Pivot Tables Recipe Book (Excel 2007) 

Pivot Table Add-Ins

Pivot Power 
Pivot Play PLUS 

Pivot Table Videos

Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
GetPivotData
Group Data
Layout, Excel 2007
Report Filters, Add
Running Totals
Select Sections
Subtotals, Create Multiple
Top 10 Filters

 

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: May 19, 2013