Theory
Ten thousand rows, one drag
Meera's chain now logs 10,000 sales rows. She asks Aryan: 'Total sales per category, per shop, per month.' Writing SUMIFS for every combination would take hours and dozens of formulas.
Aryan does it in thirty seconds, with no formulas at all, by dragging fields into a PivotTable.
The PivotTable is the single most powerful feature in Excel for summarising data, and the reason 'know Excel' on a job ad usually means 'know pivots'. It is also, secretly, the same idea as SQL's GROUP BY from your database unit. Let us build one.
Theory
Sorting a huge pile into labelled trays
Imagine 10,000 sales slips in a heap. A PivotTable is an assistant who, on command, sorts them into labelled trays: 'one tray per category, and within each, one per shop, and add up the amounts'. Ask for a different arrangement ('by month instead') and the assistant re-sorts instantly. You never touch a slip; you just say how you want them grouped, and the totals appear. Reshaping is a drag, not a rewrite.
At a glance
The four PivotTable areas
| Area | Holds | Effect |
|---|---|---|
| Rows | A category field | Groups down the side (per category) |
| Columns | A category field | Groups across the top (per month) |
| Values | A number field | The aggregate (sum/count/average) |
| Filters | A field | Page-level filter for the whole report |
Follow along
Build a sales-per-category pivot
- Click the data (ideally a Table), Insert > PivotTable Excel creates a blank pivot with a field list.
- Drag Category into Rows Each category becomes a row.
- Drag Month into Columns Each month becomes a column.
- Drag Amount into Values It sums automatically, giving totals per category per month, no formulas.
Quiz
Aryan changes some sales figures in the SOURCE data. His PivotTable still shows the old totals. Why, and what does he do?
- A pivot does not auto-update; he must Refresh it (right-click > Refresh)
- The pivot is broken and must be rebuilt
- PivotTables never show source changes
- He must delete and recreate the source
Show the answer
A pivot does not auto-update; he must Refresh it (right-click > Refresh)
A PivotTable takes a snapshot of the source and does not recalculate live like a formula, so after changing source data you must Refresh it (right-click > Refresh, or Refresh All). Building the pivot on a Table (Ctrl+T) also ensures new rows are included on refresh. Forgetting to refresh is the number-one pivot mistake, the report silently shows stale numbers.
Think first
The database idea returns
In BCA105 you learned SQL's GROUP BY, which produces one aggregate per group. How is a PivotTable the same idea, and what does it add that a single SQL query does not?
Show the answer
A PivotTable groups rows by a field and aggregates each group, exactly what GROUP BY does ('SUM(amount) per category'). What it adds is interactivity: you reshape the grouping by dragging fields, add a second dimension in columns, and filter with slicers, all without rewriting a query. It is GROUP BY you can play with by hand. Recognising this connection means you already understand pivots conceptually; you are just learning the drag-and-drop interface.
Theory
Slicers, timelines and Flash Fill
Around the pivot are power tools:
- Slicers: big clickable button filters (click 'Dairy' to filter the whole pivot to dairy). Far friendlier than filter dropdowns, and the heart of interactive dashboards.
- Timeline: a date slider to filter by month/quarter/year visually.
- Flash Fill (Ctrl+E): not a pivot tool, but magic, type one example (e.g. the first name from a full name), and Excel detects the pattern and fills the whole column. It reads your intent from a single sample.
Watch out
Where marks leak
Not knowing the four areas (Rows, Columns, Values, Filters) and that you build/reshape a pivot by dragging fields (no formulas). Forgetting a pivot needs Refresh after source changes (it does not auto-update). Confusing slicers (button filters) with timelines (date sliders). And missing Flash Fill (Ctrl+E) as pattern-based auto-fill. The pivot is the elective's richest topic; know the areas, refresh, and the interactive tools.
Theory
Pivots are the engine of dashboards
A PivotTable plus a PivotChart plus slicers is basically a dashboard: click a slicer, and the summary and chart update together. That is exactly the next lesson, turning pivots and charts into a one-page interactive report Meera can explore herself. You have just learned the engine; next you build the cockpit. Next: creating interactive dashboards with KPIs and sparklines.
Summary
Key takeaways
- A PivotTable summarises a large table by dragging fields into Rows, Columns, Values and Filters, no formulas.
- Reshape the report by dragging; it is the interactive version of SQL GROUP BY (BCA105).
- A pivot takes a snapshot: Refresh it after source data changes (build on a Table so new rows are included).
- Slicers are clickable button filters; timelines are date sliders, both make pivots interactive.
- Flash Fill (Ctrl+E) auto-fills a column from one example by detecting the pattern.
- Memory hook: an assistant sorting slips into labelled trays, re-sort by dragging.