Do you know How to Use Calculated Field in Pivot Table‼️Excel Amazing Trick #excel #exceltips, by Short and Clear Excel on YouTube. 1 min 26 s.
- Video length
- 1 min 26 s
- Time to do it
- 10 minutes
- Difficulty
- Easy
- Steps
- 8
What you need
Steps
Click any cell inside your pivot table so the PivotTable Analyze and Design tabs appear on the ribbon.
On the PivotTable Analyze tab, open the Calculations group and click Fields, Items & Sets, then choose Calculated Field.
In the Name box, type a clear label for the new column, such as Profit or Average Sale.
Click in the Formula box, delete the zero, and type your formula using field names from the list, for example =Sales-Cost.
Insert field names by double-clicking them in the Fields list so Excel spells them exactly right.
Click Add, then click OK to place the calculated field in the pivot table Values area.
Drag the new field into the Values area if it does not appear where you want it, and check the number format.
Refresh the pivot table after changing source data so the calculated field recalculates.
Tips and common mistakes
Calculated fields work on sums of data, so row-by-row results can differ from a formula in a normal column.
Use exact field names from the list, since typing them by hand can break the formula.
Calculated fields cannot refer to other calculated fields, so build each one from the source fields.
Questions
Where is the Calculated Field button in Excel?
It sits under PivotTable Analyze, then Fields, Items & Sets, then Calculated Field. You must click inside the pivot table first or the tab will not show.
Can I edit or delete a calculated field later?
Yes. Open the same Calculated Field dialog, pick the field from the Name drop-down, then change the formula or click Delete.
Does a calculated field work in Google Sheets?
Google Sheets pivot tables do not have this exact option. You can add a formula column to the source data and then include it in the pivot table.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
