Basics Updated November 2026

Rows, Columns, Values and Filters

Rows lists one label per distinct value down the left, Columns does the same across the top, Values is where number fields get summed, counted or averaged, and Filters restricts the whole pivot to the items you pick. Drag a field into an area to use it; drag it out to remove it.

Syntax

Field list: Filters (top left), Columns (top right), Rows (bottom left), Values (bottom right)

Part	What it is
`Rows`	Categories down the side, for example Region or Product; several fields nest
`Columns`	Categories across the top; keep this to fields with few distinct values
`Values`	Numbers to aggregate; each field here gets a function such as Sum or Count
`Filters`	Page-level filter shown above the pivot; the table only includes the chosen items

Examples

Rows: Region
Values: Sum of Revenue

Result: A two-column list: each region and its revenue total.

Rows: Region
Columns: Product
Values: Sum of Units

Result: A grid with regions down the side, products across the top and units in each cell, plus row and column totals.

Filters: Status
Rows: Department
Values: Sum of Amount

Result: A dropdown above the table; pick Approved and the department totals only include approved rows.

Common mistakes

The pivot is one giant column of numbers: a number field went into Rows instead of Values, so drag it to Values.
The table is enormous and unreadable: a field with hundreds of distinct values (dates, customer names) is in Columns, so move it to Rows.
A filter is hidden: Filters sit above the pivot and scroll out of view, so look at the rows just above the first header.
Two Values fields show side by side when you wanted them stacked: drag the Σ Values item from Columns to Rows.

Practice Rows, Columns, Values and Filters

These drills check your answer on live data, free, no account needed.

Revenue by regionBuild a pivot that shows total Revenue for each Region.
Region by product gridShow total Revenue with Region down the side and Product across the top.
Approved spending onlyShow total Amount by Category, counting only claims whose Status is Approved.

Questions

What is the difference between Rows and Columns in a pivot table?

Both group the data by a field's distinct values. Rows lists them vertically, Columns horizontally. Use Columns for a field with a handful of values so the grid stays narrow.

Can I put the same field in two areas?

Yes. A field can be in Rows and also in Values (as a Count, for example), and a number field can be in Values more than once with different functions.

What is the Σ Values item?

It appears when you have two or more Values fields. Its position (Columns or Rows) decides whether the value fields sit side by side or one under the other.

Create a pivot tableCreate a pivot table in Excel from a range or table, pick a new worksheet, then drag fields into Rows and Values. Steps, shortcut, data rules and fixes.
Two row fieldsPut two or more fields in Rows to nest categories, choose which field is outer and inner, collapse groups and switch to tabular layout for separate columns.
Multiple value fieldsAdd two or more fields to Values, decide whether they sit side by side or stacked with the Sigma Values item, and show one field twice as a sum and a count.
Filter a pivot tableFour ways to filter a pivot table: the row label dropdown, the Filters area above the table, Value Filters on totals, and slicers. Examples and catches.