Session 13 · Phase 2: Excel

Excel Pivot Tables & Calculated Fields

Pivot Tables are the fastest way to summarize thousands of rows into a clear report — the single most-asked Excel feature in data analyst interviews.

⏱ ~2 hrs 📚 Core content 🎯 Highest priority

Learning Objectives

Explain what a Pivot Table is and why it's the go-to summarization tool.
Use the four PivotTable areas: Rows, Columns, Values, Filters.
Group, sort, and filter pivot data, and use Slicers.
Create a Calculated Field (e.g. Profit = Sales − Cost).
Distinguish a Calculated Field from a Calculated Item.
Refresh a Pivot Table when the source data changes.

1. What Is a Pivot Table?

A Pivot Table is a tool that summarizes, analyzes, and presents large datasets — letting you regroup and aggregate data dynamically without writing formulas. It's Excel's version of SQL's GROUP BY.

🌐

Real World: "Give me total sales by region and by product" is a 30-second pivot table, versus a pile of manual SUMIFS formulas. That speed is why interviewers expect you to know it cold.

2. The Four PivotTable Areas

When you build a pivot table, you drag fields into one of four areas:

Area	What it does	Example
Rows	groups data into rows	one row per region
Columns	groups data into columns	one column per product
Values	the numbers to aggregate	SUM of sales
Filters	filters the whole report	show only a specific month

📝

Interview answer: "Rows and Columns are the categories, Values is the metric being summed/counted/averaged, and Filters let me slice the entire table."

3. Creating, Grouping, and Filtering

Create a Pivot Table

Select your data (including headers).
Insert → PivotTable.
Drag fields into Rows, Columns, Values, and Filters.

Grouping

Right-click a date or number field and choose Group — e.g. group daily data into months, or numeric values into ranges.

Sorting & filtering

Sort the Values column (largest-to-smallest) and use the filter drop-downs on row/column headers.

Slicers

Insert → Slicer adds clickable buttons that filter the pivot table visually — ideal for dashboards.

4. Calculated Fields

A Calculated Field is a new column you define using a formula on other fields. The classic example is profit.

-- Calculated Field: Profit = Sales - Cost
= Sales - Cost

To create one: select the pivot table → PivotTable Analyze → Fields, Items & Sets → Calculated Field, name it "Profit", and enter the formula.

💡

Pro Tip: A calculated field is computed after aggregation — Excel first sums Sales and sums Cost, then subtracts. This matters if your source has multiple rows per item.

5. Calculated Field vs Calculated Item

This is a favorite advanced question. The two sound alike but work at different levels.

	Calculated Field	Calculated Item
Operates on	other fields (columns)	specific items (values) within a field
Example	Profit = Sales − Cost	"Pen + Notebook" combined into one item
Creates	a new column/measure	a new item within a field

📝

Rule of thumb: use a calculated field when your formula references other columns; use a calculated item when it references other values in the same column.

6. Refreshing a Pivot Table

A Pivot Table does not auto-update when the source data changes. Refresh it:

Right-click the pivot table → Refresh, or
PivotTable Analyze → Refresh, or
Keyboard shortcut Alt+F5.

Use Refresh All to update every pivot table in the workbook at once.

📋 Stable content — Reviewed: August 2026

7. Interview Questions (with Model Answers)

Pivot Table questions are near-guaranteed in an Excel round. Self-test before revealing.

IQ1. What is a Pivot Table, and why is it useful?

Model answer: "It's a tool to summarize and analyze large datasets without writing formulas. I can drag fields to regroup and aggregate data — like total sales by region — in seconds, and change the view on the fly."

IQ2. What are the four areas/sections of a PivotTable?

Model answer: "Rows, Columns, Values, and Filters. Rows and Columns are the categories, Values is the metric being aggregated, and Filters let me slice the whole report."

IQ3. What's the difference between a PivotTable and a regular table?

Model answer: "A regular table stores raw data. A PivotTable summarizes that data dynamically — I can regroup, aggregate, and filter without changing the source or writing formulas."

IQ4. What is a Calculated Field? Give an example.

Model answer: "A calculated field is a new measure built with a formula on other fields — like Profit = Sales − Cost. Excel aggregates the source fields first, then applies the formula."

IQ5. What's the difference between a Calculated Field and a Calculated Item?

Model answer: "A calculated field references other columns (fields), like Sales − Cost. A calculated item references specific values within one column (items), like combining 'Pen + Notebook' into a single item."

IQ6. How do you refresh a Pivot Table, and why is it needed?

Model answer: "A pivot table doesn't auto-update when source data changes. I refresh it via right-click → Refresh, the Analyze tab, or Alt+F5 — and Refresh All to update every pivot in the workbook."

IQ7. What is a Slicer, and when would you use one?

Model answer: "A Slicer is a set of clickable buttons that filter a pivot table visually. I use it to make reports and dashboards interactive — a user can click a region to filter everything at once."

IQ8. How would you find the top 5 products by sales?

Model answer: "I'd create a pivot table with product in Rows and SUM of sales in Values, then sort the Values column descending and apply a Top 10 value filter set to 5."

IQ9. Why would you use a pivot table instead of formulas like SUMIFS?

Model answer: "Pivot tables are faster, less error-prone, and dynamic. Instead of writing a SUMIFS for every region-product combination, I drag fields once and Excel computes every combination."

Hands-On Project: Build a Sales Pivot

Using a sales table with columns region, product, sales, cost, complete the following.

Steps

Create a pivot table showing total sales by region (region in Rows, sales in Values).
Add product to Columns to show sales by region × product.
Add a Calculated Field "Profit" = Sales − Cost.
Add a Slicer for region and use it to show only "North".
Sort the regions by total sales, largest first.

View Solution / Walkthrough

Select data → Insert → PivotTable → drag region to Rows, sales to Values (set to SUM).
Drag product to Columns.
PivotTable Analyze → Fields, Items & Sets → Calculated Field → Name "Profit", Formula =Sales - Cost.
Insert → Slicer → choose region → click "North".
Right-click a sales value → Sort → Sort Largest to Smallest.

This produces a clean, interactive, drillable summary — exactly what you'd build in a real analyst job.

Key Takeaways

A Pivot Table summarizes large datasets dynamically — no formulas needed.

The four areas are Rows, Columns, Values, and Filters.

A Calculated Field references other columns (e.g. Profit = Sales − Cost).

Calculated Field = columns; Calculated Item = values within one column.

Pivot tables don't auto-refresh — use Refresh / Alt+F5 when source data changes.

Learning Objectives

1. What Is a Pivot Table?

2. The Four PivotTable Areas

3. Creating, Grouping, and Filtering

Create a Pivot Table

Grouping

Sorting & filtering

Slicers

4. Calculated Fields

5. Calculated Field vs Calculated Item

6. Refreshing a Pivot Table

7. Interview Questions (with Model Answers)

Hands-On Project: Build a Sales Pivot

Steps

Key Takeaways

Objective Questions — Test Your Understanding