Layout and values Updated November 2026

Two row fields

Drag a second field into the Rows area under the first; the pivot nests the inner field under each item of the outer field with a subtotal per outer item. Drag the fields up or down in the Rows list to change which one is outer.

Syntax

Rows: Region, Rep (outer first, inner second). Values: Sum of Revenue

Part	What it is
`Outer field`	The top item in the Rows list; appears once per group with a subtotal
`Inner field`	Listed beneath each outer item
`Expand / Collapse`	The minus and plus buttons next to outer labels; Expand Entire Field is on the right-click menu

Examples

Rows: Region, Rep
Values: Sum of Revenue

Result: East with its reps indented beneath it and an East Total, then North, and so on.

Rows: Department, Level
Values: Average of Salary

Result: Average pay for each level inside each department, with a department average on the subtotal rows.

Rows: Channel, Customer Type
Values: Sum of Fees
Design > Report Layout > Show in Tabular Form

Result: Channel in column A, Customer Type in column B, Fees in column C, like a normal table.

Common mistakes

The nesting is backwards (reps with regions under them): drag Region above Rep in the Rows list.
Both fields share one column and the inner one is indented: that is Compact layout; switch to Tabular or Outline for separate columns.
Subtotals clutter the view: Design, Subtotals, Do Not Show Subtotals.
You cannot see every rep at once: right-click the outer label and choose Expand Entire Field.

Practice Two row fields

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

Region, then repShow total Revenue by Region, and within each region by Rep.
Pay by department and levelShow the average Salary by Department, and within each department by Level.
Channel, customer type and feesShow total Sales and total Fees by Channel, and within each channel by Customer.

Questions

How do I show two fields side by side in a pivot table?

Add both to Rows and set Design, Report Layout, Show in Tabular Form. Each field gets its own column with the outer label on the first row of each group.

How many row fields can a pivot table have?

Many; the practical limit is readability. Three levels is usually the most that stays useful.

Tabular layoutSwitch a pivot table to Tabular Form so every row field gets its own column, compare Compact, Outline and Tabular, and make Tabular the default layout.
Repeat row labelsFill the blank cells under an outer row label so every row shows its region: Design, Report Layout, Repeat All Item Labels, plus the per-field option.
Remove grand totals and subtotalsTurn grand totals off for rows, columns or both and remove subtotals from nested fields on the Design tab. How to bring them back and show subtotals on top.
Rows, Columns, Values and FiltersThe four pivot table areas: Rows make the row labels, Columns spread categories across the top, Values hold the numbers, Filters restrict the whole table.