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

Dragging Region into Rows and Revenue into Values summarizes thousands of transaction rows in five seconds with zero formulas.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.



Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
Excel Campus - Jon
Open on YouTube (opens in a new tab)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
Countinstead ofSum, 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 Fromthe 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.
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
- Convert the source data into an Excel Table before inserting the PivotTable.
- Build one simple summary first, then add filters, slicers, or grouping.
- Change value field settings and number formats so the result matches the question.
- 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.






