Excel 2020: Text in the Values of a Pivot Table

June 25, 2020 - by Bill Jelen

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

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.

Caution

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: rawpixel.com / Unsplash

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.