Learn Excel – Text Instead of Numbers in Pivot Table – Podcast 2223

Learn Excel – Text Instead of Numbers in Pivot Table – Podcast 2223

Learn Excel from MrExcel Podcast, Episode
2223: Show Text in a Pivot Table in the Values Area. Hey, welcome back to the MrExcel netcast,
I’m Bill Jelen. Today’s question from Susan, in Melbourne,
Florida. Susan wants to create a pivot table with text
in the values area. She wants to report this code to have original
and revised. She wants to compare those and a pivot table. I know pivot tables don’t do text; pivot tables
are for numbers, you know. And, at first, when she said this, I said,
“What, are you just trying to show yes or no?” Because I have this, you know, weird trick
when you have zeros and ones. We can create a custom number format and change
the zeros and ones. The ones will be yes, negative won’t happen,
Zeros will be no, click OK. You can change the number format in the pivot
table to change the zeros and ones to yes or no. No, Susan actually wants how to report these
values. And, yeah, the answer is just No, you can’t
do it. But, wait. But wait, you can do it if you convert this
to a real table: Ctrl+T, and then Insert, PivotTable, Add this data to the Data Model. Because that enables a brand-new formula language
called DAX– Data Analysis eXpressions. So, we’ll put Market down the left hand side,
and Rep, Version across the top. And instead of Code, I’m going to calculate
a new measure. Alright, so we will, in fact, right-click
and say Add Measure, and it’s going to be called a ListOfCodes. ListOfCodes, all right. And our formula’s going to use the=CONCATENATEX
function. So,=CONCATENATEX now wants to know– the
first thing it wants to know is– the table, and, of course, I just press Ctrl+T, which
means that that’s going to be called Table1. So I start to type Table1 here, and we choose
from the list, and My expression is going to be the Code. So I start to type Code, Table1[Code] and
then the delimiter, what we want between each one, I’m going to put in-quotes, comma, space–
“, ” — like that, close– )– Check DAX Formula– Formula has no errors. Leave the category as General; we don’t want
to report this as Number. Click Ok, and then what we get is this ListOfCodes
is now here, and I can take that and drag it to the Values area. Now, check this out– this is actually, actually
working. So, for each item, we’re seeing what it was. So, in Atlanta, Gary had Fig. Mike had Cherry, and then in the Revised it’s
Fig and Orange, and then the Atlanta Total is reporting all of the values, separated
by a comma– so Fig and Cherry. And, if we take Market out and put Region
in– let’s take Rep out– so, now I’m seeing all of the items that were reported in that
region. Alright, and then down here in the Grand Total,
all of the items again. This is pretty darn cool using the Grand Total. Now, one of my pivot table defaults is, Subtotals,
Include Filtered Items in Totals, and it looks like that is not having an impact. Over here on the far right-hand side we have
a Grand Total there, so down at the bottom, we’re essentially getting a list of everything. This might be a pivot table where you really
don’t need these grand totals. Remove Grand Total, and maybe even you don’t
need these grand totals– right-click and Remove Grand Total. Very cool way using the CONCATENATEX function–
the CONCATENATEX funtion. Let’s take a look at that again: So,=CONCATENATEX,
the name of the table, the column, and then the delimiter is in “, ” It’s always awesome
to see what new things we can get just from choosing that box. Add this data to the data model. That box is discussed in my book, “MrExcel
LIVe, The 54 Greatest Tips of All Time,” along with a lot of other tips. Click that “I” on the top right-hand corner
to check it out. All right, wrap up for today. Susan in Melbourne wants to create a pivot
table that shows text in the values area. Typically, yeah, no. No deal. But it is possible with the DAX formula language. In order to use DAX, you have to format your
data as a table using Ctrl+T, Insert, PivotTable, and then check the box for Add this data to
the Data Model. If you don’t do this, it’s not going to work. Build the row and column areas of the pivot
table, right-click the table name in the fields list and choose Add Measure, and then the
formula is,=CONCATENATEX. Very much like the new TEXTJOIN function. I specify the table, which field, and then
the delimiter, and it works. To download the workbook from today’s video,
visit the URL in the YouTube description. Thanks to Susan for coming to my seminar and
thanks to you for stopping by. We’ll see you next time for another netcast
from MrExcel.

