In this article:

Add a Calculated Field to a Pivot Table in Google Sheets (2024)

In this article we will show how to add a calculated field to a pivot table in google sheets. Simply follow the steps below.

How to Add a Calculated Field to a Google Sheets Pivot Table

For the purpose of the below demonstration an example sheet was created, access this by clicking the link below and follow along:


Click here to see the example spreadsheet.

1. Highlight the Data Range and from the File Menu Select “Insert” > “Pivot table” 

Select the data range to be implemented in the pivot table. In this example we will highlight cells A1 to C7.

In the file menu, select Insert and then from the dropdown menu select Pivot table. 

Google Sheets pivot table calculated field: Generating a pivot table 

2 Choose To Insert in a New or Existing Sheet


From the menu that appears choose to insert your pivot table into a new sheet or an existing sheet.

For the existing sheet you will be prompted to enter the cell reference where you want the table to appear.


In our example we will select cell E2 to create our table.

Click Create to generate the pivot table.

Google Sheets pivot table calculated field

3. In the sidebar menu select “Rows” and then select data range 

 

A sidebar menu will appear showing various parameters to select for populating the pivot table. 

Click the Rows subsection and from the dropdown menu select a data range from the options available.

In our example below, the available data sets are visible by the header title that has been included in the selection.  

We will select the data in column A titled Names.

Google Sheets pivot table calculated field percentage of total: Editing the pivot table ‍

4. In the sidebar menu select “Values” and then “Calculated Field”

The first selected data range will re-format the pivot table. In the side menu click the Values subsection and from the dropdown menu select Calculated Field.

Add calculated field to pivot table Google Sheets: Selecting data sets‍

5. Enter formula in the “Values” subsection in the “Pivot table editor” 

The calculated Field range in the pivot table will automatically populate a value of 0 to all corresponding data in the ‘Rows’ column.

Pivot table Google Sheets calculated field: Entering a formula ‍

6. Enter the formula =’Cell_Ref1’ Operator ’Cell_Ref2’ in the Formula text box in “Values”

In the Formula text box, enter the additional data sets to be used in conjunction with the values in the Rows column in the following format:

=‘Cell_Ref1’Operator‘Cell_Ref2’

Formula Breakdown:

=’Cell_Ref1’: The first set of cells to be used in the formula, pre-selected in the first few steps. I our example this is the data in column A called Sales 1

Operator: The operator depends on what comparison or calculation needs to be made. In this case the operator is a plus (+) to add two datasets together.

‘Cell_Ref2’: The second set of data that the formula that will add together. In our example this will be the data in column B called Sales 2.

In the example below, we have used the formula to add the values from the sales column together for each name.

='Sales 1'+'Sales 2’

7. See populated results generated in the pivot table

 

The results will now be populated into the pivot table

As can be seen in the example below, the calculation will automatically generate the inputted results including a grand total of all results 

Google Sheets calculated field pivot table: Formula generated results

We hope this article has helped you and given you a better understanding of how to add a calculated field to a pivot table in Google Sheets. You might also like our articles on how to refresh a pivot table and how to sort a pivot table in Google Sheets. We also recommend reading our guide on how to hyperlink an email address in Google Sheets.

Check out our software for timesheet approval.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started