How To Add A Calculated Field In Pivot Table?

Below are the examples of the PivotTable calculated field and how to insert formulas on other Pivot fields.

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.

  • Select the data that is to be used in a PivotTable. Go to the ribbon and select the “Insert” tab. From the “Insert” tab, choose to insert a “PivotTable.” Select the “PivotTable Fields” such as “Sales Person” to the “ROWS” and Q1, Q2, Q3, and Q4 sales to the “Values.” Now, the PivotTable is ready. After the PivotTable is inserted, go to the “Analyze” tab that will only be present if the PivotTable is selected. From the “PivotTable Analyze” tab, choose the option of “Fields, Items Sets” and select the “Calculated Field” of the PivotTable. In the option of “Insert Calculated Field” in the Pivot Table, insert the formula as required in the case. Here, we have formulated a formula to calculate the 0.05% commission on sales.

Now, the PivotTable is ready.

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

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 (GetpivotDataGetpivotDataThe GetPivotData function in Excel is a query function that fetches values from a pivot table based on specific criteria such as the pivot table’s structure or the reference provided to the function.read more) is inserted if we select the location instead of manually typing the cell’s location.

Switching off the “GetPivot” table Function in a Pivot Table to have a Clean Formula

We can always switch off the “Getpivotdata” function by selecting the “Analyze” tab and selecting the dropdown.

Here, we need to turn off the “Generate GetPivotData” option, and we can use the formulas in the PivotTable as we do in the case of a simple range.

Things to Remember

  • We can use some basic mathematical operations inside the calculated fields in the Pivot TablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more. However, we cannot use the logical and other thread functions.A Cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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 refreshedPivot Table Is RefreshedTo refresh pivot tables, you may use the following methods - refresh pivot table by changing data source, refresh pivot table using right click option, auto-refresh pivot table using VBA Code, refresh pivot table when you open the workbook.read more.

This article is a guide to PivotTable Calculated Field. Here we discuss formulas in the PivotTable using calculated fields, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –