Every pivot answers the same question: how much (or how many) of this, broken down by that.
From question to pivot
Every pivot starts with a question in the form “how much X, by Y (and by Z)”. The X goes into Values; Y and Z go into Rows and Columns; anything you want to limit the question to goes into Filters.
| Question | Values | Rows | Columns | Filter |
|---|---|---|---|---|
| Sales by region last year? | Sum of Amount | Region | FY = 2025-26 | |
| Orders by channel and year? | Count of Order | Channel | FY | |
| Profit by category and quarter? | Sum of Profit | Category | FY quarter | FY = 2025-26 |
| Average order value by city? | Average of Amount | City |
Pivot vocabulary: the fields you break down by are dimensions (Region, Channel); the numbers you measure are measures (Amount, Qty). Rows, Columns and Filters take dimensions; Values takes measures.
Five clicks to a first answer
- Click inside the Table. Any cell of tblSales.
- Insert › PivotTable › From Table/Range. Excel fills in tblSales; choose New Worksheet; OK.
- Tick Region. It goes to Rows, because it is text.
- Tick Amount. It goes to Values as Sum of Amount, because it is a number.
- Drag FY to Filters and pick FY 2025-26.
Read the result, then make it readable
First, check the grand total: ₹1,68,29,992 should equal SUMIFS(tblSales[Amount], tblSales[FY], "FY 2025-26"). Then make the pivot readable: a real name, a number format, and the biggest first.
- Rename the value. Click Sum of Amount in the pivot and type “Sales (₹)” in the formula bar's place, or use Value Field Settings › Custom Name.
- Format the numbers. Value Field Settings › Number Format: the Indian format from Book 1, or ₹ lakh with a custom format. Never format the cells directly: it is lost on refresh.
- Sort largest first. Right-click a value › Sort › Largest to Smallest.
- Rename the row heading. Replace “Row Labels” with “Region”.
Sort, filter and top 5
| You want | Do this |
|---|---|
| Biggest first | Right-click a value › Sort › Largest to Smallest |
| Only some regions | Row Labels drop-down: untick the ones you don't want |
| Top 5 products | Row Labels drop-down › Value Filters › Top 10, change 10 to 5 |
| Products with sales over ₹5 lakh | Value Filters › Greater Than |
| Products containing “set” | Label Filters › Contains |
| Clear every filter | PivotTable Analyze › Clear › Clear Filters |
Layouts that read like a report
A new pivot uses compact form: all row fields squeezed into one column. Fine for exploring; awkward for reports and for copying into other sheets. The Design tab changes it in two clicks.
| Setting (Design tab) | Choose | Why |
|---|---|---|
| Report Layout | Show in Tabular Form | Each row field in its own column, with its own heading |
| Report Layout | Repeat All Item Labels | Every row is complete, so it can be copied or filtered |
| Subtotals | Do Not Show, or at the bottom | Fewer lines; totals where readers expect them |
| Grand Totals | On for columns only, usually | A total row, without a meaningless total column |
| PivotTable Styles | A light style | Banding helps; heavy colours don't |
Questions from the worksheet: Pivot question planner
List the questions you answer every month. Plan each pivot before you build it.
Columns in the worksheet: Question · Values (measure) · Rows · Columns · Filters / slicers
What to remember
- Every pivot answers “how much X, by Y”: X in Values, Y in Rows or Columns, limits in Filters.
- Check the grand total against the data, then rename, format and sort.
- Use tabular form with repeated labels for anything that becomes a report.
- Values
- The pivot area for what you measure: sum, count, average.
- Rows / Columns
- The areas for what you break down by.
- Tabular form
- A layout with each row field in its own column.
This article is adapted from the book. Examples marked “Example” use invented names and numbers; company stories are based on public sources listed in the book. For general learning; use your own judgement for your situation.
