How to Insert a Calculated Item into Excel Pivot Table

What Is a Calculated Field?

A Calculated Field in a PivotTable is a custom field that you create by using a mathematical expression to perform calculations in the PivotTable. You can also use a Calculated Field in pivot table to perform mathematical calculations on the data without changing the data in the source worksheet.


Insert Calculated Item into Excel Pivot Table

Step 1 – Create a Pivot Table

  • Organize your data in a table.

Inserting Calculated Item in Pivot Table

  • Select all the data in the dataset.
  • Go to the Insert tab.
  • Pick the PivotTable command from the ribbon.
  • Choose the From Table/Range option.

A PivotTable from table or range wizard will pop up.

  • Define the PivotTable. We have defined cell I4 on the Existing Worksheet.
  • Press OK.

Creating Pivot Table

PivotTable Fields will appear on the right side of the worksheet.

  • Select the parameters that you want to have in the PivotTable from the PivotTable Fields. We have taken Sales Rep, Month and Sales as parameters.
  • Define Rows and Columns from the PivotTable Fields.

Create a PivotTable

A PivotTable will be created.


Step 2 – Insertion of Calculated Item

The Calculated Item feature is used to insert new calculations inside a previously created PivotTable. As we have created a PivotTable on sales in each month in the previous section, we will insert their quarterly commission as Calculated Item.

  • Select Row Labels or Column Labels from the Table.
  • Go to the PivotTable Analyze tab.
  • Choose Fields, Items, & Sets from the ribbon.
  • Click on Calculated Item…

Insertion of Calculated Item

An Insert Calculated Item wizard will appear. We have set Month as a parameter; the wizard shows as Insert Calculated Item in “Month”.

  • Enter a descriptive name for the new item in the Name field and enter a formula in the Formula field in Insert Calculated Item as shown in the following image. You can use items in other fields in the formula but you can’t use worksheet functions. In our example, we have entered Qtr1 Commission in the Name field and this formula in the formula field:
=10%*(Jan+Feb+Mar).
  • Click Add.

Insertion of Calculated Item

The item Qtr1 Commission will be inserted into the Items list.

  • Using the above-mentioned process, create three more items for the conditions mentioned below:

Name field: Qtr2 Commission, Formula field: =11%*(Apr+May+Jun)

Name field: Qtr3 Commission, Formula field: = 12%*(Jul+Aug+Sep)

Name field: Qtr4 Commission, Formula field: = 12.5%*(Oct+Nov+Dec)

Inserting Calculated Item in Pivot Table

  • The newly added data will appear in the table.

It will automatically be updated in the PivotTable.

Inserting Calculated Item in Pivot Table

Read More: How to Create Calculated Field in Pivot Table Data Model


Download Practice Workbook


<< Go Back to Calculated Field in Pivot Table | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

2 Comments
  1. This is great, thank you.
    But is there a way to have calculated item not included in the total calculation?

    • Hi Yenny,
      Thanks for your query.
      You can use the calculated items option only for those fields which are available in our pivot table. Because if the field did not show in the pivot table, the corresponding field will also not show in the item section of the dialog box, which appears after clicking on Fields, Items, & Set,

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo