Contextures

Home > Pivot > Calculation > Calculated Field

Excel Pivot Table Calculated Field

In a pivot table, you can create calculated fields, using your own custom formulas that work with the sum of other pivot fields. There are limitations to what a calculated field can do, but they add a powerful tool to the pivot tables in your Excel workbook. Also, see the difference between Calculated Items and Calculated Fields

create calculated field with ROUND function

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, using your own formulas.

For example, in the screen shot below, a calculated field -Bonus - calculates 3% of the Total, if more than 100 units were sold.

calculated field for Bonus

Working with Calculated Fields

In the sections below, see how to add, change or remove calculated fields, and understand their features and limitations. There are videos, written steps, and sample files to download.

-- About Calculated Fields

-- Add a Calculated Field

-- Problems with Calculated Field Totals

-- Change or Remove Calculated Field

-- Create List of Pivot Table Formulas

-- Download the Sample Files

About Calculated Fields

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

Features

  • For calculated fields, the individual amounts in the other value 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
  • Calculated fields are not available in an OLAP-based pivot table
  • When referring to other pivot fields in a calculated field, the SUM of their values is always used (see example below). Any other functions or operations in the formula will apply to those SUMs
Referenced Pivot Fields - SUM

In the pivot table shown below, you can see an example of the Sum function limitation when referring to other pivot fields:

  • In the calculated field (PerOrder), the formula is: =Units / Rep
    • The implied formula is SUM(Units) / SUM(Rep)
    • There is no way to change that underlying SUM function
  • For Binder, the sum of Units is 786
  • Rep is a text field, which has a numeric value of zero, so the sum of Rep is zero
    • Calculated field does not use the Rep count, showing in pivot table
  • Result is a #DIV/0! error, because of dividing by zero.

pivot table fields

Add a Calculated Field

In the video and written steps below, see how to add a calculated field in an Excel pivot table. Start with a simple calculation, then see how to create a complex formula.

-- Video: Add a Simple Calculated Field

-- Add a Simple Calculated Field

-- Add a Complex Calculated Field

-- Calculated Field with Rounding

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. To get the sample file for this video, go to the download section, below.

Add a Simple Calculated Field

In this example, the pivot table report has two Value fields:

  • Total column shows total sales dollars for each sales representative, for each product
  • Units field summarizes the number of units sold

We need to add another column in the Values area, with a special formula:

  • Multiply the Total sales in each row by 3%, to calculate the bonus payments

Note: You can download the sample file for this example, in the download section, below.

pivot table fields

Calculate Sales Bonus

To create a special formula in the pivot table, you can add a calculated field. We'll create a calculated field to show how much the sales reps will earn, based on a 3% bonus on their Total Sales.

To calculate a 3% bonus in a normal worksheet cell, you could use this formula, in cell D5:

  • =C5 * 3%

In our calculated field, the formula will be similar, but it will:

  • refer to a field name (Total)
  • instead of a worksheet cell (C5)

To add a calculated field:

To show the bonuses, follow the steps below, to add a calculated field to the pivot table.

  • To start, select any cell in the pivot table
  • Next, on the Excel Ribbon, go to the PivotTable Analyze tab
  • In the Calculations group, click Fields, Items, & Sets
  • Then, in the drop-down menu, click the Calculated Field command

Excel Ribbon PivotTable Analyze tab Calculations Fields Items Sets Calculated Field

Insert Calculated Field Dialog Box

The Insert Calculated Field dialog box opens, where you can create a new calculated field, or make changes to existing calculated fields

To create the new pivot table calculated field, follow these steps:

  • First, type a unique name for the calculated field - in this example, the new calculated field is named, RepBonus
    • A pivot table cannot have duplicate calculated field names
    • Warning: If you enter the name of an existing calculated field, Excel will replace that calculated field's formula with the new formula that you enter
  • Press the Tab key on your keyboard, to move to the Formula box
  • In the Formula box, type the bonus calculation formula: =Total * 3%
    • Tip: Instead of typing field names, you can double-click on them in the Fields list, below the Formula box
  • Next, click the Add button to save the calculated field
    • The new calculated field is added to the Fields list in the dialog box, below the existing fields
  • Then, click the Close button, to close the dialog box, and return to the worksheet.

