Google sheets pivot table calculated field percentage

A pivot table is a powerful tool that lets you group and summarize large amounts of data so you can draw meaningful insights. They let you use a wide variety of summarizing metrics to help you analyze data. Moreover, they come with a ‘Calculated field’ feature that enables you to customize the Pivot table results with special functions and formulae.

In this tutorial, we will show you step-by-step, with the help of a simple example, how to add a calculated field to a Google Sheets pivot table.

• What is a Calculated Field?
• How to Use Calculated Fields in a Google Sheets Pivot Table
  • Creating the Pivot Table
  • Adding the Calculated Fields to the Pivot Table
    • Summarizing a Calculated Field by SUM
    • Summarizing by Custom Formula
• Important Notes
• Calculated Field FAQs
  • How do I add a calculated field in Google Sheets?
  • What is a calculated field in Google Sheets?
  • How do you insert a formula in a Google Sheets calculated field?
• Conclusion

What is a Calculated Field?

There are a number of analytical metrics that are built into a Pivot table, like sum, average, median, variance, etc. While these are quite helpful, they are usually not enough. We often need to perform other calculations that may not be present in the default Pivot table metrics.

A calculated field becomes quite useful in such situations. Calculated fields help you use customized functions and formulae in a pivot table. They perform computations on data from your original table and display the results in the pivot table.

How to Use Calculated Fields in a Google Sheets Pivot Table

To understand how calculated fields work, let us look at a simple example. Since this tutorial is primarily about calculated fields, we will not go over pivot tables in too much detail. We will simply create a basic pivot table and then move on to discussing how to add the calculated fields.

If you would like to know more about pivot tables, you can read our in-depth article: How to Make a Google Sheet Pivot Table

The following dataset contains 3 months’ sales data of employees from 3 different regions – East, West, and Central:

To make this tutorial easier to understand, we used a fairly small and simple dataset. Pivot tables are usually used to analyze much larger datasets.

We want to perform some analytics on the above data using Calculated fields in Pivot tables. There are an unlimited number of analytics that you can perform with calculated fields, but for this tutorial, let us assume that we want to create a pivot table to show the following:

• Region-wise sales during the whole quarter (total for all 3 months)
• Region-wise count of employees who made more than $50,000 in sales each month

To create a pivot table that can do this, we need to follow these steps:

1. Create a pivot table to display data region-wise
2. Add a calculated field to display total sales for the whole quarter (for each region)
3. Add a calculated field to display the count of employees (in each region), who made more than $50,000 each month

Let’s go over each of these steps.

Creating the Pivot Table

The first step is to create the pivot table. For this, follow the steps shown below:

• Select the range of cells containing your data.
• From the Insert menu, select Pivot table.

• A dialog box should appear asking you if you want to insert your Pivot table on the existing sheet or a new sheet. Select your preferred option. Since our dataset is quite small (and to make it easier to relate the results), we want our pivot table displayed beside the original data. So we opted to display the result in the existing sheet, starting from cell G1, as shown below:

• Click on the Create button.
• This will create the basic outline of your pivot table in the location you had specified, as shown below:

You should also see the Pivot table editor as a sidebar on the right side of the window. This sidebar appears every time you click on a cell of the Pivot table.

Since we want to display region-wise results, we can add unique regions from our dataset as separate rows of the pivot table.

For this, follow the steps shown below:

• Click on the ‘Add’ button next to ‘Rows’ (in the Pivot table editor).

• From the dropdown menu that appears, select ‘Region’.

• This will add each unique region to individual rows of your pivot table, as shown below:

Your pivot table is now ready for you to populate with your required calculated fields.

Adding the Calculated Fields to the Pivot Table

We need to add a calculated field when the pivot table default / built-in functions do not include the computations we require.

In our case, we need to combine and perform calculations on data from three different columns, a function that is not present in the default Pivot table functions. So we need to use a calculated field.

To add a calculated field, follow the steps shown below:

• Click on the ‘Add’ button next to ‘Values’ (in the Pivot table editor).

