Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

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.

Remember: Calculated Fields work with source-data fields, while Calculated Items work with items within a field. They are different features and are not interchangeable.
Calculated Field vs. Calculated Item
Calculated Field Sales and Cost Source-data fields Profit = Sales - Cost Formula uses source fields Creates an additional calculated value Calculated Item Items in a Field Example: Product Categories Combine or Compare Items Formula uses field items Calculates a result using selected field items

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
Calculated Field Formula
= 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
Result: The total sales are ₹1,00,000, the total cost is ₹67,000, and the calculated profit is ₹33,000.

3. How to Create a Calculated Field

Follow these steps in a conventional Excel PivotTable:

1
Create a PivotTable

Select the source data and choose Insert → PivotTable.


2
Open PivotTable Analyze

Click inside the PivotTable. Open the PivotTable Analyze tab. In some Excel versions, the tab may have a slightly different name.


3
Open Calculated Field

Select Fields, Items & Sets → Calculated Field, where available.


4
Enter the Name and Formula

Enter Profit as the name and = Sales - Cost as the formula.


5
Click Add and OK

Add the calculated field, confirm the dialog, and verify the new Profit values in the PivotTable.

Calculated Field Dialog — Illustration
Insert Calculated Field Name: Profit Formula: = Sales - Cost Insert Field Available fields: Product Sales Cost Add Cancel Custom teaching illustration — not a real application screenshot.
Important: Calculated Fields are available for suitable conventional PivotTables. PivotTables based on the Data Model or OLAP sources may require measures or other calculation methods instead.

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.
Formula note: Use the actual source field names from your dataset. If your field names contain spaces or special characters, select them from the Calculated Field dialog to ensure the formula uses the correct names.

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
Month Field January February March Calculated Item Combines selected field items Conceptual illustration only
Note: This diagram explains the concept only. Calculated Items are not the recommended way to total dates or arbitrary records. For monthly or yearly totals, use date grouping, filters, or a suitable measure. Calculated Items also have restrictions involving grouped fields and some PivotTable layouts.

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:

  1. Create a PivotTable summarizing Sales by Product.
  2. Create a Calculated Field named Profit using Sales minus Cost.
  3. Create a Calculated Field named Net Sales using Sales minus Discount.
  4. Calculate a commission of 5% of Sales.
  5. Format Profit and Net Sales as currency.
  6. Explore Calculated Items in a conventional PivotTable using a suitable category field.
  7. 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.