Pivot Table Calculated Field & Formula

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Sheeba M

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Is Pivot Table Calculated Field & Formula?

Pivot Table Calculated Field & Formula refers to the Pivot Table feature that simplifies creating calculated fields. A calculated field is one that derives its value w.r.t other fields. It helps in making the analysis of data easy and smooth, thereby facilitating smoother decision-making.

For example, we can visualize this feature as shown in the image below.

Pivot Table Calculated Field & Formula

The Pivot Table calculated field allows the data to be organized in a sequence and be used for calculating the fields that are dependent on the values contained in the other fields. The feature enables users to feed the data without hassle.

Table of contents
  • The Pivot Table Calculated Field & Formula helps users comprehend the generated PivotTable in relation with the other calculated fields and formulas.
  • We can create PivotTable Slicers too, to breakdown the Pivot Table into more simple tables.
  • When we generate a PivotTable for a dataset, we can modify the data within the selected cell range for the PivotTable to get updated. However, if we add more rows and columns, it will not reflect on the generated PivotTable. In such scenarios, we can make the PivotTable into a Dynamic Table to overcome the issue.
  • We can also use the Conditional Formatting to highlight the desired data in the Pivot table that is generated.

Pivot Table Calculated Field & Formula Explained

  • Pivot Table calculated field & formula arranges values in different fields in a proper sequence and allows users to handle the data either through manual reference or by using the formula.
  • The calculated fields derive their value from the other fields fed in the data set. This organized set of data makes it easier for analysts to analyze the details and make well-informed, wise decisions.
  • We can use some basic mathematical operations inside the calculated fields in the Pivot Table. However, we cannot use the logical and other thread functions.
  • A Cell reference will not change if the reference is generated via the GETPIVOTDATA function.
  • The calculated field formulas are also a part of a PivotTable. If there is a change in the source data, then the formulas will be unchanged until the pivot table is refreshed,

How To Add/Edit The Pivot Table Calculated Field & Formula?

To Add/Edit the Pivot Table Calculated Field & Formula, we must,

  • First, select the data range, and generate a PivotTable.
  • Next, click the generated Pivot Table to get the Analyze and the Design Tabs on the ribbon.
  • Finally, click the “Analyze” and the options to “Insert the Calculated field”.

Let us understand the same with an example.

The steps to Add/Edit the Pivot Table Calculated Field & Formula are as follows:

  1. Select the data that is used to generate the PivotTable.


    Pivot Table Formula Example 1

  2. Go to the ribbon and select the “Insert” tab. From the “Insert” tab, choose to insert a “PivotTable.”


    Pivot Table Formula Example 1-1

  3. Select the “PivotTable Fields” such as “Sales Person” to the “ROWS” and Q1, Q2, Q3, and Q4 sales to the “Values.”


    Pivot Table Formula Example 1-4

    Now, the PivotTable is ready.
    Pivot Table Formula Example 1-5

  4. After the PivotTable is inserted, go to the “Analyze” tab, that will only be present if the PivotTable is selected.


    Pivot Table Formula Example 1-2

  5. From the “PivotTable Analyze” tab, choose the option of “Fields, Items Sets” and select the “Calculated Field” of the PivotTable.


    Pivot Table Formula Example 1-3

  6. In the option of “Insert Calculated Field” in the Pivot Table, insert the formula as required in the case.


    Example 1-6

    Here, we have formulated a formula to calculate the 0.05% commission on sales.

    Example 1-7

How To Switching Off The “GetPivot” Table Function?

We can always switch off the “Getpivotdata” function using the “Analyze” tab. Here, go to the “PivotTable” group, as shown below.

Example 4-1

Click the “Options” option drop-down, and click the checked “Generate GetPivotData” option to turn it off, as shown below.

Example 4-2

Now, we can use the formulas in the PivotTable as we do in the case of a simple range.

Important Things To Note

  • Ensure to generate a PivotTable with a real-time existing dataset, because it will not work for blank rows or columns.
  • Anytime during the calculation, we can modify the calculated fields to get a new value.
  • Always remember to apply conditional formatting to the generated PivotTable and not to the dataset, when working with Pivot Table Calculated Field & Formula.

Frequently Asked Questions

1. How To Use the Pivot Table Calculated Field & Formula?

We can use the Pivot Table Calculated Field & Formula in 2 ways, namely:
a. Using Manual Reference of Cell in the Pivot Table Formula.
If we have to give a cell reference in a formula, we can type the location as shown below.

Example 2

b. Using GetPivotTable Function to give Reference of a Cell to a Formula.
We can also choose not to enter the cell’s location 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 manually typing the cell’s location.

Example 3

2. What is a PivotTable in Excel?

 • Pivot Tables are generated tables using an existing dataset. These tables project the dataset in an organized way where we can only filter the required data.
 • They help users track and analyze a large dataset using a compact table.
 • It helps to compare data, or show relationships between parameters.

3. Why is the Pivot Table Calculated Field & Formula not working or greyed out?

The Pivot Table Calculated Field & Formula is not working because,
 • The dataset linked to the generated Pivot table is deleted or modified.
 • The formulas may be changed, and the calculated field displays an Excel error.
 • The data modifications are done within the selected dataset, and the PivotTable is not updated automatically. In such scenarios, refresh the Pivot table manually, to display the current data.

4. How to use the Conditional Formatting option in Excel?

The steps to apply conditional formatting are as follows:
 • First, generate the Pivot Table for an existing dataset. If required, create a dynamic table to reflect any addition of data.
 • Next, click the PivotTable to activate the Analyze tab, and then insert the “Calculated field”, as learnt in the article.
 • Finally, choose the Calculated Field column or the PivotTable range - select the “Home” tab - go to the “Styles” group - click the “Conditional Formatting” option drop-down - select the “New Rule” option, as shown below.

Conditional Formatting - New Rule

Download Template

This article must help understand Pivot Table Calculated Field & Formula with its formulas and examples. You can download the template here to use it instantly.