Using Custom Formula in a Google Sheets Pivot Table (3 Easy Ways)

The pivot table is a powerful tool that helps summarize large contents of data into something that is easy to derive insight from. While this tool has its plethora of customizable options, the ability to personally customize and present data with formula is crucial in many aspects.

In this simple tutorial, we will look at a few ways that we can add and use custom formulas in a Google Sheets pivot table.

Let’s get started.

3 Ways to Add and Use Custom Formula in a Google Sheets Pivot Table

1. Include Custom Formula from the Source Dataset by Increasing the Range

It is not uncommon to have new data fields added to a dataset in Google Sheets. Especially after the original dataset is already being used for calculations or pivot tables.

For example, here we have a simple dataset:

source dataset for custom formula in a google sheets pivot table

This is being used as source data for the following pivot table.

pivot table generated from the source dataset

Now, let’s add a new column in the source dataset that will contain the number of Unsold Units. In column F, we enter the formula to find the number of unsold units by calculating the difference between the In Stock and Sold Unit columns:


The formula also accommodates blank cells.

custom formula to find the difference between two columns

Back at the pivot table worksheet, we can’t find the column with the custom formula to add to the pivot table. This is because the range that was used to create the table did not include column F.

custom formula column is not included in the pivot table range

To add the column with a custom formula to the Google Sheets pivot table, we simply update the range to include column F:

updating the range allows the pivot table to show the column with the custom formula

The result:

adding the column with custom formula in a google sheets pivot table

2. Add Custom Formula Directly in the Pivot Table with Calculated Field

On the other hand, you may ask, can we not create custom calculations directly in the pivot table?

The answer is of course yes.

The Pivot Table Editor provides its users with an option called Calculated Field under the Values panel. This option allows users to enter custom calculations directly into the Google Sheets pivot table.

the calculated field option under the values panel

Clicking on Calculated Field will open a new panel where you can input the calculation formula. For this example, let’s say we want a column calculating the percentage of the “% of Units Sold” for each product.

=('Sold Unit'/'In Stock')*100

adding custom calculated field in a google sheets pivot table

As you may have noticed, the only difference between regular formulas and pivot table formulas is how we input cell references. In a regular worksheet, we use a normal cell range reference for columns (C2:C), whereas in the pivot table we use the column header names inside single quotes (‘Sold Unit’).

Note: Some formatting was applied to the pivot table to make it look more presentable. Got rid of the empty cells and Grand Total rows and put a border around the values.

Learn More: How to Apply and Work with a Calculated Field of a Google Sheets Pivot Table

3. Filter Using Custom Formula in a Google Sheets Pivot Table

Filtering data has always been a common process for any table with data. As such, the pivot table editor has its own Filters panel.

While regular filters are available that can take care of most needs, a user may sometimes need a custom approach.

For example, let’s say we want to display the Region and Sold Unit data for Total Sales above $2000.

We have set the preliminary filter:

the preliminary filter is set

To set a custom condition, follow these steps:

  1. Click on Status.
  2. Filter by condition.
  3. Custom Formula is.
  4. Enter the following formula:
='Total Sales'>2000

adding the custom formula to the filter

The result:

filtered by custom formula in a google sheets pivot table

Only the North and West regions have seen sales of more than $2000.

Learn More: How to Filter with Custom Formula in a Pivot Table of Google Sheets

Final Words

As powerful a tool as pivot tables is, custom formulas can add another layer of customizability to the summarization of the data in a Google Sheets pivot table.

On that note, it is advisable to directly create calculation fields in the pivot tables instead of creating them in the source dataset. This is for the sake of efficiency.

Please feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply