Pivot tables are a great way to summarize a large amount of data, without complex formulas. But if you need to, you can create formulas within a pivot table, with calculated fields and calculated items. After you create one, here’s how to change a pivot table calculated field formula.
Pivot Table Calculations
If you’re not sure which type of formula to use, see my explanation of pivot table calculated items and calculated fields.
Here’s the key difference between them:
- Calculated Fields are formulas that can refer to other fields in the pivot table.
- Calculated Items are formulas that can refer to other items within a specific pivot field.
Create a Simple Calculated Field
If you haven’t set up a Calculated Field before, this short video shows the steps. The formula calculates a bonus for each Sales Rep, based on 3% of their total sales.
=Total * 3%
Change a Calculated Field Formula
After you create a calculated field, you can change the formula later, if necessary. For example, the simple formula, shown above, gave everyone a bonus, no matter how many units they sold.
You could change that formula, so it checks the total number of units sold for each product. Then, if more than 100 were sold, a bonus of 4% is calculated. Otherwise, the bonus is zero.
=IF(Units>100,Total * 4%,0)
Video: Change the Formula
This video shows how to change a calculated field formula, and the written steps are below the video.
Steps to Change the Formula
Here are the steps to change a calculated field formula:
- Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Analyze tab
- In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
- Click the arrow in the Name box, and select the calculated field that you want to modify — Bonus in this example.
- In the Formula box, make changes to the formula — in this example, the percentage was changed from 3% to 4%, and we added an IF function:
=IF(Units>100,Total*4%,0) - Click Modify to save the calculated field changes, then click Close.
Video: List All Pivot Table Formulas
Before you make changes to a calculated field, you can create a list of all the existing formulas in the pivot table. I like to do this if I’m making multiple changes, or major revisions.
This video shows the steps, and there are written steps below the video.
How to List All Pivot Table Formulas
To list a pivot table’s calculated fields and calculated items details, follow these steps:
- Select any cell in the pivot table.
- On the Ribbon, under the PivotTable Tools tab, click the Analyze tab
- In the Calculations group, click Fields, Items & Sets
- Click List Formulas.
List of Formulas
After you click the List Formulas command, a new sheet is added to your workbook, with a list of the calculated fields and calculated items.
The list shows the Solve Order, field or item name, and the formula.
More on Calculated Fields
Go to my Contextures website, to get the sample file for this example, and more information on pivot table calculated fields.
And if you use pivot tables, get my free pivot table add-in too!
__________________________
Change a Pivot Table Calculated Field Formula
__________________________