How to Add Custom Formulas to a Google Sheets Pivot Table

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 Pivot Table Formulas in the Table

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:

‍