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.

1. On the Pivot table editor panel, add a “Calculated Field” to Values by clicking the “Add” button against “Values.”
2. Remove the existing formula, i.e., =0 in the formula entry field that appears.
3. Select “Custom” under summarize by.
4. Insert the following XLOOKUP formula.
5. 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.

Pivot Example 1520

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.

1. Reference should point to the source data, not to the Pivot table itself.
2. 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.
3. 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.