Google Sheets – Calculated Fields in Pivot Tables

Google Sheets allows you to build pivot tables to summarize large sets of data. When building the pivot tables, you can also add fields that perform calculations on the data once it is in the pivot tables. These calculated fields are a must-have in certain situations as you may want to add/subtract/multiply/etc summarized data from the pivot table that doesn’t exist in the original data being pivoted. For example, if you have a table of salaries and years of college each employee attended college, you may want to calculate the return for each year of college. To do this, you would first summarize the data by average salary for each group, then perform the division to arrive at the average after the data is summarized.

raw table of salaries
Raw table of salaries
pivoted salaries with calculated field
Pivoted salaries with calculated field

Video explanation

The “Salary per year of college” column above is a Calculated Field that is the quotient of the first and second column as seen in the Pivot Table parameters below which can be seen on the right hand side of your browser screen when you select a field inside the Pivot Table.

pivot table parameters
Pivot table parameters used to create this Pivot Table

These pivot table calculated fields can allow you to leave the original data in its raw untouched form. Then you can use the pivot table to do your own work however you would like without changing the original data that was given to you. Further, it is simpler to calculate the above average after summarizing the data as it is really the average of the summarized data that you are after.

In order to insert a calculated field, you should first build your pivot table. Then, once you have the data pivoted, you can insert the calculated field through the options on the right side of the screen. As of the date of this writing, this can only be done on the desktop browser version of Sheets and not through the mobile apps.

Follow image below for the live Google Sheet with this data

docs share icon