Add a Calculated Field in a Pivot Table in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on March 31, 2023

This tutorial demonstrates how to add a calculated field in a pivot table in Excel and Google Sheets.


calcfield intro


Pivot tables make viewing and analyzing large amounts of data easy. For necessary calculations in your analysis, you can always add a column to your source data and include it as a pivot table field. But you can also calculate new fields right in the pivot table, without making any edits to your source data.

Tip: Try using some shortcuts when you’re working with pivot tables.

Add Pivot Table Calculated Field

Say you have a pivot table created from the data table below.


calcfield data


  1. The pivot table is set up with the Product field as the filter, Salesperson as the rows, and the Total as the values.


calcfield pivot


  1. Now, add a calculated field that gives each salesperson a 10% bonus based on the total.
    In the Ribbon, go to PivotTable Analyze > Calculations > Fields, Items & Sets > Calculated Field…


calcfield ribbon


  1. Type in a Name for the field, and then in the Formula box, type in your custom formula. Click Add to add your field to the Fields list below. Then, click OK to add the field to the pivot table.


calcfield insert field


The additional field is added as the last column in this pivot table.


calcfield show field


  1. Your formula can be as simple or as complicated as necessary. For example, say you want to give a 10% bonus for sales over $5,000 but only a 5% bonus otherwise. Modify the Bonus formula to do this with an IF statement.
  1. In the Insert Calculated Field dialog box, choose the calculated field (here, Bonus) from the Name drop down, and then edit the formula accordingly.


calcfield if formula


  1. Click Modify and then OK to update your pivot table.


calcfield modified pt


Calculated Pivot Table Field in Google Sheets

You can also add a calculated field as an additional column when you have a pivot table in Google Sheets.

  1. Click in your pivot table to show the pivot table editor pane on the right side of your screen. If the pane does not show, click the Edit button below your pivot table to make the pane visible.


calcfield gs edit button


  1. In the Pivot table editor, click the Add button in the values section, and then click Calculated Field.


calcfield gs select calculated


  1. Type the calculation in the Formula box. The pivot table updates automatically.


calcfield gs value add formula


  1. You can, optionally, rename the column for the calculated field. Just type the new name in the relevant cell in the pivot table.


calcfield gs rename calcfield

AI Formula Generator

Try for Free

See all How-To Articles