Excel Pivot Tables Getting Started Guide (2026)
Home›Excel›Pivot Tables›Getting Started
ExcelSub-Guide⏱ 12 min read

Pivot Tables Getting Started Guide

Your first pivot table in 60 seconds — from opening a raw dataset to a working summary. This guide covers everything a beginner needs: data prep, inserting the pivot, dragging fields, changing aggregations, and refreshing when data changes.

Before you start: prep your data

Pivot tables need clean data. Check these five things before inserting:

  1. One header row — column names in row 1, data starts in row 2
  2. Unique column names — no duplicates, no blanks
  3. No blank rows inside the data — a blank row tells Excel the data ends
  4. No merged cells — pivot tables refuse to build on merged data
  5. Consistent data types — dates in the date column, numbers in the number column, no mixing
Convert to a Table first

Press Ctrl + T to turn your data into an Excel Table. Give it a name (Table Design → Table Name → "SalesData"). Pivot tables built on a named table auto-expand when new rows are added. This one habit prevents most refresh headaches later.

Inserting your first pivot table

  1. Click anywhere in your data

    Excel detects the range boundaries automatically.

  2. Insert tab → PivotTable

    Keyboard: Alt + N, then V.

  3. Check the source range

    Excel guesses. If it's an Excel Table, you'll see the table name. If a range, you'll see something like Sheet1!$A$1:$G$5000.

  4. Choose destination

    New Worksheet is safest — keeps the pivot separate from the source. Existing Worksheet only if you have a specific layout planned.

  5. Click OK

    Empty pivot area appears on a new sheet. The PivotTable Fields pane opens on the right.

The Fields pane explained

The pane has two sections:

Top: field list

Every column from your source data. Tick a field to add it to the default area (numeric → Values, text → Rows).

Bottom: four drop zones

  • Filters — pivot-level filter dropdowns above the pivot
  • Columns — fields become column headers
  • Rows — fields become row labels
  • Values — numeric aggregations (the actual numbers in the pivot)

Drag fields between zones freely. Every drag rebuilds the pivot.

Building common pivot layouts

Layout 1: One-dimensional summary

"Total revenue by region."

  • Region → Rows
  • Revenue → Values (Sum)

Result: a simple two-column table listing each region and its total revenue.

Layout 2: Cross-tabulation

"Revenue by region and product."

  • Region → Rows
  • Product → Columns
  • Revenue → Values (Sum)

Result: a matrix with regions down the side, products across the top, revenue in the cells. The most common pivot layout.

Layout 3: Nested rows

"Revenue by region → state → city."

  • Region → Rows
  • State → Rows (below Region)
  • City → Rows (below State)
  • Revenue → Values

Result: hierarchical row groups with subtotals. Great for drill-down reports.

Layout 4: Filtered summary

"Revenue by product, filtered to Q3 only."

  • Product → Rows
  • Revenue → Values
  • Quarter → Filters

Set the Quarter filter to Q3. Only Q3 rows are included in the sum.

Changing aggregations

By default, pivot tables sum numeric fields. To change:

  1. Right-click any value cell → Summarize Values By

    Options: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, Var.

  2. Pick the aggregation

    Sum for revenue, Average for deal size, Count for number of transactions, Max/Min for extremes.

Value Field Settings dialog

Right-click value cell → Value Field Settings for advanced options — custom name, number format, and "Show Values As" percentages and running totals.

Number formatting

Right-click a value cell → Number Format. NOT Format Cells — Number Format is pivot-specific and persists through refresh.

Common formats:

  • Currency — Category: Currency, Symbol: $, Decimals: 0 or 2
  • Thousands separator — Category: Number, use 1000 separator
  • Percentage — Category: Percentage, Decimals: 1 or 2
  • Custom — for millions display: $#,##0.0,,"M"

Sorting and filtering rows

Sort by value

Click the row-label dropdown arrow → Sort → More Sort Options → Descending by → pick your value field. Now rows are sorted by the aggregated value, not alphabetically.

Top N filtering

Row-label dropdown → Value Filters → Top 10. Excel shows the top N by the selected value. Change 10 to 5, 20, or any number.

Manual filter

Row-label dropdown → tick/untick specific values.

Refreshing when data changes

Pivot tables cache data. When source updates:

  • Right-click pivot → Refresh — refreshes just this pivot
  • Data → Refresh All — refreshes every pivot, query, and connection
  • Keyboard: Alt + F5 for current pivot, Ctrl + Alt + F5 for all

Auto-refresh on open

PivotTable Analyze → Options → Data tab → tick "Refresh data when opening the file". Every time the workbook opens, pivots refresh automatically.

Refresh preserves formatting only if you tell it to

PivotTable Analyze → Options → Layout & Format tab → tick "Preserve cell formatting on update" and untick "Autofit column widths on update". Otherwise Excel resets column widths and number formats on every refresh.

Excel Wizard

The pivot table you meant to build

Learning which fields go in which drop zones takes practice. Excel Wizard lets you describe the summary you want in plain English ("top 10 customers by revenue this year with month-over-month change") and it builds the pivot with the right layout, aggregation, and filters.

Install Excel Wizard →

Frequently asked questions

Why is my pivot table showing Count instead of Sum?

Excel picks Count when the field has any text or blank values. Fix the source — clean the column so it contains only numbers. Or right-click the value cell → Summarize Values By → Sum to force it.

How do I add a total column to a pivot table?

Grand totals are on by default. If missing: Design tab → Grand Totals → On for Rows Only, Columns Only, or Both. For row-level "row total" behavior, add the same value field twice and change one's Show Values As.

Can I use a pivot table with data on another sheet?

Yes. When inserting, click into the Table/Range field and select the range from the other sheet. Or better: convert the source to an Excel Table first, then reference the table by name.

My pivot table won't refresh — what's wrong?

Common causes: (1) source range changed and pivot still points to the old range — Change Data Source; (2) blank rows in the middle of source data cutting off the range; (3) the source sheet was deleted or renamed. Check PivotTable Analyze → Change Data Source.