How to Create a Pivot Table in Excel (2026)
Pivot tables summarize thousands of rows into an answer in seconds. This guide covers the fastest way to create one, plus the three prep steps that make pivots survive refresh, resizing, and reuse.
⚡ Quick Answer
Click anywhere in your data → Insert tab → PivotTable → OK. Fields pane opens. Drag fields to Rows, Columns, Values. Keyboard shortcut: Alt + N then V.
Before you insert: 3 prep steps
Step 1: Clean the data
Pivot tables refuse messy data. Check:
- One header row with unique names (no duplicates, no blanks)
- No merged cells anywhere in the range
- No blank rows or columns inside the data
- Consistent data types per column (all dates, or all numbers, not mixed)
Step 2: Convert to an Excel Table
Press Ctrl + T. Confirm "My table has headers". Excel converts the range to a proper Table.
Table Design → Table Name → give it a memorable name like "SalesData".
Pivots built on Excel Tables auto-expand when new rows are added. Pivots built on plain ranges do not. This single decision prevents 80% of pivot refresh problems later.
Step 3: Save the file first
Not strictly required, but pivot creation on unsaved workbooks occasionally causes name-collision issues. Save first for a clean start.
Method 1: Standard pivot table (most common)
Click anywhere in your data
Cursor inside the range or table. Excel auto-detects boundaries.
Insert tab → PivotTable
Keyboard: Alt + N, then V.
Confirm the source
Dialog shows Table/Range. If you converted to a Table, it shows the table name. If not, cell references.
Choose destination
New Worksheet (recommended) or Existing Worksheet.
Click OK
Empty pivot layout appears. PivotTable Fields pane opens on the right.
Method 2: Recommended PivotTables
Excel suggests pivots based on your data. Great when you're not sure what to build.
Select any cell in your data
Or select the entire data range.
Insert tab → Recommended PivotTables
Or Home tab → Analyze Data (in some Excel versions).
Preview the suggestions
Excel shows 6-10 layouts with live previews.
Pick one → OK
Pivot inserts with the suggested layout.
Building the pivot: dragging fields
The Fields pane has two sections:
- Top: your source data's field names
- Bottom: four drop zones — Filters, Columns, Rows, Values
Quick example
Data: Region, Product, Date, Revenue. To build "revenue by region and product":
- Drag Region to Rows
- Drag Product to Columns
- Drag Revenue to Values
Result: matrix with regions down, products across, revenue in the cells.
Change the aggregation
By default, numeric fields get Sum. Text fields get Count. To change:
Right-click any value cell → Summarize Values By
Options: Sum, Count, Average, Max, Min, Product, StdDev, Var.
Pick the right aggregation
Sum for revenue. Average for deal size. Count for transaction count.
Format like a pro
Number format that survives refresh
Right-click any value cell → Number Format (not Format Cells). Set currency, thousands separator, decimals. Number Format is pivot-level and persists through refresh.
Report layout
Design tab → Report Layout. Compact (default), Outline, or Tabular. Tabular is cleanest for exports.
Preserve formatting on refresh
PivotTable Analyze → Options → Layout & Format tab. Tick "Preserve cell formatting on update". Untick "Autofit column widths on update".
Common first-time mistakes
Range-based pivots don't auto-expand. When you add rows next week, they don't show up in the pivot. Convert source to Table (Ctrl + T) first.
If you manually select the range and skip the header row, Excel doesn't know field names. Include headers — or better, click a single cell inside the data and let Excel detect the range.
Merged cells anywhere in the source data cause pivot creation to fail. Unmerge before pivoting.
Pivots from a question
Building a pivot means guessing which fields go where, then iterating. Excel Wizard lets you ask the question in plain English ("top 10 products by revenue this quarter with month-over-month change") and it builds the correct pivot in one step.
Install Excel Wizard →Frequently asked questions
How do I create a pivot table in Excel?
Click anywhere in your data. Insert tab → PivotTable → OK. Drag fields into Rows, Columns, Values, or Filters. Keyboard: Alt + N then V.
Where should I put my pivot table — new or existing sheet?
New Worksheet is the safe default. Existing Worksheet only when you have a designed dashboard layout.
Do I need to convert my data to a table first?
Not required, but strongly recommended. Press Ctrl + T first. Table-based pivots auto-expand when data grows.
Can I create a pivot table from data on multiple sheets?
Not directly. Consolidate first via Power Query, or add each sheet to the Power Pivot data model and pivot from the model.