Layout and values Updated November 2026

Text in the Values area

The Values area only aggregates, so a text field there becomes Count. To see the text itself, add the field to Rows (Tabular layout shows it as a column), or add the data to the Data Model and write a measure like CONCATENATEX(Table, Table[Status], ", ") in Power Pivot.

Syntax

Rows: Order ID, Customer, Status (Tabular Form) with no Values, or Data Model measure =CONCATENATEX(Table1, Table1[Status], ", ")

Part	What it is
`Rows with Tabular layout`	Simplest way to list text fields side by side
`CONCATENATEX measure`	Joins the text values of each group; needs the Data Model (Power Pivot)
`Max / Min`	Works on dates and numbers but not on text in a standard pivot

Examples

Rows: Ticket ID, Assignee, Priority
Design > Report Layout > Tabular Form; Subtotals off; Grand Totals off

Result: A clean list of tickets with the assignee and priority as text columns, one ticket per row.

Data Model > Measures > New Measure: Statuses := CONCATENATEX(VALUES(Tickets[Status]), Tickets[Status], ", ")
Rows: Assignee. Values: Statuses

Result: Each assignee with a comma-separated list of the distinct statuses on their tickets.

Rows: Product. Values: Count of Status

Result: A number per product, which is all a standard pivot can do with a text column.

Common mistakes

You dragged Status to Values hoping to see Approved or Rejected: Excel shows Count of Status; put it in Rows or Columns instead.
CONCATENATEX is not available: the pivot is not on the Data Model; rebuild it with Add this data to the Data Model ticked.
The text list repeats the same value: wrap the table in VALUES() to get distinct values before concatenating.

Practice Text in the Values area

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

High priority workloadCount tickets by Team with Status across the top, but only tickets whose Priority is High or Urgent.

Questions

Can a pivot table show text in the values area?

Not directly in Excel. It will count the text. Use Rows for a listing, or a Data Model measure for concatenated text.

How do I show text values in a Google Sheets pivot table?

The same limit applies; put the field in Rows. Google Sheets has no CONCATENATEX, so use a QUERY or TEXTJOIN formula outside the pivot.

Data Model pivot tableWhat Add this data to the Data Model does: relate several tables without lookups, get Distinct Count and write DAX measures. Also what you lose by using it.
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.
Sum, Count and Average in a pivot tableChange a pivot table value from Count to Sum or Average in Value Field Settings, show total, count and average side by side, and fix a column that counts.