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.
- Select the data that is to be used in a Pivot table.
- 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 the Pivot Table is ready.
- After the pivot table is inserted, then go to the “Analyse tab” that will be present only if the pivot table is selected.
- From the “Analyze tab,” choose the option of “Fields, Items Sets” and select the “Calculated fields” of the Pivot Table.
- From the option of Calculated Field in the Pivot Table, Insert the formula as required in the case.
Here we have formulated a formula that will calculate the .05% commission on sales.
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.
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 (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.) is inserted if we select the location instead of typing the location of the cell manually.
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 the 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.
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. ; this means that we cannot use the logical and other thread functions.
- 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. will not change if in case the reference is generated via the “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 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..
This has been a guide to Pivot Table Calculated Field. Here we discuss the use of use formulas in the Pivot table using calculated field along with practical examples and downloadable excel template. You may learn more about excel from the following articles –