Excel Pivot Table Show Text in Values? How It Works

Charts & pivot tables · Updated November 18, 2026

A standard Excel PivotTable cannot display a text field as an ordinary value: when you drag text into Values, Excel usually counts the nonblank entries. To show the actual text, place the field in Rows or Columns, or use a suitable workaround when you need text alongside summarized results.

What a standard PivotTable can show

A PivotTable is designed to group records and summarize them. Its Values area is for calculations such as sums, counts, and averages. When you add a text column—such as a product name, status, or salesperson—to Values, Excel has no general rule for choosing one text item from a group. It therefore uses Count, which reports how many nonblank records are in that group.

For example, suppose a sales table contains an order number, a region, and a delivery status. If you put Region in Rows and Delivery Status in Values, the result will normally count status entries for each region. It will not select a status such as “Shipped” and show it as the value. If a region includes both Shipped and Delayed orders, displaying just one status would be misleading unless you specify which one you want.

That distinction is the key: a regular PivotTable can show text as a label, but its standard Values area summarizes records rather than returning an arbitrary text string. Excel does have ways to display text associated with a group, but they require a different layout, a formula outside the PivotTable, or an advanced Data Model solution.

Why Excel changes a text field to Count

When Excel builds a PivotTable, it checks the source column and chooses a default summary. Numeric data is often assigned Sum; text data is generally assigned Count. Count tells you the number of records containing a value, not the number of distinct text labels. If there are four orders and each has a status, the count can be four even if the statuses are not all the same.

You can see or change the summary setting by opening the field menu in the Values area, choosing Value Field Settings, and looking at Summarize Values By. For a text field, Count is the useful standard summary. Changing the setting to Sum or Average does not make Excel display the words. Options that calculate a maximum or minimum are not a dependable method for retrieving text from a regular PivotTable.

Also check whether data that looks numeric is actually stored as text. An imported quantity such as “12” may be counted instead of summed if the source column contains text values, inconsistent entries, or errors. That is a different issue from wanting a word displayed: convert the source values to numbers if you intend to calculate with them.

Show the text as a row or column label

If your goal is to see the actual labels, the simplest solution is to place the text field in Rows or Columns rather than Values. In the PivotTable Fields pane, drag the field into Rows. Excel groups records by each distinct label, showing the text itself as a category. You can then add a numeric field—such as Quantity or Revenue—to Values to calculate a total for each category.

For example, put Region in Rows, Delivery Status beneath Region in Rows, and Order Number in Values. The PivotTable can show each status as a label and count the orders in each region-and-status group. This preserves the categories instead of pretending that each group has one single status.

To make the result easier to read in Excel for Microsoft 365 on Windows:

  1. Select a cell in the PivotTable and open the PivotTable Design tab.
  2. Choose Report Layout, then Show in Tabular Form if you want each row field in its own column.
  3. If needed, choose Report Layout, then Repeat All Item Labels so row labels appear beside each detail row.
  4. Use the field filters or sorting controls to limit the labels shown.

Compact Form can place multiple row fields in one column, which can make labels look nested. Tabular Form separates them into columns; it does not change the underlying summary rules. The PivotTable Analyze and Design tabs appear when a PivotTable cell is selected. On Mac, the ribbon labels and placement can differ slightly, but the same field-list approach works in current desktop Excel.

Show one related text value beside the PivotTable

Sometimes the group really should have one text answer—for example, each order number has one delivery status—and you want that answer next to a PivotTable summary. A practical option is to keep the text out of Values and retrieve it in an adjacent worksheet column. This is useful for reports where the PivotTable summarizes amounts while a neighboring column displays a description.

For a Microsoft 365 worksheet, if order IDs are in A2:A20, their statuses are in B2:B20, and the PivotTable’s order ID is in E5, enter this in the adjacent cell: =XLOOKUP(E5,$A$2:$A$20,$B$2:$B$20,""). If E5 contains ORD-104 and that ID’s source status is Awaiting parts, the formula returns Awaiting parts. XLOOKUP returns the first matching record, so the result is only appropriate when each order ID has one status or when the first match is the intended rule.

Keep the lookup ranges aligned with the source data and consider converting the source range to an Excel Table so new records are easier to include. A formula beside a PivotTable is not itself part of the PivotTable: after a refresh or layout change, check that the formula still lines up with the intended row. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older versions need a different lookup approach.

