Table of Contents
Pivot Table Calculated Field & Formulas
A pivot table is the first thing that comes to our mind when we are in a situation where we want to summarize the data in excel. However, sometimes we often face difficulty while using the pivot table at its core level that is using the formulas in the pivot table.
In the pivot table, you can create calculated fields that can perform a calculation on other pivot fields. In this article, we learn how we can achieve this goal –
How To Add A Calculated Field In Pivot Table?
Below are the examples of Pivot Table Calculated Field and how to insert formulas on other pivot fields.
Example #1 – Using Pivot Table Calculated Fields to Show a Field that is not in the Source Data
In this method, we will create a new header that is not present in the source data.
Step 1: Select the data that is to be used in a Pivot table.
Step 2: Go to the ribbon and select the “Insert” Tab. From the Insert tab choose to insert a “Pivot Table”.
Select the Pivot Table Fields such as Salesperson to the Rows and Q1, Q2, Q3, Q4 sales to the Values.
Now Pivot Table is ready.
Step 3: After the pivot table is inserted then go to the “Analyse tab” that will be present only if the pivot table is selected.
Step 4: From the “Analyze tab” choose the option of “Fields, items & Sets” and select the “Calculated fields” of Pivot Table.
Step 5: From the option of Calculated Field in Pivot Table, Insert the formula as required in the case.
Here we have formulated a formula that will calculate the .05% commission on sales.
Example #2 – Using Manual Reference of Cell in the Pivot Table Formula
If we have to give a reference of cell in a formula that we can type the location as shown below.
Example #3 – Using GetPivotTable Function to give Reference of a Cell to a Formula
We can also choose not to enter the location of the cell manually, in this case, we can choose to insert the location by using the keyboard instead of a mouse.
This type of location (GetpivotData) is inserted if we select the location instead of typing the location of the cell manually.
Example #4 – Switching off the “GetPivot” table Function in a Pivot Table to have a Clean Formula
We can always choose to switch off the “Getpivotdata” function by going to analyze tab and selecting the dropdown.
Here we need to turn off the “generate GETPIVOTDATA” option and we can simply use the formulas in the pivot table as we do in a case of simple range.
Explanation of Pivot Table Formula in Excel
Using formulas can be sometimes necessary if we want to calculate a field that is not yet present in the source data from which we have created a pivot table.
This might be a case of sales data where we have sales of all salesperson for each of the month but we do not have a sum of sales they have made in the complete year. Now, in this case, what we can do is, either we can go back to the source data and create a new header that has this data or we can use the formula in the pivot table to get the work done without changing the source data in any way. So, in this case, we will use the formulas that will first calculate the sum of all the months and present that in a new header as a total sale.
Using the formulas is utmost necessary if in a case where we cannot change the source data, either that is preserved by protection or we may not be allowed to change the data because of some permission lack or if we are using a shared file.
However, inserting formulas in a pivot table is not as easy as it seems. This is because there is “GetPivotData” functionality that creates a problem if we give reference of cell that is located inside the pivot table.
Things to Remember About Excel Pivot Table Calculated Field
- We can use some basic mathematical operations inside the calculated fields in the Pivot Table, this means that we cannot use the logical and other thread functions.
- Cell reference will not change if in case the reference is generated via “GetPivotDate” function.
- Calculated field formulas are also a part of a pivot table.
- If there is a change in the source data then the formulas will be unchanged until the pivot table is refreshed.
You can download this Pivot Table Calculated Field Excel template here –Pivot Table Formula Excel Template
This has been a guide to Pivot Table Calculated Field. Here we discuss the use of use formulas in Pivot table using calculated field along with practical examples and downloadable excel template. You may learn more about excel from the following articles –