Pivot Tables

What Is a Pivot Table?

A Pivot Table summarises a large dataset without writing a single formula. Drag a field to see totals by category; drag another to break it down further. What would take hours of SUMIFS and formatting takes seconds.

Example: A sales dataset with 50,000 rows → Pivot Table shows revenue by region, product, and month in a single table.

Sample Dataset

A         B           C             D           E
Date      Salesperson Department    Product     Revenue
Jan-26    Priya       Finance       Advisory    75000
Jan-26    Raj         Technology    Software    95000
Feb-26    Priya       Finance       Advisory    82000
Feb-26    Raj         Technology    SaaS        110000
Mar-26    Meera       Marketing     Campaign    68000
...

Creating a Pivot Table

1. Click any cell in your data
2. Insert → PivotTable
3. Choose:
   • Table/Range — confirms the data range (auto-detected)
   • New Worksheet (recommended) or Existing Worksheet
4. Click OK

The PivotTable Field List panel appears on the right.

The Field List — Drag and Drop

The field list has four areas:

Area	Function
Rows	Values that become row labels (e.g., Salesperson)
Columns	Values that become column headers (e.g., Month)
Values	What gets calculated (e.g., Sum of Revenue)
Filters	Drop-down filter for the entire pivot table

Basic Pivot Table: Revenue by Salesperson

Drag:

• Salesperson → Rows
• Revenue → Values (auto-sums)

Result:

Row Labels    Sum of Revenue
Meera         68000
Priya         157000
Raj           205000
Grand Total   430000

Two-Dimensional: Revenue by Salesperson by Month

Drag:

• Salesperson → Rows
• Date → Columns (Excel auto-groups by month)
• Revenue → Values

Result:

                Jan     Feb     Mar    Grand Total
Meera                           68000     68000
Priya           75000   82000             157000
Raj             95000   110000            205000
Grand Total     170000  192000  68000     430000

Value Field Settings

Right-click any value cell → Value Field Settings:

• Summarize by: Sum (default), Count, Average, Max, Min, Product, StdDev, Var
• Show values as: % of Grand Total, % of Column Total, Running Total, Rank, etc.

Show Revenue as % of Grand Total:
Priya: 157000/430000 = 36.5%
Raj: 205000/430000 = 47.7%

Common "Show Values As" Options

Option	Shows
% of Grand Total	Each cell as % of the overall total
% of Column Total	Each cell as % of its column
% of Row Total	Each cell as % of its row
Running Total In	Cumulative total within each column
Rank Smallest to Largest	Rank within each column
Difference From	Change vs. a specific item (e.g., prev month)

Grouping

Group Dates

Right-click any date in Rows/Columns → Group:

• Options: Seconds, Minutes, Hours, Days, Months, Quarters, Years
• Select multiple (e.g., Months + Years) → nested grouping

Group by Quarter + Year:
Q1 2026, Q2 2026, Q3 2026...

Group Numbers

Right-click a numeric field in Rows → Group:

• Starting at: 60000, Ending at: 120000, By: 10000
• Creates buckets: 60000-70000, 70001-80000, etc.

Group Text Items

Select multiple row items → right-click → Group:

Select "Finance" and "Accounting" → Group → "Group1"
Rename to "Finance & Accounting"

Sorting and Filtering in Pivot Tables

• Click the dropdown on Row Labels or Column Labels
• Sort A to Z, Z to A, or More Sort Options
• Label Filters: Contains, Begins With, etc.
• Value Filters: Top 10, Greater Than, etc.

Top 10 filter: Click dropdown → Value Filters → Top 10 → Top 10 Items by Sum of Revenue

Slicers — Visual Filters

Slicers are clickable filter buttons — much easier than dropdown filters.

1. Click inside the Pivot Table
2. PivotTable Analyze → Insert Slicer
3. Select fields to create slicers for (e.g., Department, Product)
4. Click buttons to filter

Multiple slicers can be connected to multiple pivot tables on the same sheet:

• Right-click slicer → Report Connections → select which pivot tables to connect

Timeline Slicer

For date fields specifically:

1. PivotTable Analyze → Insert Timeline
2. Select your date field
3. Drag to filter by month, quarter, or year visually

Calculated Fields

Add custom calculations that don't exist in your data:

1. Click inside Pivot Table
2. PivotTable Analyze → Fields, Items & Sets → Calculated Field
3. Name: "Tax Amount"
4. Formula: = Revenue * 0.18

Calculated field appears in the Values area like any other field:
Tax Amount = SUM of Revenue × 0.18 (per row)

