A pivot table summarises thousands of rows in seconds without a single formula. It is often the fastest way to answer "how much, by what" questions, and it is asked about in almost every Excel interview.
Prepare the data first
Pivot tables work best on clean, tabular data:
- One header row, with a unique name for each column.
- One record per row; no merged cells, subtotals or blank rows.
- Consistent values ("North", not a mix of "North" and "north ").
- Convert the range to an Excel Table (Ctrl + T), so new rows are included when you refresh.
Build your first pivot
- Click inside the table and choose Insert โ PivotTable.
- Drag Region to Rows.
- Drag Amount to Values (it becomes "Sum of Amount").
- Drag Product to Columns.
- Drag Year to Filters.
You now have sales by region and product, filterable by year.
The four areas
| Area | What it does |
|---|---|
| Rows | Categories listed down the side |
| Columns | Categories across the top |
| Values | Numbers to summarise (sum, count, averageโฆ) |
| Filters | Restrict the whole pivot to certain values |
Value settings
Click a value field โ Value Field Settings:
- Summarise by: Sum, Count, Average, Max, Min, Distinct Count (when the data is added to the Data Model).
- Show values as: % of Grand Total, % of Column Total, % of Row Total, Difference From, Running Total In.
"% of Column Total" answers "what share of each product's sales came from each region?" without any formulas.
Group dates and numbers
Right-click a date in Rows โ Group โ choose Months, Quarters and Years. For numbers, group into bands (for example order sizes in steps of 1,000).
Calculated fields
PivotTable Analyze โ Fields, Items & Sets โ Calculated Field. Example: Avg Price = Amount / Qty. Calculated fields work on the summed values, which is usually what you want for ratios.
Slicers and timelines
Insert โ Slicer adds clickable filter buttons; a Timeline adds a date slider. Connect one slicer to several pivots (Report Connections) to build a simple interactive dashboard.
Refresh
Pivot tables do not update automatically. Right-click โ Refresh, or Data โ Refresh All. If new rows are missing, the source range probably did not include them; using an Excel Table prevents this.
Common mistakes
- Count instead of sum: if a number column contains text or blanks, Excel may default to Count. Fix the data and change the setting.
- Text numbers: amounts stored as text will not sum.
- Averages of averages: averaging a column of already-averaged values gives the wrong overall average when groups differ in size.
- Hard-coding pivot results into other formulas with cell references that break when the pivot changes. Use GETPIVOTDATA or rebuild the reference.
The same idea in SQL and pandas
A pivot table is a GROUP BY with an optional pivot. In SQL: GROUP BY region, product with SUM(amount). In pandas: pd.pivot_table(df, index="region", columns="product", values="amount", aggfunc="sum"). Practise the SQL version in our GROUP BY set and the pandas version in merge, pivot and reshape.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: