📎 This article includes 1 downloadable practice file ↓

A pivot table summarises thousands of rows in seconds — totals by region, by month, by product — without a single formula. It is the most time-saving feature in Excel that many people still avoid because it looks complicated. It is not. This guide builds one from scratch and then covers the settings that turn a basic pivot into a real report.

1. Prepare the data (this decides everything)

• One header row, one record per row, no blank rows or columns, no merged cells.
• One type of data per column — dates in a date column, numbers as numbers.
• Convert it to a Table (Ctrl+T). New rows are then included automatically when you refresh.
• Data in “wide” format with a column per month? Unpivot it first.

2. Create the pivot

1. Click inside the table → Insert → PivotTable → New Worksheet → OK.
2. In the field list, drag Region to Rows, Product to Columns, Amount to Values.

That is a complete cross-tab. Drag fields between areas to look at the data another way — that is the “pivot” in pivot table.

3. Value field settings

Numbers are summed by default, text is counted. Right-click a value → Summarize Values By to switch to Average, Max, Min or Count. Click Number Format in the same dialog to format the whole field once — it survives refreshes, unlike formatting cells.

4. Show Values As — the hidden superpower

Right-click a value → Show Values As:

• % of Grand Total — each region’s share of all sales.
• % of Row Total — product mix within each region.
• Difference From (previous month) — month-on-month change.
• Running Total In (Date) — year-to-date.
• Rank Largest to Smallest — league tables.

5. Group dates, numbers and items

• Dates: right-click a date in Rows → Group → Months and Years (and Quarters).
• Numbers: right-click a quantity → Group → start 0, end 100, by 10 — instant distribution bands.
• Items: select several items (North, East) → right-click → Group → rename the group “Zone 1”.

6. Slicers and timelines

PivotTable Analyze → Insert Slicer adds clickable buttons for a field; Insert Timeline adds a date slider. Right-click a slicer → Report Connections to control several pivots with one slicer — the core of a simple dashboard.

7. Calculated fields

PivotTable Analyze → Fields, Items & Sets → Calculated Field, e.g. Margin = Amount - Cost. Note that calculated fields work on sums: a calculated “Price = Amount / Qty” divides total amount by total quantity, which is usually what you want for averages.

8. Distinct counts

“How many different customers bought in each region?” Tick Add this data to the Data Model when creating the pivot. Then Value Field Settings offers Distinct Count.

9. Layout for reports

• Design → Report Layout → Show in Tabular Form and Repeat All Item Labels — makes the pivot look like a normal table you can copy elsewhere.
• Design → Subtotals → Do Not Show when you only need grand totals.
• Design → Blank Rows for readability.

10. GETPIVOTDATA: formulas that read the pivot

Type = and click a pivot cell — Excel writes =GETPIVOTDATA("Amount",$A$3,"Region","North"). It keeps working when the pivot re-arranges. Replace the fixed text with cell references to build a formatted summary page on top of a pivot. Turn the behaviour off in PivotTable Analyze → Options if you prefer normal cell references.

11. Refresh and common problems

12. Beyond the pivot table

In Microsoft 365, PIVOTBY and GROUPBY create pivot-style summaries with formulas that update instantly — try GROUPBY in the Formula Lab. For millions of rows, load data into the Data Model (Power Pivot) and write measures.