• From the dropdown menu that appears, select Calculated field.

• This will add a new column to your pivot table, as shown below.

• You should also see a small box in your Pivot table editor containing all the details about your new calculated field, as shown below:

At the moment, the calculated field simply shows a list of zeros in the pivot table, since we haven’t yet given it a formula.

If you click on the dropdown under ‘Summarize by’ in the calculated field box (of the pivot table editor), you will notice you have two options:

• SUM
• Custom

Let us create two example calculated fields to understand the difference between these two options.

Summarizing a Calculated Field by SUM

We want our first calculated field to display region-wise sales for the whole quarter.

Notice that our original dataset contains sales made by each employee over 3 months – Apr, May, and June.

But we want to consolidate the three month’s sales to get the total sales for the whole quarter.

Moreover, we want this result to be summed up region-wise. In other words, we want the results summarized by sum (for each region).

So let us configure the first calculated field as follows:

• In the input box under ‘Formula’, enter this formula:

• =Apr+May+June

• From the dropdown menu under ‘Summarize by’, make sure the SUM option is selected.

That’s it! Your calculated field should now show region-wise total sales in all 3 months – Apr, May, and June.

You can change the name of the calculated field column by double-clicking on it and typing in the new name, as shown below:

Summarizing by Custom Formula

Next, we want to add 3 calculated fields to display the region-wise count of employees who made more than $50,000, one for each month.

Since we want to count based on a condition, we will need to use the COUNTIF function, which is not present in the list of default pivot table functions.

Note that we want to count each sales value individually (we don’t want to sum up the counts). In other words, we don’t want the results summarized by SUM.

Add a new calculated field for the month of April and configure it as follows:

• In the input box under ‘Formula’, enter this formula:

• =COUNTIF(Apr,">50000")

• From the dropdown menu under ‘Summarize by’, make sure the ‘Custom’ option is selected.

This will display the region-wise total count of employees who made sales of more than $50,000 in April.

Change the name of the calculated field column at this point.

Repeat the same for May and June. Here’s how our Pivot table looks in the end:

Important Notes

Calculated fields can provide added functionality to your pivot tables. However, there are a few things that should be kept in mind when using them:

• You can only refer to data from your original dataset. Google Sheets calculated fields cannot work on any data directly from the pivot table.
• Make sure you use the correct column names with correct spelling in your formulae.
• If a column name contains more than one word with spaces in between, then make sure you enclose the column name in single quotes when using it in your formula.

Calculated Field FAQs

How do I add a calculated field in Google Sheets?

You can add a calculated field to your Pivot table by following the steps below:

1. In the Pivot table editor, click on the ‘Add’ button next to ‘Values’.
2. Select ‘Calculated field’ from the dropdown menu.
3. In the input box under ‘Formula’, enter your formula, making sure to use the correct column names from the original table.
4. Select the SUM or Custom option as needed from the dropdown menu under ‘Summarize by’.

What is a calculated field in Google Sheets?

A calculated field in a Google Sheets Pivot table is one that contains a special formula that refers to other fields of the original dataset. We usually use calculated fields when the built-in summary functions available with the pivot tables don’t include the computation that we need to perform. With calculated fields, you can create your own formulas and apply them to your pivot table.

How do you insert a formula in a Google Sheets calculated field?

After adding the calculated field, you should see a description box for it in the Pivot table editor. In this box, simply enter your formula in the input box under ‘Formula’. Make sure it uses the correct field names. If there are spaces in the field name, then make sure you enclose it in single quotes.

Conclusion

In this tutorial, we discussed Calculated fields in Google Sheets pivot tables. We explained when to use them and how to add them to a pivot table. We also showed you two examples of calculated fields – one which displays data summarized individually and another which summarizes by SUM.

We hope we have given you a basic idea and guideline on adding calculated fields to your pivot tables.

Can you calculate percentage in pivot table?

How do I add a calculated field to a pivot table in Google Sheets?

How do I add %age to a pivot table?

How do you automatically calculate percentages in Google Sheets?

How do I show both values and percentage in a PivotTable?