Google Sheets allows you to build pivot tables to summarize large data sets. 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, 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 completed your Pivot Table and know how to use it. See this video if you need some basic help on Pivot Tables.
If you need a primer on Pivot Tables, this video will walk you through them.
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 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 given to you. Further, it is easier to calculate the average after summarizing the data. It is the average of the summarized data that you are after.
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.