Pivot tables are a powerful way to work with large sets of data in Google Sheets.
You can use Pivot tables to group together and summarize data in your spreadsheet in a number of ways.
Pivot tables offer a great amount of flexibility for analyzing data. One such feature that helps you analyze data when working with Pivot tables is the option to add calculated fields.
Calculated fields allow you to create your own custom formula in a Pivot table. This is an incredibly useful feature.
In this tutorial, I will show you how to add a calculated field to a Pivot table in Google Sheets.
Table of Contents
What is a Calculated Field?
Calculate fields are custom formulas that you can use to summarize and analyze data in a Pivot table. Pivot tables offer a great number of metrics you can use to analyze data in your spreadsheet, but sometimes you may want to add a custom formula if you need to summarize a certain metric.
This is where calculated fields come in. Using a calculated field offers you a great amount of flexibility that allows you to get the data you need from your spreadsheet.
How to Add a Calculated Field to a Pivot Table in Google Sheets
Setting Up your Pivot Table
To learn how to use calculated fields, first, we need to create a Pivot table. I will be creating a Pivot table with this example data:
To create a Pivot table:
1. Highlight the range of data that will be used to create your Pivot table
2. In the top menu select Data
3. Then in the drop-down select Pivot table
4. The Pivot table dialog box will open up. You can either select to create a Pivot table in a new sheet or an existing sheet. In this example, I have selected to create the table on a new sheet. Hit the green Create button when you are done
Your Pivot table will now be created. It should look something like this:
Next, we need to start filling out our Pivot table depending on what data we want to look at. That is done over on the Pivot table editor on the right-hand side:
First I will add a row to my pivot table to look at data by Item by select Row and then choosing the Item header
Then I will go to the Values section and add Sales so that I can look at the total sales per item in my Pivot table. So my data now looks like this:
Adding a Calculated Field to the Pivot Table
Now that our Pivot table is set up, we can begin to add a calculated field to the table. In this example, let’s say that I want to get an idea of how much revenue each item produced in total.
Since there is not a revenue column in my original dataset, I will have to create a calculated field that will multiply Sales*Sales Price so that I can view the revenue by item.
Here is how this is done:
1. In the Pivot table editor navigate to the values section and select Add>Calculated Field
2. Under Summarize by select Custom. If we leave it on SUM the formula will not work correctly
3. Next we need to add our Formula into the Calculated Field formula area. My formula in this example is =SUM(Sales)*AVERAGE(‘Sales Price’). I am doing a SUM of the sales because I want the total sales of each item. For Sales Price, we have to do an AVERAGE if Sales Price, otherwise the pivot table will only take the very first sales price in the pivot table for the calculation, and since the Item’s in my example have different sales prices, this would not be as accurate. Another important note is that since the “Sales Price” column is separated by a space, I have to use single quotation marks around “Sales Price” for it to work correctly.
4. After you have added your custom formula to the calculated field area you should see the calculation showing up in your data now
Again, if you have a header that has a space in it like “Sales Price” in any formula that you use in your calculated field you have to put the header inside single quotation marks for the formula to work correctly.
Closing Thoughts
I have now walked you through creating a Pivot table and adding a Calculated field to the pivot table. This tutorial is just meant to be an introduction to using calculated fields.
Hopefully, you can see how they can be useful for summarizing and analyzing large data sets by giving you the ability to add custom formulas to your data to make quick calculations.
I will definitely be covering more on Pivot tables in a different post in the future.
To really understand how to use Calculated fields we recommend that you follow along with this tutorial and add one into your own spreadsheet so that you can really get this down.
More Google Sheets Tutorials:
How to Calculate Percentage
How to Use Slicers