For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

How to create a pivot table in Excel (step-by-step)

PVT.A1 INSERT > PIVOTTABLE
How to create a pivot table in Excel (step-by-step) — Excel example preview

Dragging Region into Rows and Revenue into Values summarizes thousands of transaction rows in five seconds with zero formulas.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Beginner

Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)

Excel Campus - Jon

Open on YouTube (opens in a new tab)
Excel Campus - Jon

Loading preview…

A pivot table summarises thousands of rows — totals, counts, averages, broken out any way you like — without a single formula. You drag field names into boxes; Excel does the arithmetic and rebuilds the whole summary each time you move one.

The skill is almost entirely in two places: getting the source data into the shape pivot tables require, and knowing what the four drop zones do. Everything else is dragging.

Step 1: Fix the source data first

This is where nearly all pivot table pain originates, and fixing it afterwards is much worse than fixing it now.

Your source needs to be flat:

  • One header row, at the very top, with a non-blank name on every column. Two-row headers break it; a blank header is rejected outright.
  • One row per record. Every row is a complete observation — date, region, product, amount.
  • No blank rows or columns inside the block. Excel guesses the range by expanding from the cell you clicked, and a blank row stops it short. Half your data silently disappears.
  • No merged cells. Merging is the single most destructive thing you can do to data you intend to analyse.
  • No subtotals mixed in with detail. A “North Total” row sitting among the North rows gets counted again, and your figures come out double.
  • One kind of value per column. Dates as dates, numbers as numbers. Numbers stored as text will Count instead of Sum, which is the second-commonest surprise after (blank).

Data arriving from another system usually needs a pass first — cleaning data before a pivot table is the checklist, and if the same export arrives every month, Power Query does that pass once and replays it on refresh.

Then make it a Table. Click inside the data and press Ctrl + T. This takes five seconds and prevents the most annoying failure in this whole article — see Step 5.

Step 2: Insert it

Click anywhere inside the data, then Insert → PivotTable. Confirm the range Excel guessed (if it is a Table, you will see the table name instead, which is what you want), choose New Worksheet, and OK.

You get an empty frame and the PivotTable Fields pane: your column headers listed at the top, four boxes below.

Step 3: The four boxes

Box What it does Typical field
Rows One row per distinct value, going down Region, Product
Columns One column per distinct value, across Year, Quarter
Values The number being calculated Revenue, Units
Filters A dropdown limiting the entire table Year, Salesperson

Start with one field in Rows and one in Values — “Region” and “Revenue” — and you have revenue by region. Add “Product” to Columns and you have a cross-tab. Drag a second field under Region in Rows and you get nesting: region, broken down by product within it.

Rearranging recalculates instantly, which makes this the rare part of Excel where poking at it is genuinely the fastest way to learn. Nothing you do here touches the source data.

A note on Filters: slicers are usually better. PivotTable Analyze → Insert Slicer gives you clickable buttons that show what is currently filtered instead of hiding it behind a dropdown that reads “(Multiple Items)”. On anything another person will use, this is worth the extra click to set up.

Step 4: Change how it summarises

By default Excel sums numbers and counts text. To change it, click the field in the Values box → Value Field Settings → pick Sum, Count, Average, Max, Min, or Distinct Count (which needs the Data Model — tick “Add this data to the Data Model” when inserting).

Two things worth knowing in that same dialog:

  • Number Format — set it here, not by formatting the cells. Cell formatting is lost whenever the pivot is refreshed or rearranged; the Value Field Settings format survives.
  • Show Values As — this is the underused one. % of Grand Total, % of Parent Row Total, Difference From the previous period, Running Total In. People build helper columns for these all the time without realising the pivot does them natively.

You can also drop the same field into Values twice — once as Sum, once as % of Grand Total — to get value and share side by side.

Step 5: The things that go wrong

“(blank)” appears as a category. There are empty cells in that column of the source. Decide what blank means — genuinely unknown, or a category like “Unassigned” — fill it in the source, and refresh.

Numbers you expected to sum are counting instead. The column contains text: either numbers imported as text, or one stray non-numeric cell such as N/A. Find it with Ctrl + G → Special → Constants → Text on that column.

Changes to the data do not show. Pivot tables do not update by themselves. Right-click → Refresh, or Alt + F5. If several pivots feed one report, Data → Refresh All does the lot. To make it automatic: PivotTable Options → Data → “Refresh data when opening the file”.

New rows do not appear even after refreshing. The pivot is pointed at a fixed range like A1:E500 and your new rows are at 501. This is the one Step 1 prevents: if the source is an Excel Table, the range grows with the data and refresh picks everything up. Retrofitting it is PivotTable Analyze → Change Data Source, then point at the Table name.

Column widths reset every refresh. PivotTable Options → Layout & Format → untick “Autofit column widths on update”.

The totals are wrong, and too big. Almost always subtotal rows in the source being counted as data. Back to Step 1.

When not to build one

For a single fixed number — “total revenue where region is North” — SUMIFS is faster to write, lives in a cell where you can reference it, and never needs refreshing. A pivot table earns its setup when you want to slice the same data several ways, or explore without knowing in advance what you are looking for.

Pivot tables vs formulas works through where the line falls, and building a pivot table report for managers picks up where this article stops — turning an exploratory pivot into something you can hand to somebody else.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Use PivotTables to explore data quickly while keeping the source table clean and refreshable. For this article, the goal is to practice: Build your first pivot table: the data shape it requires, the four drop zones and what each does, how to change the summary, and the handful of failures that catch everyone the first month.

Practice workbook setup

Prepare a source table with headers, dates, categories, and numeric measures.

Practice workflow

  1. Convert the source data into an Excel Table before inserting the PivotTable.
  2. Build one simple summary first, then add filters, slicers, or grouping.
  3. Change value field settings and number formats so the result matches the question.
  4. Refresh after changing the source data and confirm new rows appear.

Quality checks

  • The source table has no blank header names or merged cells.
  • The PivotTable answers a specific question.
  • Filters and slicers are visible enough for readers to know what they are seeing.

Common mistakes

  • Building pivots from messy ranges instead of clean tables.
  • Forgetting to refresh after source changes.
  • Counting text fields when a numeric sum was expected.

Next actions

  • Add one slicer and one PivotChart to turn the summary into a small report.
  • Create a second PivotTable from the same source to answer a different question.

Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.