You might have seen the option to add a calculated field in the Values field of the Pivot table in Google Sheets. Do you know how to use it?
In this Google Sheets tutorial, I’ll explain when and where to use this Pivot table feature in Google Sheets.
For example, you can summarize the sales value of any specific items in a year by quarter, month, or both.
Related: Group Dates in Pivot Table in Google Sheets (Month, Quarter, and Year).
Further, you can break up this summary by area, for example, the sales value of laptops for January from the south zone, north zone, etc.
Likewise, you can find which item sold the most in any specific month.
Usually, you do not require any formula to summarise or group data in the Pivot table because it has functions such as Sum, Count, Max, Min, etc., to select from.
For basic types of data manipulation, it’s enough. But you can use custom formulas too in the Pivot table to supercharge it.
You can add a calculated field to the Values field and enter your custom formula and that adds a new calculated field column within the Pivot table. Please find those details below.
How to Add a Calculated Field in Pivot Table in Google Sheets
Here is a sample dataset.
It’s a very basic dataset that can help you understand how to add a calculated field to the Pivot table in Google Sheets.
1. Preparing a Pivot Table
In this sample data, I can group the first two columns and they are date (column A) and material name (column B).
Related: Drill Down in Pivot Table in Google Sheets (Date Field).
For example, if I group column B, there would be two items: Gravel and Sand. I’m grouping this column in my example below.
Here is how to group it.
First, select the data range A3:D9. Then go to the Insert menu Pivot table (earlier it was within the Data menu).
Select whether you want the report in the same sheet or a new sheet. I’m selecting “New sheet.” Click “Create.”
It will open the Pivot table editor panel. Let’s group the field “material name.”
How do we do that?
Click the “Add” button against “Rows” and select the field “material name” for grouping the items Sand and Gravel.
How do we add a column that contains the total quantity (number of units) of these two grouped items in the pivot table report?
It’s similar to adding the “material name.”
Click the “Add” button against “Values” and select the field “number of units.” Don’t forget to choose the aggregation function SUM under “Summarize by.”
You are closer to adding a calculated field in this Pivot table report.
2. Adding a Calculated Field for Getting the Most Recent Price Per Unit
I’ve grouped the “material name” in the report above. So I have the unique material names and their SUM of “number of units.”
Now what I want is the “price per unit” against it. Here there is an issue.
The prices of Gravel and Sand are different on different dates.
I don’t want the max, min, or average “price per unit” added to the grouped item. Instead, what I want is the most recent price per unit.
We can add a calculated field to the Pivot table for that purpose in Google Sheets. Here is how.
Please note that my source data range A3:D9 is sorted based on the date column in ascending order.
It’s a prerequisite for the type of formula that I’m adding to the pivot table calculated field.
It will also work in a data set sorted based on the date column in descending order. But that formula will be different. I’ll provide that too later.
- On the Pivot table editor panel, add a “Calculated Field” to Values by clicking the “Add” button against “Values.”
- Remove the existing formula, i.e.,
=0in the formula entry field that appears. - Select “Custom” under summarize by.
- Insert the following XLOOKUP formula.
- Click outside the formula field to activate it, and voila!
=xlookup('material name','material name','price per unit',,0,-1)We have added a calculated field in the Pivot table in Google Sheets.
Please note, each change in the source data will also reflect in the calculated field column.
Other than the “material name,” we have a “SUM of the number of units” (quantity) column and a “Calculated Field 1” (recent price per unit) column in the Pivot table. Please see the image above.
How do we get the amount, I mean quantity * recent price per unit (“SUM of the number of units” * “Calculated Field 1”)?
Add a second calculated field and insert the following custom formula in that.
=sum('number of units')*xlookup('material name','material name','price per unit',,0,-1)
Please check the result in my sample Sheet below.
Note:- If you have data sorted from highest to lowest dates, you should replace -1 in the last part of both custom formulas with 1.
Things to Remember When Coding the Formula
Here are the key points one should note while coding a formula for the calculated field in Google Sheets.
- Reference should point to the source data, not to the Pivot table itself.
- Use field labels in the source data instead of range references. For example, I’ve used ‘price per unit’ instead of the range D4:D9 in my formulas.
- Field labels in the formula must be enclosed within single quotes.
Renaming Calculated Fields in Google Sheets
Do you know how to rename a calculated field in Google Sheets?
You can’t do it within the Pivot table editor panel. Then?
Go to the cell containing the label and overwrite it with the name you want. For example, go to cell C1 and enter “Price.”
That’s all. Thanks for the stay. Enjoy!
Related: SUMIF in Pivot Table Calculated Fields in Google Sheets.
Is there a way to go about calculating the difference between to values columns in the pivot table?
I’ve tried something like =b4-d4, but the results (same number) apply to all cells in calculated field 1.
I want it to be unique and reflected across all rows. What am I doing wrong here? Thanks
Hi, Grant,
That might be easy by adding an extra column to your source data.
Feel free to share a sample sheet URL in the comment below.
Hi, All,
I am trying to hide the Pivot Table Editor. So that I can view my Pivot Table. Any tips for this?
Hi, Anushma Datt,
You just click outside the Pivot Table output range. It will hide the editor. There is no other way, at least for now!
Thanx. Sumproduct worked.
=sumproduct(Nur_type="Plants",Num_plants)I was able to get the desired output following formula
=sum(arrayformula(if(nur_type="Plants",Num_plants,0)))But it was too clumsy. Sumproduct is sleek.
Thanx once again.
I have noted that Sumif or Sumifs do not work in calculated field in the pivot table while Countif/Countifs work without any issue. I think there is some bug.
Can you please check and confirm.
Hi, S K Srivastava,
That’s possibly due to the last argument, i.e.
sum_range.It may not be a bug associated with the Pivot Table. It’s most probably associated with the capability of the Sumif function.
In your spreadsheet also, you will see the same issue with Sumif in some cases like when you are trying to use an expression as the
sum_range.You may better use Sumproduct.
For example, this Sumif can be replaced by;
=sumif(zone,"north",qty)the below Sumproduct.
=sumproduct(zone="north",qty)Best,
I notice that the ‘Grand Total’ for each of the ‘Calculated Field’ columns are incorrect in your examples.
I’ve found this happens with my Calculated Field’s inside of pivot tables when I select ‘summarize by’ “custom”. Is this a bug in sheets, or is there some logic to it?
For me, sometimes the Grand Total looks like it just selects at random one of the results in the column above. Other times I’ve seen results that I can seem to explain.
Did a little digging. It turns out that the ‘grand total’ row doesn’t always sum the values above it, but instead applies the function to all of the data in question. It’s a poor choice of wording.
I was trying to get a sum of just the unique values, so I ended up creating a calculated field with the formula
=sum(UNIQUE ('field name'))Hi, Joshua Seal,
The total is actually the multiplication of the values from the grand total row itself. Not the sum of the column.
When using ‘Calculated Fields’ in Pivot Table reports disable the ‘Grand Total’ under row grouping within the editor. I think that would be better to avoid confusion.
I’ve just included my sample pivot table sheet within the post (in the last part).
Hello, This is great stuff. Thanks for sharing. Using the name of source field can be a little confusing instead of being able to just simply click on a cell. Wish it was that simple. With that said I have a question about calculated fields formula.
If I am trying to calculate impressions by grand total impressions (shown on the table) how do I go about doing so? For instance, =sum(Impr.)/1108. It works if I do use a specific number but how do I use contextual formulas to calculate this?
Thanks for your help.
Is there a possible answer to this?