Handle groups that contain several different text values

If one PivotTable group contains several statuses, names, or descriptions, there is no single correct value to return unless you decide on a rule. You might want the most recent status, the status from a particular date, a list of all statuses, or a label such as “Mixed.” These are different reporting requirements, and a plain Count cannot answer them.

  • Show every category: Add the text field to Rows, under the field that defines the group. This is the clearest option when each distinct label matters.
  • Show a count by category: Put the text field in Rows and a record identifier in Values, summarized by Count. The result reports how many records have each label.
  • Choose according to a business rule: Define which record should supply the text, such as the latest dated record. A lookup or data-preparation step can then return that record’s label.
  • Combine multiple labels: Use a method designed to concatenate values, such as preparing a grouped result in Power Query. A standard PivotTable does not automatically combine text from its source records into one cell.

Be cautious about showing a representative label without stating the rule. If a customer has several orders with different delivery statuses, displaying one status for the customer can imply that all orders share it. Showing status as a nested row field, or summarizing at order level, avoids that ambiguity.

When the Data Model may help

For a more advanced report, a PivotTable based on the Excel Data Model can use a DAX measure intended to return a text result in a particular filter context. For instance, a measure can return a product name when the current group identifies exactly one product, and a fallback label when the context contains several. This is different from dragging a text column into Values: the measure applies an explicit rule.

This approach is most suitable when you already use relationships or measures in the Data Model and understand what each PivotTable row represents. A text-returning measure still needs a well-defined result for groups with multiple candidates; otherwise the displayed result can be blank or a fallback message rather than the text a reader expects. It is not the simplest fix for a small, ordinary PivotTable.

In Excel for Microsoft 365 on Windows, you can create a PivotTable from a table or range and select Add this data to the Data Model in the creation dialog. DAX measure creation uses the Power Pivot tools when they are available and enabled. Power Pivot authoring is not available in Excel for the web, and availability differs by Excel edition and platform; Mac users should not assume the Windows Power Pivot workflow is present. For a basic report, row labels or a neighboring lookup formula are usually easier to maintain.

Common problems and refresh checks

If Excel shows a count when you expected words, first verify which area contains the field. A field in Values is being summarized; move it to Rows to show the labels. If you already placed it in Rows but see fewer entries than expected, the PivotTable may be grouping identical text together. Extra spaces, inconsistent spelling, or different punctuation can also create apparently duplicate labels in the source.

Check the source range when new text does not appear after a refresh. If the PivotTable uses a fixed range, newly added rows may be outside that range. Converting the source data to an Excel Table before creating the PivotTable makes the source expand as rows are added; then refresh the PivotTable to update its results. Refreshing updates the PivotTable from its source, but it does not change the rule that text in Values is counted.

Finally, if a numeric field is being counted unexpectedly, inspect the source column for text-formatted numbers, blanks, or error values. Correct the source data, refresh, and verify the summary setting in Value Field Settings. Do not convert a genuine text label into a number merely to force it into Values; that changes the data rather than solving the display requirement.

Frequently Asked Questions

Can I put text in the Values area of an Excel PivotTable?
A regular PivotTable does not display a text field there as a label; it normally counts nonblank text entries. Put the field in Rows or Columns to show the text, or use a separate method with a clearly defined rule.

How do I stop a PivotTable from counting text?
Move the text field from Values to Rows or Columns in the PivotTable Fields pane. If you need a number, keep it in Values and choose an appropriate summary such as Count; changing Count to Sum will not turn text into displayed words.

Can a PivotTable show the first text value in a group?
Not as a general setting for a standard text field in Values. Use a lookup or a Data Model measure with a deliberate selection rule, and make sure the group cannot contain conflicting values—or define what should happen when it does.

Can I concatenate text from PivotTable rows into one cell?
A standard PivotTable does not automatically join several text entries into one cell. Prepare a grouped, concatenated result with a suitable data-transformation method such as Power Query, or use a formula-based solution designed for your Excel version.

Why does my PivotTable count a number column instead of summing it?
The source values may be stored as text, contain blanks or errors, or have inconsistent data types. Correct the source column, refresh the PivotTable, and check Value Field Settings to confirm the desired summary.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.