How to Insert a Calculated Item into Excel Pivot Table

PivotTable is an amazing feature that we use to represent our data in a more organized way. Sometimes, we might face the necessity of inserting calculated item in Pivot Table which is previously completed. That is kind of a complicated process. In this article, I am going to explain the process of how to insert a calculated item into Excel PivotTable. I hope this article will be very helpful for you.


Download Practice Workbook


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 to perform mathematical calculations on the data in the PivotTable, without changing the data in the source worksheet.

When you need to execute mathematical operations on data that is contained in a PivotTable, Calculated Fields come in very handy. For instance, you could wish to add the values in a field or figure out what the average value of a field is.


Step by Step Procedure for Inserting a Calculated Item into Excel Pivot Table

Inserting Calculated Item in Pivot Table is not a difficult task to do. you just need to follow the right procedure. In order to explain the whole procedure to insert a calculated item into Excel PivotTable, I have divided the whole procedure into two major sections.

  1. Create a PivotTable
  2. Insertion of calculated Item

1. Create a Pivot Table

A  PivotTable is an effective tool that let you rapidly summarize data. A PivotTable collects a variety of data, giving you an easy method to see trends and patterns.

Steps:

  • Organize your data in a table.

Inserting Calculated Item in Pivot Table

  • Select all the data in the dataset first.
  • Then, go to the Insert tab.
  • Pick the PivotTable command from the ribbon.
  • Next, choose the From Table/Range option.

A PivotTable from table or range wizard will appear.

  • Define the PivotTable Here, I have defined cell I4 on the Existing Worksheet.
  • Then, 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. Here, I have taken Sales Rep, Month, and Sales as parameters.
  • Define Rows and Columns from the PivotTable Fields.

Create a PivotTable

Thus, we can create a PivotTable.


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.

Steps:

  • Select Row Labels or Column Labels from the Table.
  • Then, go to the PivotTable Analyze tab.
  • Choose Fields, Items, & Sets from the ribbon.
  • After that, click on Calculated Item…

Insertion of Calculated Item

An Insert Calculated Item wizard will appear. As I have input Month as a parameter, the wizard shows as Insert Calculated Item in “Month”.

  • Next, 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 figure. 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).
  • After that, click Add.

Insertion of Calculated Item

Thus, the item Qtr1 Commission is 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

  • Thus, we will have the newly added data in the table.

It will automatically be updated in the PivotTable.

Inserting Calculated Item in Pivot Table

This is how Inserting Calculated Item in Pivot Table is done.


Conclusion

That’s all for today. I have tried to explain the process of how to insert a calculated item into Excel PivotTable. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

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

ExcelDemy
Logo