45 thoughts on “Learn Excel – Text Instead of Numbers in Pivot Table – Podcast 2223

  1. If you like this tip, please consider subscribing and clicking the bell icon!
    The article for this video is at: https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/

  2. So awesome – DAX and Power Pivot Data Model really up the level of PivotTable fun : ) Thanks for the CONCATENATEX fun : )

  3. Finally, the Rosetta Stone that we have been looking for: How to get a Pivot Table to speak "text". Thanks!

  4. Cool trick Bill. Thanks. I knew there was a DAX? option for first or latest as measure. I use that in the tooltips in PoweBI. I also use the distinctcount formula which appears when you checkmark "add to datamodel". Thanks again!!

  5. I just had this question 2 hours ago and was still trying to figure it out without using any vlookups! Thanks.

  6. Totally amazing! I would never think it is possible to add text into Values field like this! Thanks for CONCATENATEX !

  7. This is great! Is there a way to display the values under each other by column, rather than beside each other separated by comma?

  8. I don't have an option "To add this data" in the pivot table. That check box doesn't appear when I want to create a new table. Could it be, this is not available on the Excel used on MAC's? Help!

  9. I do everything to the letter and the new item appears in the menu but then when I drag it to the values box it goes through the motions of dropping but then never appears. So while I can see it there I cannot add it to the pivot table ? Any Help

  10. Hey bud, any tip to ignore same text value notif? I cannot refresh the table due to "measure cannot be created due to same name already exist in column"
    Thank you.

  11. This does not work for me. When I add the 2nd item too rows it just stacks them in the same cell on a row. I just want across the top, "customer names" next column "close date" next column "rep" next column "product' Next column "dollar amount" Any help appreciated. 🙂

  12. Hi, I am using excel 2018 in MAC, I cannot find DAX formula language in the PIVOT table options. Please help me.

  13. Thanks Bill for the video. For those having the issue of being able to create a measure but not being able to add it to Values, I found a work around in the comments to an article written by Bill about this same subject: https://sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-tableThere is a restriction on the number of characters that can be displayed. Filter the table to a subset of the data.My specific issue., from my comment on the article:

    I have a sheet with three columns of text. Two could be added to Values as Measures, one could not.
    JL's solution worked. Filtering to a subset of the data allows the 3rd column
    to be added.

    Looking at my dataset, I don't think the restriction is the number of cells with data. I think it
    is the total number of characters. Column one in my data has the most number of
    cells with data, 1936, but only 5624 characters. Column two has 154 data cells
    and 9394 characters. Column three has 1008 data cells and 75143 total
    characters. This is the one that didn't work until I filtered it. I was able to
    expand the filter up to a total character count of 28591. Any additional filter
    items i tried to enable simply didn't work. I checked the box, but nothing was
    added when I pressed "OK". Back in the filter dialog the additional
    item was back to being unchecked. 

    So there is limit on the number of characters than can be displayed using the concatenatex function.
    Something greater than 28591, but not much greater.

  14. ***You can do this without any code or adding 'measures':
    Easiest way is to "FUZZY LOOKUP"; Step 1: create the pivoted data, then create a table name for it, Step 2: Create a table name for your Original data; Step 3: join the desired fields to the pivoted data using fuzzy lookup. DONE. _You can also create a Macro for Table creation.
    Finally; You can also do this quite easily in PowerBI, its literally faster to load and drag, then export. Especially if you have 3 or more sheets/tabs to merge.

  15. Mr.Excel.com I created the measure per the video in Excel 2019 and it won't let me move it into the Values. I tried with a simple table first and it worked fine. Now with a 13 column x 10804 row table it won't work. HELP!

  16. NOTE: I spend much time looking for this option as I opened a .csv dump or .txt dump of data; you will not get a DAX pivot table with CONCATENATEX option until you first save your file as XLSX. If you simply open and pivot a txt or csv file you will go crazy looking for menu items that do not appear.


  18. Hi .. thanks for this.. extremely helpful. I actually used this technique and used an IF statement to convert Marks into Grades in the Pivot Table. Only issue I am finding is that the IF statement has to be static and cannot reference my static values in a list. Also, the Sort Order doesn't stay. e.g. Originally if I sort by Month but after I refresh the data it resets alphabetically even though I am using the "use custom lists when sorting" in the pivot table. Please advise if there is a solution. Thanks!

  19. OK .. I was able to fix the sorting issue within the Pivot Table but when I create a Slicer based on the Pivot Table, the Slicer is not sorted by Months (Jan, Feb .. Dec) .. The Slicer sorts itself Alphabetically. Any help by anyone is appreciated.

  20. This is amazing!!!! Mr. Excel is awesome! I have a question. I have created a measure for my dataset (“Max BreedingCategory by Block”) that reports the MAX breeding category value reported for each species in every block it is observed in “MAXX(DISTINCT(Data_All_Record_Full[CommonName; BlockName]),MAX(Data_All_Record_Full[Breeding_Category_Value_1-3]))”

    In my data I also have the text code for the corresponding data in a column called “Breeding_Category_Value_1-3” where POSS=1, PROB=2, and CONF=3”. I have always made a pivot table and then copied and pasted as text and done a find and replace to change them all to the letter code. Do you know if there would be a way for me to create a measure that would display one distinct letter code for the max breeding category reported? It is not such a big deal to do find and replace with this one as it is 1-3, but for the breeding code, I have to do a find and replace for 27+ codes and every time you have to do this it takes time and is a potential source of error.

    Thank you kindly if you can help, and GREAT VIDEO!!!


  21. Great video.   I got this to work once, but now it is not working.  The data fields are the same.   I create the table, insert pivot, add to data model, add fields, add measure (formula is right, no errors).  But when I try to add my new measure to 'values', it does not add.  At the bottom a 'reading data' message with a status bar appears, but never goes away.   So it never appears on my pivot.

Leave a Reply

Your email address will not be published. Required fields are marked *