insert calculated field

New Field in Pivot Table

When you return to the Excel worksheet, the pivot table has a new field named RepBonus

The new field appears in 2 places:

  1. In the Values area of the pivot table worksheet layout, to the right of the other fields
  2. At the bottom of the check box list of fields, in the PivotTable Field List.

calculated field in pivot table

Change Field Caption (Optional)

In the pivot table layout, the new field has "Sum of" at the start of its caption.

To make the caption shorter, you can type a different caption in the heading cell.

  • Note: The caption cannot be exactly the same as the pivot table field's name.

In the screen shot below, I selected cell D4

Then, to replace the long caption, I typed "RepBonus " -- there is a space character at the end of that caption

calculated field in pivot table with caption change

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:

  1. Follow the steps above, to open the Insert Calculated Field dialog box
  2. Type a name for the calculated field, for example, Bonus.
  3. In the Formula box, type the IF formula that refers to the Units and Total fields:
    =IF(Units>100,Total*3%,0)
  4. Click Add to save the calculated field, then click Close.

    calculated field

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

    complex Calculated Field

Calculated Field with Rounding

In addition to the IF function, as shown in the previous example, you can use other Excel functions to create Calculated Field formulas.

In this example, see how to use Excel rounding functions - ROUND or MROUND - in a calculated field, to project product sales for the upcoming quarter.

Note: You can get the sample file for this example -- it's file #1 in the download section, below

Pivot Table for Sales Projections

In this pivot table shown below, first quarter sales for each product are summarized

first quarter sales in pivot table

To forecast the second quarter sales, we'll create two calculated fields, based on the number of units sold.

  1. Dn03 - a pessimistic projection - sales will go down 3%
  2. Up05 - an optimistic projection - sales will go up 5%

1) Create Pessimistic Projection

To create the calculated field for the pessimistic sales forecast, follow these steps:

  • First, select a cell in the pivot table, and follow the steps above, to open the Insert Calculated Field dialog box
  • Type a name for the new calculated field, Dn03
  • In the Formula box, type the following formula (see notes below, on how this function works):
    • =ROUND(Units * 0.97, 0)
  • Click Add to save the new calculated field
  • Next, click the Close button, to go back to the worksheet.

create calculated field with ROUND function

Pessimistic Projection in Pivot Table

The new calculated field appears in the pivot table, to the right of the existing value field.

Its default heading was Sum of Dn03, and I changed the heading to "Dn 3%".

create calculated field with ROUND function

The Excel ROUND function

The new calculated field formula uses the ROUND function, which rounds a number to the closest number, and with a specific number of digits rounded.

  • =ROUND(Units * 0.97, 0)

The ROUND function has two arguments, and both are required:

  • number - the value to be rounded (Units * 0.97)
  • num_digits - the number of digits to which the value should be rounded, to the left or right of the decimal point (0)

Create Optimistic Projection

Next, to create a calculated field for the optimistic sales forecast, follow these steps:

  • First, select a cell in the pivot table, and follow the steps above, to open the Insert Calculated Field dialog box
  • Type a name for the new calculated field, Up05
  • In the Formula box, type the following formula (see notes below, on how this function works):
    • =MROUND(Units * 1.05, 5)
  • Click Add to save the new calculated field
  • Next, click the Close button, to go back to the worksheet.

create calculated field with rounding

Optimistic Projection in Pivot Table

The new calculated field appears in the pivot table, to the right of the existing value fields.

Its default heading was Sum of Up05, and I changed the heading to "Up 5%".

create calculated field with rounding

The Excel MROUND function

The new calculated field formula uses the MROUND function, which rounds a number to a specific multiple.

  • =MROUND(Units * 1.05, 5)

