In a standard Excel PivotTable, you generally cannot put a text field in the Values area and have it return the original text; Excel summarizes that field, usually by counting entries. To display text itself, place the field in Rows or Columns, or use a workaround such as a Data Model measure if you need text combined into one result.
What the Values area is designed to do
A PivotTable groups source records and calculates summaries for each group. The Values area is where those calculations appear: sums of sales, averages of ratings, counts of orders, maximum dates, and similar results. Because these operations expect a value that can be aggregated, a text field does not behave like a label when you drag it there.
For example, imagine a source table with Region, Product, and Status columns. If you put Region in Rows and Status in Values, Excel cannot sensibly add values such as Open and Shipped. It will normally create Count of Status, showing how many nonblank status entries belong to each region. The result is a number, not a status word.
This is different from seeing text beside or inside the PivotTable. Row and column labels are text, and a text field can be displayed there. The key distinction is its placement: Rows and Columns define categories; Values produces calculations for those categories.
Why Excel chooses Count instead of the text
When Excel creates a PivotTable, it chooses a default summary based partly on the data it recognizes in the source column. A column containing text typically defaults to Count. A column containing numeric data typically defaults to Sum. Count is useful because it answers a real question—how many records have a nonblank entry—even when the entries are words.
Count does not mean that Excel has selected one representative word. If a region has three rows with statuses Open, Open, and Shipped, Count of Status returns 3. It does not return Open, Shipped, the first status, or the most common status. A regular PivotTable needs a defined calculation rather than an arbitrary choice of text.
You can inspect or change the calculation by selecting a value in the PivotTable and choosing PivotTable Analyze > Field Settings or right-clicking the value and selecting Summarize Values By. In Excel for Microsoft 365 on Windows, the Value Field Settings dialog lists the available summaries. Changing Sum to Count, Average, or another option changes the calculation; it does not turn the field into a text label.
How to show the original text in a PivotTable
If the goal is to show a descriptive word, move that source field to Rows or Columns rather than Values. For instance, put Region in Rows and Status below Region in Rows. Excel then displays the status categories under each region. This groups records by the text and lets you add a numeric field, such as Order ID or Sales, to Values to count or total records for each combination.
To arrange a PivotTable so that categories are easier to read, select a cell in it and use Design > Report Layout. Show in Tabular Form places each row field in its own column; Repeat All Item Labels can repeat category names on each displayed row. These options improve the presentation of labels but do not make Values return text.
Use this approach when you want to analyze which text categories occur and how often. It can produce a table like Region, Status, Count of Orders. It cannot generally produce one row per region with a single arbitrary status from that region, because a region may have many different statuses.
Check whether numbers are stored as text
A common source of confusion is a column that looks numeric but contains numbers stored as text. For example, imported values such as 125 and 300 may be left-aligned or carry a warning indicator. When added to Values, Excel may count them instead of summing them, or the PivotTable may show an unexpected Count calculation. This is not the same problem as wanting a word displayed as a value: the underlying entries are intended to be numbers, but Excel has not recognized them as numbers.
First inspect the source column, not just the PivotTable. A number format such as Currency or Number changes how a numeric value looks; it does not convert text into a numeric value. Depending on the source, you can select the affected cells and use the warning icon’s Convert to Number option, or use Data > Text to Columns and finish the wizard to coerce consistently formatted numeric text. For recurring imports, set the column’s data type during import or transformation instead of repeating a manual cleanup.
A helper formula is another option. Assume the imported text numbers are in A2:A100, with the first record in A2 and a blank helper column B. In B2, enter =IF(A2="","",VALUE(A2)) and fill it down. If A2 contains the text 125, the formula returns the numeric value 125; if A2 is blank, it returns a blank. VALUE may return an error for entries that are not valid numbers under your regional number conventions, so check for symbols, separators, and nonnumeric text before relying on the converted column.
Build or refresh the PivotTable using the converted helper field, and set it to Sum if that is the intended calculation. A formula that returns a number is different from a formula using TEXT to create a display string: TEXT returns text, so a PivotTable will not sum that result as a number.
Refresh the PivotTable after changing source data
Fixing the source does not always change an existing PivotTable immediately. A PivotTable uses a cached view of its source data, so refresh it after converting values or changing the source table. Select a cell in the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables.
If the field still behaves unexpectedly, confirm that the PivotTable source includes the corrected column and that the field in Values is the corrected field rather than the original one. In the PivotTable Fields pane, remove the old field from Values and add the intended field again. Then open Value Field Settings and choose the required summary.
Mixed columns deserve special attention. A column with mostly numbers but some words, spaces, or error values may be imported or interpreted inconsistently. Clean the source into separate, clearly typed columns—for example, an Amount column containing numbers and a Note column containing descriptions—rather than asking one field to act as both a number and text.
When you need one text result for a group
Sometimes the real requirement is to combine several text entries into one cell for each group, such as listing all comments for a customer. A standard PivotTable does not offer a normal Values summary that concatenates arbitrary text. Putting Comment in Rows creates separate comment categories; putting it in Values counts comments.
For a model-based solution, Excel’s Data Model and Power Pivot can use a DAX measure with CONCATENATEX to join text values in the current filter context. The measure can be placed in a PivotTable’s Values area because it calculates a result for each group. For example, a measure over a Comments column can join comments for the customer represented by each PivotTable row. The exact measure depends on the table and field names, and you should decide whether repeated comments should appear more than once. Data Model and Power Pivot availability can vary by Excel edition and platform, so check that the workbook and installation support them.
For a small, one-off report, it may be simpler to use formulas beside the PivotTable or prepare a grouped result in Power Query before loading it. Power Query is useful when the transformation must be repeatable, but the standard PivotTable Values area still does not become a general-purpose text display area. Choose the method based on whether you need categories, a joined list, or a calculated numeric result.
Common misconceptions and practical choices
- Changing the number format will not convert text. Number formats affect the display of numbers, not the underlying data type.
- Changing Count to Sum only works when the field contains usable numbers. It cannot add words, and numbers stored as text may need conversion first.
- Count is a count of entries, not a text selection. It does not identify the first, last, or most frequent label.
- Putting text in Rows does not remove the ability to calculate. You can group by text labels and summarize a separate numeric field in Values.
For a report, start by stating the intended result. If you need a label, put the field in Rows or Columns. If you need the number of records with a label, use Count. If you need a total, ensure the source is numeric and use Sum. If you need several words joined together in one group result, use a model-based measure or prepare the grouped output with a suitable transformation.
Frequently Asked Questions
Can I display text in the Values area of an Excel PivotTable?
A standard PivotTable does not normally return the original text from a Values field. It summarizes the field, commonly with Count. Put the text in Rows or Columns, or use a Data Model measure for a calculated text result.
Why does my PivotTable show Count instead of Sum?
Excel may recognize the source field as text, or the field may contain a mixture of numbers and nonnumeric entries. Convert intended numeric text in the source, refresh the PivotTable, and select Sum in Value Field Settings.
Can I show the first text value for each group?
Not as a standard text summary in the usual PivotTable Values options. If selecting a particular record is important, define a clear rule for which record qualifies and use a formula, query, or Data Model calculation to implement that rule.
Does formatting a text number as Number fix it?
No. A number format changes appearance, not the stored value. Convert the text to a real number in the source data, then refresh the PivotTable.
How do I combine several text entries into one PivotTable cell?
A regular PivotTable does not concatenate text in Values. A DAX measure using CONCATENATEX in a supported Data Model is one option; Power Query or formulas can be better for a one-time or separately prepared output.
Related
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Online Pivot Table Chart: How to Create, Read, and UseAn online pivot table chart is a visual summary built from data organized in a pivot table. In…
- Excel Box and Whisker Plot X-Axis ExplainedIn an Excel box-and-whisker plot, the x-axis is the horizontal category axis: it identifies the groups represented by…
- Excel Bar Graph Creation: Steps, Tips, and FixesTo create a bar graph in Excel for Microsoft 365 on Windows, select a labeled data range, then…
- Can You Power Pivot Two Excel Sheets? How to Do ItYes. In Excel for Microsoft 365 on Windows, turn each sheet’s data range into an Excel table, add…
- Excel Pivot Chart Multiple Lines: How to Create ThemYes. To show multiple lines in an Excel PivotChart, create a PivotChart from your PivotTable, choose a line…
- How to Add a Trendline in an Excel GraphTo put a trendline in an Excel graph, select the chart, choose Chart Design > Add Chart Element…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.