How to make a pivot table
in Excel, step by step

What a pivot table actually does

A pivot table takes a long list of raw data — hundreds or thousands of rows — and summarizes it into something readable, without you writing a single formula.

Say you have a spreadsheet of every sale your business made this year: date, region, product, amount. Reading through 2,000 rows tells you nothing. A pivot table can answer "total sales by region" or "average order size by product" in about ten seconds, just by dragging two fields into place.

Step 1 — Get your data ready

Pivot tables are picky about how source data is laid out. Before building one, make sure your data looks like this:

Date	Region	Product	Amount
01 Jun 2026	North	Widget A	120
02 Jun 2026	South	Widget B	85
03 Jun 2026	North	Widget A	200

• One header row at the top — no merged cells, no blank rows above it.
• No blank rows or columns inside the data itself.
• Each column has one type of data — don't mix dates and text in the same column.

Step 2 — Insert the pivot table

Step 3 — Build your summary

This is the part that actually does the work — and it's entirely drag and drop, no typing required.

The Fields panel has four boxes: Filters, Columns, Rows, and Values. Here's what each one means in plain terms:

• Rows — what you want to group by, listed down the left side. Drag "Region" here to see one row per region.
• Values — the number you want to calculate. Drag "Amount" here, and it defaults to Sum — exactly what you usually want.
• Columns — an optional second grouping, spread across the top instead of down the side.
• Filters — lets you narrow the whole table down, like showing only one year.

Drag "Region" into Rows and "Amount" into Values, and you instantly get total sales per region — no formula, no SUMIF, nothing typed.

Common beginner mistakes

Forgetting to refresh after editing source data

Pivot tables don't update automatically when you change the underlying data. Right-click anywhere inside the pivot table and choose Refresh, or it'll keep showing old numbers.

Putting numbers in Rows instead of Values

If you drag "Amount" into Rows by mistake, Excel lists every unique amount as its own row instead of summing them — a common, confusing-looking error for beginners.

Source data with inconsistent text

"North" and "north " (with a trailing space) will show up as two separate rows in your pivot table, even though they look identical. Clean your data first.

Keeping it updated

If your source data grows over time — new rows added every week, for example — you have two options:

1. Right-click the pivot table → Refresh, every time you add new data.
2. Convert your source data into an Excel Table first (Insert → Table). Pivot tables built from a real Table automatically expand to include new rows — no manual range editing needed.