Calculated Field and Calculated Item

Calculated Item

The following are the main characteristics of the calculated items in the pivot table:

• A calculated item becomes a pivot field item.
• The total of additional items in the same field can be used in its calculation.
• The results are summed after the individual records in the source data have been calculated.
• In the Row or Column portion of the pivot table, calculated items are shown alongside other elements.
• The PivotTable Field List does not display calculated entries.

Example

The values of other items are used in a computed item. Execute the procedures below to insert a computed item.

Suppose there are three fields in the original data: Date, Region, and Sales. The calculated item is not included in the field list.

Select "Insert Calculated Item" from the "Fields, Items, and Sets" menu on the ribbon to create the calculated item:

The calculated field "Southeast" is named after the formula "=South + East," which is seen below:

Field names that contain spaces must be enclosed in single quotes ('). When you click the Insert Field button or double-click a field in the list, Excel will automatically add these.

To avoid double-counting, the East and South areas must be filtered off after the calculated item is created.

FAQs

1. What is Excel?

Microsoft Excel is powerful data visualisation and analysis software, which uses spreadsheets to store, organise, and track data sets with formulas and functions.

2. What are pivot tables?

A Pivot Table is a tool for summarising, sorting, reorganising, grouping, counting, totalling, or averaging data in a table. It allows us to change rows into columns and columns into rows. It lets us organise our data by any field (column) and perform complex calculations on it.

3. What is a calculated field?

In a pivot table, we can create a new field called the calculated field that performs calculations on the sum of other pivot fields using our own formulas.

4. What is a calculated item?

In an Excel pivot table, a Calculated Item is a custom formula that can use the sum of other items in the same field. For example, we can calculate the total of two additional things in a field.

5. What is the difference between a calculated field and a calculated item?

Calculated Fields are formulas that can refer to other fields in the pivot table. Calculated Items are formulas that can refer to other fields in the pivot table. Formulas that relate to other items within a pivot field are known as calculated items.

Key Takeaways

In this article, we have learned about the concept of pivot tables in excel. We also learned about the concept of calculated fields and calculated items. We also saw an example of how to insert a calculated field and calculated item in our pivot tables. We also learned about the key difference between a calculated field and a calculated item.

Apart from this, you can also expand your knowledge by referring to the blog- Introduction to Excel.

For more information, refer to Worksheets in Excel.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enrol in our courses and refer to the mock test and problems available; take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

Happy Learning!