October 14, 2020

How to Add Custom Formulas in a Google Sheets Pivot Table

Google Sheet spreadsheet
SECTIONS
  1. Adding custom formulas in the source sheet first
  2. Adding custom formulas in the Pivot Table directly

The Pivot Table is Google Sheets’ answer to Microsoft Excel’s PivotTable feature. This feature allows the user to quickly summarize a large amount of structured data through few clicks, giving the user a powerful tool for free. Pivot Table can be a bit more challenging to tame, but this tutorial will help you master it by considering the common demands of data analysis. 

The Pivot Table contains a lot of predefined functions you can use in summarizing your data into meaningful information. However, you have to sometimes apply a certain formula to it that is not predefined in the Pivot Table. How can you do so? There are two ways, one involves adding extra columns in the data sheets while the other does not. We will tackle both ways here.

Adding custom formulas in the source sheet first

What is the advantage of adding the custom formula in the source sheet first? It lets you keep the data for individual entries intact and accessible. There are times where you have to keep the individual data for later auditing. You won’t be able to do that if you go directly to the Pivot Table.

For our example, we need to calculate the profit from each sale. To calculate the profit from each sale, we use the following formula:

Profit = Sales - Cost

We need to calculate this to make accurate budgeting. Here, we have the sheet we're working with:

Original sheet. Contains item, price, amount, sales, cost, and total cost. 
It includes everything we need to calculate profit but no profit!


To do so, follow the following steps:

Step 1: Go to the individual sheet, and add a column that will contain the formula.

Original sheet. Contains item, price, amount, sales, cost, and total cost. The label profit added to the next column.
The label profit is now added to the next column.


Step 2: Apply the formula. For our example, the formula for cell H2 will look like this:

=E2-G2

Google Sheets will automatically calculate it. To apply it to all the cells, one quick technique is to click on the small square box on the lower-right corner of the highlight on the cell where you entered your formula and drag it down across your range.

A highlighted cell containing the formula. The small square box on the lower-right corner of the highlight box encircled. You click-and-drag it to duplicate the content of the cell to adjacent cells. 


Voila! You have now applied the formula to all rows below the header. 

Original sheet. Contains item, price, amount, sales, cost, and total cost. Now with profit added in the last column and calculated. 
Our original sheet, now with profit in column H!


Step 3: Go to the sheet containing the Pivot Table and adjust the range to include the new column. Access the Pivot table editor by clicking on any cell of the Pivot Table and then look for the range box, just below the label Pivot table editor.

Pivot table editor. Focus on the data cell range on the top of the box. Range: week28!A1:G450. The new column is not yet included. 
The new column is not yet included. 


Step 4: For our example, the change is simply from week28!A1:G450 to week28!A1:H450. Refresh the Pivot Table by clicking on any cell outside the Pivot Table and then clicking on any cell of Pivot Table again. The new column will be listed when you click Add beside the Values.



Adding custom formulas in the Pivot Table directly

If you do not need to keep the values for individual entries and just want the summary or you are not allowed to modify the original sheets, then you can go straight to the sheet containing the Pivot Table. Here are the steps:

Step 1: Open the sheet containing the Pivot Table. Open the Pivot table editor by clicking on any cell in the Pivot Table. 

Step 2: Go to the Values section of the Pivot table editor and click the Add button beside it. A drop-down list of columns from the source sheet of the Pivot Table will appear. In the end, there is an option called Calculated Field. Click the Calculated Field.

Pivot table editor. Add button beside Values as clicked. The drop-down box lists the options, which are the columns in the source sheet.
The drop-down box lists the options, which are the columns in the source sheet.


Step 3: A new entry for the Calculated Field will appear listed below the Values as label. The custom formula should be placed in the box below the Formula box, using the labels of the columns in the Pivot Table as the variables. 

Calculated Field 1. The formula is =0, not yet replaced by custom formula. Summarize by SUM, Show as Default. 
The formula is =0, not yet replaced by custom formula.

For our example, the custom formula is 

='total price' - 'total cost',

Notice we are referencing the name of the columns we included in the Pivot Table. To customize the name of the field, simply double-click on the cell containing the header of the column and type the name. 

For instance, we can change the name "Calculated Field 1" to "Profit." Our example now looks like this:

Pivot table. Contains the new calculated field. Column name Profit. Calculated for each product.
Voila! Pivot table now contains profit per item.


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.