MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Text in the Values of a Pivot Table

June 24, 2019 - by Bill Jelen

Excel Text in the Values of a Pivot Table. Photo Credit: rawpixel at

Another amazing use for a measure in a Data Model pivot table is to use the CONCATENATEX function to move text into the values area of a pivot table.

In this data set, there is an original and revised value for each sales rep.

The source data has Region, Market, Rep, Version and Code. The code is text. The version is either Original or Revised.

Insert a pivot table and check the box for Add This Data To The Data Model. Drag Rep to the Rows and Version to Columns.

Build a pivot table with Rep down the side, Version across the top. Currently, there are no fields in the Value area yet.

The Grand Totals get really ugly, so you should remove them now. On the Design tab, use Grand Totals, Off For Rows and Columns.

On the Design tab, choose Grand Totals, Off for Rows and Columns.

In the Pivot Table Fields panel, right-click the Table name and choose Add Measure.

Right click the Table1 heading in the Pivot Table Field list and choose Add Measure....

The formula for the measure is =CONCATENATEX(Values(Table1[Code]),Table1[Code],", "). The VALUES function makes sure that you don't get duplicate values in the answer.

The measure name is AllText. The Formula is =CONCATENATEX(Values(Table1[Code],Table1[Code),", ")

After defining the measure, drag the measure to the Values area. In this case, each cell only has one value.

Success! Words are being reported in the values area of the pivot table.

However, if you rearrange the pivot table, you might have multiple values joined in a cell.

This pivot table has Market in column A instead of rep. There are two words per market for original and two words per market for Revised. The Atlanta cell for Original is Fig, Cherry. The Atlanta cell for Revised is Fig, Orange.


A cell may not contain more than 32,768 characters. If you have a large data set, it is possible that this Grand Total of this measure will be more than 32,768 characters. The Excel team never anticipated that a pivot table cell would contain more than this many characters, but thanks to DAX and CONCATENATEX, it can happen. When it does happen, Excel can not draw the pivot table. But - there is no error message - the pivot table simply stops updating until you get rid of the Grand Total or somehow make the largest cell be less than 32,768 characters.

Title Photo: / Unsplash


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.