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 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

tw

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.