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.
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.
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.
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.
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.
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
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.
