Calculated Fields & Items in PivotTables
Learn how to create calculated fields and calculated items in Excel PivotTables, apply formulas, calculate profit and profit percentage, and analyze summarized business data with practical examples.
1. Introduction
Excel PivotTables summarize information from a source dataset. Sometimes, however, we need to calculate additional values that are not directly available in the source data.
For example, a company may have Sales and Cost columns, but it also needs to calculate Profit. We can create a Calculated Field in a suitable PivotTable to calculate this value.
A Calculated Item is different. It performs a calculation using items within a PivotTable field, subject to the limitations of the PivotTable and its source.
Calculated Field vs. Calculated Item
Conceptual illustration for learning purposes.
2. What Is a Calculated Field?
A Calculated Field creates a new calculation in a PivotTable using fields from the source data. In a conventional PivotTable, its formula refers to field names rather than individual cell addresses.
Consider a business dataset containing Sales and Cost. We want to calculate the Profit for each product category.
Source Data
| Product | Sales (₹) | Cost (₹) |
|---|---|---|
| Laptop | 50,000 | 35,000 |
| Mobile | 30,000 | 20,000 |
| Tablet | 20,000 | 12,000 |
Required Calculation
= Sales - Cost
The formula subtracts the Cost field from the Sales field. Excel calculates the result using the field values in the PivotTable's underlying data.
Expected Result
| Product | Sum of Sales (₹) | Sum of Cost (₹) | Calculated Profit (₹) |
|---|---|---|---|
| Laptop | 50,000 | 35,000 | 15,000 |
| Mobile | 30,000 | 20,000 | 10,000 |
| Tablet | 20,000 | 12,000 | 8,000 |
| Grand Total | 1,00,000 | 67,000 | 33,000 |
3. How to Create a Calculated Field
Follow these steps in a conventional Excel PivotTable:
Create a PivotTable
Select the source data and choose Insert → PivotTable.
Open PivotTable Analyze
Click inside the PivotTable. Open the PivotTable Analyze tab. In some Excel versions, the tab may have a slightly different name.
Open Calculated Field
Select Fields, Items & Sets → Calculated Field, where available.
Enter the Name and Formula
Enter Profit as the name and
= Sales - Cost as the formula.
Click Add and OK
Add the calculated field, confirm the dialog, and verify the new Profit values in the PivotTable.
Calculated Field Dialog — Illustration
4. More Calculated Field Examples
| Calculated Field | Formula | Purpose |
|---|---|---|
| Profit | = Sales - Cost |
Calculates profit. |
| Net Sales | = Sales - Discount |
Calculates sales after discount. |
| Commission | = Sales * 5% |
Calculates commission at 5% of sales. |
| Total Expenses | = Rent + Salary |
Combines rent and salary fields. |
5. What Is a Calculated Item?
A Calculated Item creates a calculation involving items within a field of a conventional PivotTable. Unlike a Calculated Field, it uses item names from a particular field.
For example, imagine a PivotTable with a Month field containing January, February, and March. A calculated item could be used to combine selected month items in a supported layout.
Calculated Item — Conceptual Example
6. Difference Between Calculated Field and Calculated Item
| Basis | Calculated Field | Calculated Item |
|---|---|---|
| Uses | Source-data fields | Items within a field |
| Example | Sales minus Cost | A calculation involving selected month or category items |
| Purpose | Creates an additional calculated measure in a conventional PivotTable | Creates an additional calculated item in a field |
| Restrictions | Does not support every Excel formula or every PivotTable source type | Has additional restrictions related to items, grouping, and layout |
7. Common Mistakes to Avoid
| Problem | What to Check |
|---|---|
| Formula is not accepted | Check field names and formula syntax. |
| Unexpected Profit total | Check the underlying Sales and Cost fields and the aggregation behavior. |
| Calculated Field option is unavailable | Check whether the PivotTable uses the Data Model or an OLAP source. |
| Calculated Item is unavailable | Check the selected field, grouping, and PivotTable source restrictions. |
| Incorrect percentage result | Check the denominator and apply the appropriate number format. |
8. Practice Tasks
Use a sample Sales dataset containing Product, Sales, Cost, Discount, and Month fields. Complete the following tasks:
- Create a PivotTable summarizing Sales by Product.
- Create a Calculated Field named Profit using Sales minus Cost.
- Create a Calculated Field named Net Sales using Sales minus Discount.
- Calculate a commission of 5% of Sales.
- Format Profit and Net Sales as currency.
- Explore Calculated Items in a conventional PivotTable using a suitable category field.
- Compare the results and verify the Grand Total.
9. Important Points
- Calculated Fields use source-data field names in their formulas.
- Calculated Items use items from a particular PivotTable field.
- Conventional PivotTables support these features subject to Excel's restrictions.
- PivotTables based on the Data Model may require measures instead.
- Always verify calculated results against the source data.
- Use number formats to make currency and percentage results easy to read.
Summary
Calculated Fields and Calculated Items extend the analytical capabilities of conventional Excel PivotTables. Calculated Fields create formulas using source-data fields, while Calculated Items perform calculations involving items within a field.
Understanding the difference, checking source data, and verifying formulas help you build more reliable PivotTable reports.