Important: Calculated fields always operate on the SUM of the field, not on individual rows. They're best for simple rate-based calculations.

Pivot Table Formatting

Subtotals and Grand Totals

• Design → Subtotals — show/hide subtotals for each group
• Design → Grand Totals — show/hide row and column totals

Pivot Table Styles

Design → PivotTable Styles — choose a colour theme. Banded rows option: alternating colour rows.

Report Layout

Design → Report Layout:

• Compact — default; all Row fields in one column
• Outline — each Row field in its own column with indentation
• Tabular — each Row field in its own column; classic grid look; most useful for exporting

Refreshing Data

When your source data changes, the Pivot Table doesn't update automatically:

• Right-click → Refresh (refreshes this pivot table)
• PivotTable Analyze → Refresh All (refreshes all pivot tables in the workbook)
• Refresh on file open: PivotTable Analyze → Options → Data → Refresh data when opening the file

Extending the Data Source

If you added rows below your original range:

• PivotTable Analyze → Change Data Source → adjust the range
• Best practice: Convert source data to an Excel Table (Ctrl+T) before creating the pivot — the table auto-expands, so the pivot source is always current.

Practical Examples

Example 1: Sales Dashboard by Region and Product

Data: Date | Region | Product | Salesperson | Revenue | Units

Pivot 1 — Revenue by Region and Product:
Rows: Region
Columns: Product
Values: Sum of Revenue

Pivot 2 — Monthly Trend:
Rows: Date (grouped by Month)
Values: Sum of Revenue
Show as: Running Total In = Month

Slicers: Region, Product, Salesperson (connected to both pivots)

Example 2: HR Headcount and Compensation Analysis

Data: Employee | Department | Grade | Salary | Join Date | Status

Pivot Table:
Rows: Department, Grade
Values: Count of Employee, Average of Salary, Sum of Salary

Filters: Status = "Active"

Result:
Department  Grade  Count  Avg Salary  Total Salary
Finance     L1     5      68000       340000
Finance     L2     3      82000       246000
Finance     L3     1      95000       95000

Example 3: Customer Purchase Frequency

Data: Customer | Order Date | Amount

Pivot:
Rows: Customer
Values: Count of Order Date (how many orders), Sum of Amount (total spend)
Value Field Settings → Average of Amount (avg order value)

Sort by Sum of Amount descending → see your top customers

Common Mistakes

1. Pivot Table not picking up new rows

Source data was a plain range, not a Table. When you add rows, the pivot doesn't know. Fix: Convert to Table (Ctrl+T) before creating the pivot, or use Change Data Source to extend the range.

2. Dates not grouping properly

Some dates in the column are text ("27/06/2026" as text). Excel can't group text. Fix: Ensure all dates are actual date values (check alignment — right-aligned = number, left-aligned = text).

3. Calculated field gives wrong results

Calculated field: = Revenue / Units   → divides SUM of Revenue by SUM of Units
→ NOT the same as average revenue per unit at individual row level

For row-level calculations, add a helper column to the source data instead.

4. Value field defaults to COUNT instead of SUM

This happens when there are blank cells or text in a numeric column. Fix: Clean the source data — remove blanks/text from numeric columns, then refresh.

Practice Exercises

1. Create a Pivot Table showing total revenue by department and by month (columns = months).
2. Add a Value Field Settings to show Revenue as "% of Column Total" so you can see each department's share per month.
3. Insert a slicer for Department and connect it to the pivot table. Filter to show only Finance and Technology.
4. Group the date field by Quarter instead of Month.
5. Add a Calculated Field called "Net Revenue" = Revenue − (Revenue * 0.10) representing a 10% commission deduction.

Summary

In this chapter you learned:

• Create: Insert → PivotTable → drag fields to Rows, Columns, Values, Filters areas
• Values: default Sum; change to Count, Average, Max, Min via Value Field Settings
• Show Values As: % of Grand Total, Running Total, Rank — no formulas needed
• Grouping: right-click dates → Group (Months, Quarters, Years); numbers → Group by range
• Slicers: Insert Slicer → clickable filter buttons; connect one slicer to multiple pivots
• Timeline: Insert Timeline for date-field visual filtering
• Calculated Fields: PivotTable Analyze → Calculated Field → custom formula using source column names
• Refresh: right-click → Refresh; use Excel Tables as source for auto-expanding ranges
• Layout: Design → Report Layout → Tabular for a clean export-ready format
• Source data must have no blank rows, no merged cells, and consistent types per column

Next up: Charts & Visualisations — turn your data into compelling visuals with the right chart types.