pivoted salaries with calculated field

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 as shown in this live Google Sheet. 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 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 which includes full access to all of the spreadsheet files used including source data and Pivot Tables.

If you need a primer on Pivot Tables, this video will walk you through them.

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

Watch the video

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

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 present the data however you would like without changing the original data that was given to you. Further, it is easier to calculate the average after summarizing the data. 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 using 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.

 Live examples in Sheets

Go to this spreadsheet for an example of a pivot table with a calculated field that you can study and use anywhere you would like.

Leave a Reply

Your email address will not be published.

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>