Excel Advanced
Intermediate 14 min read Excel Advanced

Pivot Tables and Basic Dashboards

Aggregation without a single SUM formula

Pivot tables summarize tabular data into rows, columns, values and filters without formulas. Slicers, timelines and pivot charts turn them into self-serve dashboards.

pivot tableslicerspivot chartdashboardPivotTablegrouping
Prerequisites: Excel Essential Skills
Before we start

What you will learn

  • Design a PivotTable from rows/columns/values/filters
  • Group dates and values; change aggregation and show-as
  • Wire slicers and timelines into a dashboard
Introduction

What is this?

A PivotTable is a live aggregation engine over range or Table data. You drag fields into four areas — Filters, Columns, Rows, Values — and Excel computes counts, sums, averages and more, with 'Show values as' transforms like % of column total or running total. Slicers and timelines make the pivot interactive dashboards that anyone can use.

Motivation

Why do you need it?

  • Summarizing '500k rows by country, month and channel' takes seconds instead of formulas.
  • Pivots answer ad-hoc questions without writing new queries each time.
  • Slicer-driven dashboards put self-service analysis in non-technical hands.
  • Pivots catch data issues fast: a pivot of a bad column instantly looks wrong.
Think about it

Mental model

Imagine

Sorting a deck of cards into piles on demand

Think of

Pivoting

Each query is 'pile by suit (rows), count how many (values), ignore jokers (filter)'. Re-pile differently for the next question — the deck never changes.

Mechanics

How it works

  • Rows and Columns fields become the grouping levels of the output grid.
  • Values fields are aggregated (SUM/COUNT/AVERAGE) and can be re-based via 'Show values as'.
  • Filters restrict the whole pivot; date grouping buckets days into months/quarters/years.
  • Slicers connect visually to one or many pivots and update them instantly.
  • PivotCharts live on the same data model and stay in sync with pivot state.

Pivot fields map to output regions.

Source range/Table
Filters → Columns
Rows
Values
Slicers
Hands-on

Examples

A classic revenue pivot

A classic revenue pivot
// Data: Sales[date, region, channel, amount]
Rows:    region,  channel
Columns: month (group date by month)
Values:  SUM(amount), shown as % of parent total
Filter:  amount > 0
Slicer:  region (multi-select)

Dragging these fields instantly produces a regional × month revenue matrix, with share-of-total values and a region slicer for interactivity.

Real world

In production

  • A weekly ops review pivot: sessions by platform and version, refreshed from a raw Table.
  • Finance: a budget vs actual pivot comparing two periods with a 'Show values as' difference.
  • Self-serve dashboards: a slicer-driven workbook executives flip through in meetings.
Guidelines

Best practices

  • Make the source a Table or named range so new rows appear on refresh.
  • Use 'Show values as' (running total, % of total) instead of stacking helper columns.
  • Rename pivot value fields to human labels ('Revenue ($)') before presenting.
  • Keep values numeric: aggregation of text gives counts that confuse everyone.
Watch out

Common mistakes

  • Grouping text dates that are stored as text — months won't group.
  • Forgetting to refresh (Alt+F5) after source data changes.
  • Blank dates/regions producing an 'empty' row in output.
  • Stacking multiple aggregation types without relabeling the value fields.
When things break

Debugging tips

  • Unexpected totals? Check for blanks and duplicates in the source first.
  • A month won't group? Confirm the source column contains true dates.
  • Pivot numbers differ from SQL? Compare the source grain and filters exactly.
  • Disappearing rows after refresh? The source range shrank — point the pivot at the Table, not the range.
Interviews

Interview checkpoint

Practice

Hands-on lab

Pivot a Sales Table and Slice a Dashboard
25 min · intermediate
In progress
Objectives
  • Create a pivot summarizing revenue by region and month
  • Add a second pivot with % of total values
  • Connect slicers and verify interactivity

Pivot a Sales Table and Slice a Dashboard

25 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Which pivot field area holds the aggregation you want to compute?

2

A pivot built on a plain range automatically includes new rows you add later.

Go deeper

Official documentation

Microsoft — create a PivotTable
docs