Have you ever wanted to perform custom calculations on your data in Google Sheets without altering the original spreadsheet? Adding a calculated field to a pivot table allows you to do just that. Pivot tables already summarize your data in helpful ways. But with calculated fields, you can apply formulas to that summarized data right inside the pivot table. This makes it easy for you to gain deeper insights into your data.
In this article, we will explain step-by-step what a calculated field in Google Sheets is, why it is useful when working with pivot tables, and how to add one to your pivot table in Google Sheets.
Whether you’re an absolute beginner or a seasoned Sheets user, you’ll learn exactly how to create customized calculations within your pivot tables using the powerful calculated field feature. After reading this article to the end, you’ll be able to add calculated fields like a pro.
What is a Calculated Field in Google Sheets?
A pivot table allows you to summarize and analyze data from a spreadsheet. It does math to show totals, averages, counts, and other neat stuff. But sometimes, the built-in math options aren’t enough, especially when you want to calculate something extra special.
That’s where a calculated field in Google Sheets saves the day. A calculated field is like a little math machine you can add to a pivot table. It lets you create custom formulas using the data.
For example, maybe you’re using a pivot table to explore sales data. You could make a calculated field to show each item’s profit. Just take the sales amount for that item and subtract its cost. Pretty cool, right?
So, in simple terms, a calculated field applies formulas to your pivot table, going beyond the basic options. You make up the math yourself to answer new questions about your data. It makes pivot tables even more powerful for crunching numbers your own way.
Copy Sample Sheet
If you want to follow along with today’s tutorial on adding calculated field in Google Sheets, feel free to copy our sample sheet from the link below.
Click Here to Copy Sample Sheet
Learn How to Add Calculated Fields to a Pivot Table in Google Sheets
Now that you know what a calculated field in Google Sheets is, let’s explore how to use calculated fields with an easy example. This guide focuses on calculated fields, so we won’t get into too much detail about creating pivot tables.
First, we’ll make a simple pivot table and then learn to include calculated fields.
Today’s guide will look at sales data from employees in three regions: East, West, and Central, over three months. This dataset is small and simple, which makes it easier to learn from.
Typically, pivot tables are used for much bigger sets of data.
Our goal is to analyze this sales data using ‘Calculated fields’ in Pivot tables. Calculated fields allow us to do many types of analysis. In this guide, we’ll focus on two things:
- Finding out total sales for each region for the entire three months.
- Counting how many employees in each region made over $50,000 in sales each month.
To do this, we’ll:
- Make a pivot table that sorts data by region.
- Use a calculated field to show total sales for each region over the quarter.
- Use another calculated field to count the number of employees in each region who made more than $50,000 each month.
First, we’ll make a basic pivot table organized by region. Then, we can create our calculated fields to enhance the analysis. Ready? Let’s do it.
Creating the Pivot Table
Before we can add a calculated field in Google Sheets, we first need to create a pivot table, which is easy and straightforward. Follow us as we go over the steps together.
Step 1: Highlight Your Data Set
We first need to select the cells in our spreadsheet that contain the data we want to analyze in the pivot table. For this tutorial, we will highlight the entire data set in our spreadsheet.
Step 2: Insert Menu > Pivot Table
Having selected the data we want to use to create our pivot table, let’s quickly add the pivot table. To do that, navigate to the insert menu. From the options there, choose the option for Pivot table.
Step 3: Define Pivot Table Area
When you choose to make a Pivot table, a small window will appear. In this window, you have to decide if you want your pivot table in a new sheet or the current sheet. For this tutorial, we’ll put the pivot table in the current sheet.
After selecting that, we need to pick where our pivot table should go in the sheet. For this example, we’ll place it in cell G1.
If you did everything exactly as we showed you, you should have something like this in your spreadsheet:
In addition to this, you should also see the pivot editor at the extreme right of your spreadsheet. This is what it looks like:
Step 4: Add Unique Region
Since our objective is to display region-wise results, what we want to do is add unique regions to our pivot tables. Here is how you want to go about it.
Head to the pivot table editor at the extreme right of your spreadsheet. You’ll see a bunch of options. From those options, you want to click the Add button next to Rows.
From the drop-down option that appears, select the option for Region.
This action will add each unique region to the pivot table. It should look something like this.
With this, your pivot table is now ready to accept the calculated fields, which is the entire goal of today’s article. In the next section, we will show you how to add a calculated field in Google Sheets. Let’s dive right in, shall we?
Adding the Calculated Fields to the Pivot Table
In the previous section, we learned how to set up a pivot table in Google Sheets. We chose to place our pivot table in the current sheet, specifically in cell G1 and explored how the Pivot Table Editor appears as a sidebar, helping us manage and arrange our data.
Now, let’s move on to the next important step: Adding Calculated Fields to the Pivot Table.
Sometimes, the default functions in a pivot table might not be enough for the specific calculations we need. For example, in our situation, we want to combine and calculate data from three different columns.
Unfortunately, this specific function isn’t available in the standard options of a pivot table. That’s where calculated fields come in handy. They allow us to create custom calculations that aren’t ordinarily available. In this section, we’ll learn how to add and use these calculated fields to get the precise data analysis we need.
Here is how to add a calculated field in Google Sheets.
Step 1: Access the Pivot Editor
Start by accessing the pivot table editor at the extreme right of your spreadsheet. If yours isn’t there, you can simply launch it by clicking the edit button in your pivot table.
Step 2: Click the Add Button Next to Values
With the pivot editor now launched, scroll down and click the Add button next to Values.
Step 3: Add Calculated Field
After clicking on the Add button next to the options for Values, you’ll be presented with several options. Since our objective here is to add a calculated field to our pivot table, we will select the option for Calculated field.
The previous action will instantly update our pivot table and include a column for the Calculated Field. Here is what it looks like:
Additionally, you should see a small box in the pivot table editor. This box contains all the details for the new calculated field. It should look something like this:
When you add a calculated field, it shows zeros in the pivot table. That’s because we haven’t given it a formula yet – it’s running on empty.
In the box for editing the calculated field, you’ll see a dropdown menu labeled “Summarize by.” This gives you choices for the type of calculation:
SUM or Custom.
Let’s walk through making two sample calculated fields to see the difference.
Summarizing a Calculated Field by SUM
First, we want to show the total sales per region for the whole quarter – April, May, and June combined.
Our original data shows the individual monthly sales. But with a calculated field, we can add them up.
Here’s how to configure a calculated field that sums the quarterly sales:
- In the “Formula” box, type: =’April Sales’+’May Sales’+’June Sales’
- From the “Summarize by” menu, choose “SUM.”
This will add all the monthly sales data and display the total sales per region. If you did everything as we explained above, you should have something like this:
We can also customize the name of the calculated field column. To do that:
- Double-click the column header for the Calculated Field
- Delete what you have there and replace it with something like “Total Quarterly Sales.”
With all these changes, our pivot table should now display each region’s total sales revenue across the quarter.
Summarizing by Custom Formula
Now, let’s add three special fields to our pivot table. Each field will show us the number of employees in each region who sold more than $50,000, and we’ll have one field for each month: April, May, and June.
To do this, we’ll use a unique formula called COUNTIF. This formula helps us count things based on certain conditions, like how many employees sold more than $50,000. Since the pivot table doesn’t have this COUNTIF function by default, we will use the Custom Formula option.
Remember, we want to count each employee separately. This means we’re not adding up (summing) the numbers; we’re just counting them.
Let’s start with April. We’ll create a new Calculated field in our pivot table for April and set it up with the right formula.
Here is how to go about it:
Step 1: Add a Calculated Field for April to the Pivot Table
Remember how we added the Calculated field in the previous section? Well, we need to do the same here. All you need to do is click the Add button next to Values and select Calculated field.
Step 2: Add COUNTIF Formula
With the new calculated field now created in the pivot table, change the option from SUM to Custom and enter the following formula:
=COUNTIF(‘April Sales’,”>50000″)
Now, let’s rename the Calculated field appropriately. Since the objective was to see the number of sales above 50,000 for April, let’s rename it to something like this: Sales over 50k
If you followed the steps exactly as we showed you, the Pivot table should be instantly updated to indicate the number of sales in all three regions above 50,000 for April.
Looking at our sample sheet, you can see we have 0 because there are no sales within all three regions above 50,000 for April.
Here is what our sample sheet looks like.
Now, repeat the same process for the other months.
For us, this is what our spreadsheet looks like after including May and June.
Now, that’s how to add a calculated field in Google Sheets. Though tricky, you should get the hang of it if you follow today’s steps precisely as we showed you.
Final Thoughts
And there you have it – a complete guide on using calculated fields in Google Sheets pivot tables.
In today’s guide, We walked through what calculated fields are, why they’re helpful, and step-by-step instructions for adding them to your pivot tables.
So far, you have learned:
- How to build a basic pivot table in Sheets to summarize your data
- Adding a calculated field that sums values (using the SUM option)
- Adding a calculated field that counts based on criteria (using CUSTOM)
Our example showed how calculated fields unlock tons of customization for your pivot table analysis. Using the information you have learned, you should be able to crunch numbers exactly how you want.
Some next steps to apply your new skills:
- Brainstorm what key metrics would be helpful for your data
- Build calculated fields to display those custom calculations
- Analyze and make decisions from the enhanced pivot table
The possibilities are wide open. Calculated fields let you ask more profound questions and get custom answers from your data.
We hope today’s guide has been pretty helpful. Feel free to comment if you need more clarification, and we will be happy to provide a detailed answer.