How to Display Text Instead of Sum or Count in an Excel PivotTable
Question details
The user needs a way to display original text strings in the Values area of a PivotTable instead of the default numerical aggregations like Count.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering data by specific categories (like a person's name) and needing to view the associated text data for that person in a single PivotTable row.
- Observed behavior
- When text fields are dragged into the Values area, the PivotTable automatically defaults to summarizing them as a 'Count', replacing the actual text with numbers.
Ensure your source data is formatted as an official Excel Table (press Ctrl+T). This ensures that any formulas, Data Model queries, or custom workarounds will update dynamically when new data is added.
Use the Data Model and a DAX Measure
By adding your source data to the Excel Data Model, you can write a custom DAX measure to concatenate and display text strings in the values area.
Standard PivotTables cannot display unaggregated text natively. However, Excel's Data Model feature unlocks DAX (Data Analysis Expressions), which includes specific functions for text manipulation.
Select your source data table, go to the 'Insert' tab, and click 'PivotTable'. In the dialog box, check the box labeled 'Add this data to the Data Model' before clicking OK.
In the PivotTable Fields pane, right-click on your Table name at the top and select 'Add Measure'.
Name your measure (e.g., 'TextValue'). In the formula box, type: =CONCATENATEX(Table1, Table1[YourTextColumn], ", ") - adjusting the table and column names to match your data. Click OK.
Drag your newly created measure into the 'Values' section of the PivotTable. The text will now be displayed instead of a count.
Use Formula Alternatives (TEXTJOIN and FILTER)
If you don't strictly need a PivotTable, you can build a dynamic summary table using modern array formulas that handle text perfectly.
Use Power Query to Group Text Data
Power Query can summarize your data before it even reaches a worksheet, combining text values based on grouped categories.
Analyze Data Seamlessly with WPS Office
While standard PivotTables aggregate text into counts across all spreadsheet tools, WPS Office provides powerful dynamic array functions like TEXTJOIN and FILTER, allowing you to build custom text-based summary tables effortlessly. WPS Office is a highly compatible, free alternative to Microsoft Office.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open your Spreadsheet: Launch WPS Spreadsheet and easily open your existing .xlsx files without losing formatting.
- 3. Apply Array Formulas: Utilize built-in functions like TEXTJOIN and FILTER to easily group and summarize your text data exactly as needed.
Frequently Asked Questions
Why does my PivotTable show a count instead of my text?
PivotTables are fundamentally designed for mathematical data analysis. When a non-numeric (text) field is placed into the Values area, the program cannot sum or average the data, so it defaults to counting the number of text entries.
Can I use Conditional Formatting to display text in a PivotTable?
Yes, as a visual workaround, you can apply custom number formatting (such as ;;;"Your Text") to display a static text string based on a numerical value. However, this will only show predefined text and cannot dynamically pull varying text strings from your source data.
Will these workarounds slow down my Excel workbook?
Using the Data Model (DAX) or Power Query is highly efficient and optimized for large datasets. However, using thousands of complex array formulas (like TEXTJOIN combined with FILTER) on very large datasets may cause slight performance slowdowns.
