Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Calculated Field
2.1.
Example
3.
Calculated Item
3.1.
Example
4.
FAQs
5.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

Calculated Field and Calculated Item

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Microsoft Excel is powerful data visualisation and analysis software, which uses spreadsheets to store, organise, and track data sets with formulas and functions. Excel is used by marketers, accountants, data analysts, and other professionals. It's part of the Microsoft Office suite of products.

Calculated fields and calculated items are custom formulas in an Excel pivot table.

There are a few limitations to utilising pivot table formulas in general:

  • Formulas are only available in pivot tables that aren't OLAP(Overview of Online Analytical Processing)-based.
  • Formulas that relate to the totals or subtotals in the pivot table can't be created.
  • Worksheet cells cannot be referenced by address or name in formulas.

 

You may use a built-in command to create a list of all the formulas in a pivot table after you've created them.

We'll set up a pivot table using both types of formulas in the examples below to examine where and how each style of formula performs best.

Calculated Field

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

  • To do calculations on other values in the pivot table, we use calculated fields.
  • The individual amounts in the other fields are first added together, and then the total amount is calculated.
  • For a calculated field, the sum function is the sole option.
  • A calculated field is added to the pivot table as a new field, and its computation can include the sum of other fields.
  • In the pivot table, calculated fields show with the other value fields. A computed field's name, like those of other value fields, might be preceded by ‘Sum of’.
  • The PivotTable Field List shows calculated fields.

Example

The data from another field are used in a computed field. Execute the procedures below to create a calculated field.

Product, Quantity, and Sales are the three fields of the source data. A calculated field called "Unit Price" is the fourth field.

Field list after adding calculated field

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

The calculated field "Unit Price" is called after the formula "=Sales/Quantity," as shown below:

The Insert Calculated Field window

Note: Single quotes (') must be used to surround field names with spaces. When you click the Insert Field button or double-click a field in the list, Excel will automatically add these.

After being added to the Values area, the Unit Price field is renamed "Unit Price " (notice the extra space).

Excel won't let you use the exact same field name that exists in the data in a pivot table, so you'll need the extra space.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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.

Field list does not show calculated item

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:

The Insert Calculated Item window

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!
 

Previous article
Slicers and Timeline
Next article
GetPivotData
Live masterclass