raw table of salaries

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.

This tutorial assumes that you you have already completed your Pivot Table and you have a basic knowledge of how to use them. See this video if you need some basic help on Pivot Tables or take a deep-dive in our comprehensive training course.

raw table of salaries
Raw table of salaries

pivoted salaries with calculated field
Pivoted salaries with calculated field

Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you'll like that.

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

Learn to Summarize Data in Seconds

Contains 10 videos, LINKED GOOGLE SHEETS for you to copy and use, quizzes, and built-in notes. You'll have lifetime access to this course.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>