The MROUND function has two arguments, and both are required:

  • number - the value to be rounded (Units * 1.05)
  • multiple - the multiple to which the value should be rounded (5)

How MROUND Function Works

To determine if it should round up or down, the MROUND function uses this test:

  • divide the number by the multiple
  • find the remainder of that division
  • divide the multiple in half

Is the remainder less than half of the multiple?

  • YES -round down, toward zero
  • NO - round up, away from zero

Worksheet Example of MROUND Function

The worksheet example in the screen shot below shows how that MROUND rounding works.

  • D2: remainder from 126/5 is 1, less than 2.5, round down
  • D3: remainder from 133.4/5 is 3.4, greater than 2.5, round up

create calculated field with rounding

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

complex Calculated Field

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.

filter product field

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.

hide pivot field subtotals

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.

calculate totals in adjacent column

Change or Remove Calculated Field

After you create a calculated field, you might need to change its formula. Or, you might want to remove a calculated field, if you no longer need it in the pivot table.

-- Modify Calculated Field

-- Remove Pivot Table Calculated Field

-- Macro to Remove Pivot Table Calculated Field

Modify a Calculated Field

After you create a calculated field, you might need to change its formula. In this example, we'll change the Bonus percentage for the calculated field formula that was created in the previous section.

Watch the video to see the steps, and the written instructions are below the video. To get the sample file for this video, go to the download section, below.

  1. Follow the steps above, to open the Calculated Field dialog box
  2. Click the arrow in the Name box, and select the calculated field that you want to modify -- Bonus in this example.
  3. select a Calculated Field

  4. In the Formula box, make changes to the formula -- in this example, the percentage was changed from 3% to 4%:
    =IF(Units>100,Total*4%,0)
  5. Click the Modify button, to save the calculated field changes, then click Close.
  6. modify a Calculated Field

Remove a 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. go to top

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 (Analyze tab in Excel 2013).
  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.
  5. delete calculated field

  6. Click Delete, and then click OK to close the dialog box.
  7. go to top

Macro to Remove 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.

Watch this video to see a macro that will remove all the calculated fields, without showing an error message. The download link and sample code are below the video.

The following code removes all the calculated fields by changing the Visible property to False. You can download the sample file below, to test the code. Thanks to Rory Archibald, from Excel Matters, for suggesting this solution.

You must have a pivot table cell selected, and only that pivot table is affected.

Sub RemoveALLCalculatedFields()
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
On Error Resume Next
Set pt = ActiveCell.PivotTable

If pt Is Nothing Then
  MsgBox "Select a pivot table cell"
  Exit Sub
End If

For Each pf In pt.CalculatedFields
    For Each df In pt.DataFields
      If df.SourceName = pf.Name Then
        With df
          .Parent.PivotItems(.Name) _
            .Visible = False
        End With
        Exit For
      End If
    Next df
Next pf

End Sub

Create a List of Pivot Table Formulas

After you add calculated fields or calculated items in a pivot table, you can create a quick list of all the formulas in those calculations.

-- Create List of Pivot Table Formulas

-- Macro Lists All Formulas For All Pivot Tables

Video: Create a Quick 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. Watch this short video to see the steps, and the written instructions are below the video.

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

  1. Select any cell in the pivot table.
  2. On the Ribbon, under the PivotTable Tools tab, click the Options tab (Analyze tab in Excel 2013).
  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

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

Macro Lists 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 a macro 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.

formula list

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 Files

  1. Calculated Field Examples: Sample file for the Simple Calculated field and Complex Calculated Field tutorials: Calculated Field Samples
  2. Formula List Macro: Sample file with the code to create a list of all pivot table formulas. The file is zipped, and is in xlsm format. The file contains macros, so enable them to test the code.
  3. Remove CF Macro: Sample file with the code to remove all Calculated Fields. The file is zipped, and is in xlsm format. The file contains macros, so enable them to test the code:

More Tutorials

Calculated Items vs Calculated Fields

Calculated Field - Count

Calculated Items

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

 

Last updated: October 31, 2022 10:05 AM