How to Make a Pivot Table in Excel (Step by Step)

A pivot table is Excel's answer to "summarize this for me." You point it at a table of rows — orders, tickets, transactions — and drag fields into four boxes; it produces totals, counts, and averages by any grouping you choose, with no formulas. Most people who avoid pivot tables do so because the first one they built showed nonsense. This tutorial builds one that doesn't, then adds the four features that make pivots genuinely useful.

Before you start: the source table

A pivot needs a flat table: one header row, one record per row, no blank rows, no total rows inside the data, no merged cells. This example uses a sales sheet:

Do one thing first: click any cell in the data and press Ctrl+T (Cmd+T on Mac) to turn it into a Table. This is the single most important habit in this guide — a pivot built on a Table grows automatically when you add rows. A pivot built on a plain range doesn't, and "my pivot doesn't show the new data" is the number one pivot complaint.

Step 1: insert the pivot

Click inside the Table → Insert → PivotTable → keep New Worksheet → OK. You get an empty pivot on the left and the PivotTable Fields pane on the right, with your column names at the top and four boxes below: Filters, Columns, Rows, Values.

Step 2: rows and values

Drag Region to Rows and Revenue to Values. You now have revenue by region:

That's a pivot. Everything else is refinement.

If Values shows Count of Revenue instead of Sum, at least one revenue cell is text (see cleaning messy data). Click the field → Value Field Settings → Sum as a temporary fix, but convert the column properly.

Step 3: a second dimension

Drag Product to Columns. The pivot becomes a grid — regions down, products across, revenue in the cells, with grand totals on both axes. Twelve numbers you'd otherwise have written twelve SUMIFS for (see SUMIFS vs pivot tables for when a formula is the better choice).

Drag Product from Columns to Rows beneath Region and you get a nested outline instead of a grid. Both are the same data; pick whichever reads better.

Step 4: group dates by month

Drag Date to Rows. Modern Excel groups dates into Years/Quarters/Months automatically. If it shows individual dates instead, right-click any date in the pivot → Group → pick Months (and Years if the data spans more than one).

Move Region out of Rows and you have monthly revenue — a trend table in three drags. This grouping is something formulas do awkwardly; pivots do it natively.

Step 5: format and name

• Right-click a value → Number Format → Currency. Formatting the pivot's value field formats every cell at once, including new ones after refresh.
• Click the "Sum of Revenue" header and type a better name, like "Revenue".
• Design tab → Report Layout → Show in Tabular Form and Repeat All Item Labels gives you a flat table that can be copied elsewhere without blank cells.

Four features worth learning next

Percent of total

Drag Revenue into Values a second time. Right-click the second one → Show Values As → % of Grand Total (or % of Column Total for share within a region). Now each region shows both its revenue and its share.

Count of distinct things

"How many different products did each region sell?" A normal count counts rows. For distinct counts, check Add this data to the Data Model when you insert the pivot; then Value Field Settings offers Distinct Count.

Slicers

PivotTable Analyze → Insert Slicer → Product. You get clickable buttons that filter the pivot; far friendlier than the Filters box, and one slicer can control several pivots (right-click the slicer → Report Connections).

Sorting and top N

Right-click a region → Sort → Largest to Smallest by revenue. For "top 3 products", click the Product dropdown → Value Filters → Top 10 and change 10 to 3.

The three problems everyone hits

1. New rows don't appear. The pivot reads a fixed range. Fix: make the source a Table (Ctrl+T) and right-click the pivot → Refresh after every change. If the source is already a Table and rows still don't appear, PivotTable Analyze → Change Data Source and confirm it points at the Table name.

2. A "(blank)" row appears. Some source rows have an empty Region. Either fill them in or filter (blank) out — but know that the rows exist, because they're missing from every regional total.

3. Totals are double what they should be. The source data contains subtotal or "Total" rows, and the pivot summed them too. Delete them from the source; a pivot makes its own totals.

When a pivot is the wrong tool

Pivots are for exploring and reporting. They're the wrong tool when a number needs to live in a specific cell of a dashboard (use SUMIFS), when the logic needs auditing cell by cell, or when the question is a one-off you'll never ask again.

For one-off questions, upload the sheet to ChatExcel and ask "revenue by region and product" — the grid comes back in seconds, as does "which region grew most from January to March?", which no pivot layout answers directly. When you want the pivot in the workbook permanently, ask "how do I build that as a pivot table?" and you'll get the field placement for your actual columns.