Pivot Tables¶

The pivot table is Excel's most powerful analytics feature. It summarises thousands of rows into an interactive table in seconds — no formulas required. If you learn one Excel skill deeply, make it this one.

Learning Objectives¶

Create a pivot table from raw data
Summarise by rows, columns, values, and filters
Change aggregation type and show values as percentages
Group data (dates into months, numbers into bands)
Add slicers for interactivity

What a Pivot Table Does¶

A pivot table takes flat, row-level data and lets you reshape and aggregate it by dragging fields. The same source data can answer dozens of questions without writing a single formula.

Raw data (one row per order):       Pivot table (summarised):
order | region | category | sales    Region   | Electronics | Books | Total
------+--------+----------+------     ---------+-------------+-------+------
O001  | West   | Elec     | 150       West     |     2,400   |   800 | 3,200
O002  | East   | Books    | 80        East     |     1,100   | 1,500 | 2,600
...                                    Total    |     3,500   | 2,300 | 5,800

Creating a Pivot Table¶

Click anywhere in your data (ensure it has headers, no blank rows)
Insert → PivotTable
Choose to place it on a new worksheet
Drag fields into the four areas:

┌─────────────────────────────────┐
│  FILTERS    (slice the whole     │
│             table, e.g. by Year) │
├──────────────┬──────────────────┤
│  ROWS        │  COLUMNS          │
│  (e.g.       │  (e.g. Category)  │
│   Region)    │                   │
├──────────────┴──────────────────┤
│  VALUES                          │
│  (e.g. Sum of Sales)             │
└─────────────────────────────────┘

Changing the Aggregation¶

By default, Excel sums numeric fields and counts text fields. To change:

Click the field in the Values area → Value Field Settings
Choose: Sum, Count, Average, Max, Min, etc.

Show Values As — the hidden gem

In Value Field Settings → Show Values As, you can display:

% of Grand Total — each cell as a share of the total
% of Row/Column Total — share within its row or column
Running Total — cumulative
% Difference From — vs previous period

This turns a raw pivot into an analytical one without any formulas.

Grouping Data¶

Group dates into months/quarters/years¶

Right-click any date in the pivot → Group → select Months, Quarters, Years. Now you can analyse by month without a helper column.

Group numbers into bands¶

Right-click a numeric row field → Group → set the interval (e.g., group order values into £0-100, £100-200 bands).

Slicers — Interactive Filtering¶

Slicers are visual filter buttons that make a pivot table interactive.

Click the pivot table → PivotTable Analyze → Insert Slicer
Choose a field (e.g., Region, Category)
Click slicer buttons to filter the pivot instantly

Connect one slicer to multiple pivots

Right-click a slicer → Report Connections → tick all the pivot tables it should control. Now one slicer filters your whole dashboard — the foundation of an Excel dashboard.

A Complete Worked Example¶

Question: "What's our revenue by region and category, and which region's biggest category is what?"

Insert pivot table from the orders data
Drag Region to Rows, Category to Columns, Sales to Values
Value Field Settings → Sum → format as currency
Add a Year slicer to filter by year
Optionally, Show Values As → % of Row Total to see each region's category mix

In under a minute, you've answered a question that would take real effort in raw data.

Common Pivot Table Gotchas¶

Refresh after the source data changes

Pivot tables don't auto-update. After changing the source data, right-click the pivot → Refresh (or Refresh All).

Blank rows and inconsistent headers break pivots

Your source data needs clean headers and no blank rows in the middle. Convert your range to a Table (Ctrl+T) first — then the pivot auto-expands when you add rows.

"Count" when you expected "Sum"

If a numeric column shows Count instead of Sum, it usually has text mixed in (e.g., "N/A" in a number column). Clean the column first.

Practice Exercises¶

Warm-up 1. Create a pivot table showing total sales by region. 2. Add category as a column field to create a region × category matrix. 3. Change the aggregation from Sum to Average.

Main 4. Group order dates into months and show monthly revenue. 5. Use "Show Values As → % of Grand Total" to show each region's share. 6. Add slicers for Region and Category and connect them.

Stretch 7. Build a mini-dashboard: two pivot tables (revenue by month, revenue by category) controlled by one shared slicer. 8. Group order values into bands (£0-50, £50-200, £200+) and count orders in each.

Interview Questions¶

[Beginner] What is a pivot table and what is it used for?

[Beginner] What are the four areas you can drag fields into?

[Mid-level] How would you show each region's revenue as a percentage of the total within a pivot table?

[Mid-level] Your pivot table is showing "Count of Sales" when you want the sum. What's the likely cause?

[Senior] What are the limitations of pivot tables, and when would you move to Power Pivot or Power BI instead?

Previous: 01-basic-formulas | Next: 03-lookup-functions