|
Home > Pivot > Calculation > Calculated Field Excel Pivot Table Calculated FieldIn 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
|
Pivot Table Calculated FieldIn 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.
Working with Calculated FieldsIn 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. -- Problems with Calculated Field Totals -- Change or Remove Calculated Field -- Calculated Field vs Calculated Item -- Create List of Pivot Table Formulas About Calculated FieldsHere are some of the features and limitations of calculated fields: Features
Limitations
Referenced Pivot Fields - SUMIn the pivot table shown below, you can see an example of the Sum function limitation when referring to other pivot fields:
|
Add a Calculated FieldIn 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 FieldWatch 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 FieldIn this example, the pivot table report has two Value fields:
We need to add another column in the Values area, with a special formula:
Note: You can download the sample file for this example, in the download section, below.
|
Insert Calculated Field Dialog BoxThe 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:
New Field in Pivot TableWhen you return to the Excel worksheet, the pivot table has a new field named RepBonus The new field appears in 2 places:
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.
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
|
Add a Complex Calculated FieldIn 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:
|
Calculated Field with RoundingIn 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 ProjectionsIn this pivot table shown below, first quarter sales for each product are summarized
To forecast the second quarter sales, we'll create two calculated fields, based on the number of units sold.
1) Create Pessimistic ProjectionTo create the calculated field for the pessimistic sales forecast, follow these steps:
|
Pessimistic Projection in Pivot TableThe 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%".
The Excel ROUND functionThe new calculated field formula uses the ROUND function, which rounds a number to the closest number, and with a specific number of digits rounded.
The ROUND function has two arguments, and both are required:
Create Optimistic ProjectionNext, to create a calculated field for the optimistic sales forecast, follow these steps:
Optimistic Projection in Pivot TableThe 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%".
The Excel MROUND functionThe new calculated field formula uses the MROUND function, which rounds a number to a specific multiple.
The MROUND function has two arguments, and both are required:
How MROUND Function WorksTo determine if it should round up or down, the MROUND function uses this test:
Is the remainder less than half of the multiple?
Worksheet Example of MROUND FunctionThe worksheet example in the screen shot below shows how that MROUND rounding works.
|
Problem - Calculated Field TotalsWhen 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 FilterTo 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 TotalsAnother workaround is to remove the Subtotals and Grand Totals from the Sales Rep field.
Calculate Outside of the Pivot TableIf 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.
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.
|
Change or Remove Calculated FieldAfter 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. -- Remove Pivot Table Calculated Field -- Macro to Remove Pivot Table Calculated Field Modify a Calculated FieldAfter 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.
Remove a Calculated FieldIn 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 FieldTo temporarily remove a calculated field from a pivot table, follow these steps:
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 FieldTo permanently remove a calculated field, follow these steps to delete it:
|
| 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
|
Calculated Field vs Calculated ItemThis short video shows the steps to create custom formulas in Excel pivot table with calculated fields and calculated items. |
Create a List of Pivot Table FormulasAfter 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 FormulasWith 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 FormulasWith 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
|
List the Pivot Table Formulas in Excel 2007
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
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 TablesTo 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.
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
|
More Tutorials
Calculated Items vs Calculated Fields
Clear Old Items in Pivot Table
Last updated: January 10, 2023 4:00 PM