If you like this tip, please consider subscribing and clicking the bell icon! The article for this video is at: www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/
You can do this without any code or adding 'measures': its not at pretty but super quick. • drag "Markets data" to the 'Rows' area • drag "REP" to the 'Rows' area as well GO TO RIBBON --> DESIGN --> You will see "REPORT LAYOUT" Change the Report Layout to "Tabular Form" or "OUTLINE FORM" ; then Click "Subtotals" and turn off subtotals. your can also remove 'blank lines' from this ribbon as well. You can also do this quite easily in PowerBI, I do this all the time in PBI its literally faster to load, drag, and export then it is to create tables and code. even better if you have multiple sheets and tables to merge. the second Easiest way is to "FUZZY LOOKUP" create the pivoted data, create a table out of your pivot data, then join the text fields to the pivoted data using fuzzy lookup.
Just found your channel. Liked and subscribed. I’ve been using ConcatX to show the residents census status for each period (use tabular form) for each resident. I added row grand total in text. I want to add column total in value for each period, how do I go about it?
@@MrAlivallohow do I add count for column grand total in value for each period for all the residents? I have. Row grand total in text for census status for each period (in column in tabular format and resident names in rows) for each resident? Please help. Thank you!
@@sallyho146 TO add a COUNTOF something, create a new column. Use the MAX function to retun MAX of TOTAL COUNT. [Count of TOTAL] and [Count of SUM of TOTAL] will be calculated differently in DAX, which is why they have (COUNTX) in DAX. Everything else you mentioned is just dragging the data to the proper field in the pivot GUI. Not sure but, I think you would actually want to just "Reference" the [TOTAL Cell] for that column, and do a HIPSTER pivot...which is what I call a pivot of meta data, or a pivot of calculated pivot numbers, commonly the TOTALS data. Essentially you would reference all the total columns in another sheet then pivot again. Excel will get very slow very fast doing this. But its Easily done in PowerBI with a Function and a 'Card' for that. If working with Big census data, I'd use PowerBI instead. An Example: COUNT of 'Grand Total' will give you a number like 5, meaning there will be 5 COUNTS or 5 Grand Totals. To get this I would create a column and use the MAX function to get the max number of COUNT of that [melon], then reference that number in the pivot table. You may want to post your question to to Quora, I have 14+ years of excel answers on there with 178k followers. However; I'm not clear on this question; the pivot table in the video is (region) and (type), [east] and [melon] for example. The video is about showing the "String Value" in the Pivot table instead of the Total Count or Total Sum, which are numbers. @MrExcel.com; utilizes the Excel Engine to Concatenate, and to accomplish this. There are many ways to accomplish this, I prefer another method out of scope for this reply. If this is mission critical to your JOB, I might be open to a screenshare. Let me know and best of luck.
My table has mostly number data and 1 column of free text. Is your method able to work with both in a pivot table? When I use your method, I am then unable to change the numbers values from count to sum or average in the field settings. Any help is appreciated!
Thank you gorflunk. I used to put 1's and zero in the pivot table and then use a custom number function to get Yes and No to appear. But this DAX trick makes it far more versatile.
I thought this was the answer to my Excel dreams until it didn't work! I was able to get through all the steps, but when I tried to add the "list" to the values section it doesn't work. Can you help, am I doing something wrong?
change: =CONCATENATEX(Table1,Table1[Code],”, “) to =CONCATENATEX(Values(Table1[Code]),Table1[Code],", ") The VALUES function returns a new table with the unique values found in a column. This worked well for me.
Hi there this is brilliant! It was years that I had been trying to do it! I just wanted to ask you whether is possible to separate the values with a line break rather than a comma? I would really appreciate your help on this :) Thanks in advance
I think you might have found the answer by now. If not, find below the solution. character 10 or represent line break in excel. So, use character 10 or in place of comma.
Thanks it's help me so much, but i'd got some errors likes columns had been showed just 3 columns and nos i have more than 10 columns, would u mine to solve this? Thanks
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.
I found the answer to this problem in the comments here: sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table Basically, the combination of all of the values in the Grand Total row may exceed Excel's max character limit. Removing the Grand Total row before putting the measure in the Values box works!
Hi. This works in excel for MAC? When I add the pivot table, don´t show the option "add this data to the Data Model". There is another way? Thanks. Tiago
No. Nothing on this channel will work with Excel for the Mac, Excel for the iPhone, Excel for Android, Excel for Windows NT, Excel Online, Excel for the Commodore 64. The Data Model is limited to the Windows 32 version of Excel.
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.
Great formula, thanks. One question, how it's possible to do the same thing but it will display only unique values ? I have tried to incorporate DISTINCT into the formula but it didn't work no matter where have I put the Distinct. Thank you!
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.
Yes. After the video was published, I found a workaround to remove duplicates. Look at the video description. There is a link to an article about how to remove the duplicates.
For some reason, I keep getting an error message that reads "This PivotTable field isn't available because it exceeds the total number of characters that a cell can contain" whenever I try to drag the new measure into the values section. Is there a fix for this by chance?
A cell can’t contain more than 32768 characters. Try removing the grand total … this cell often contains all text from the column. Also there is a better formula linked in the description to remove duplicate values using VALUES()
Can this be done in Office 365? We have Office 2013 at my office, but a coworker needs to be able to do exactly this. I read in other comments that Power Pivot in 2013 does not work for this either.
The DAX formula language in Excel 2013 does not include CONCATENATEX, so this will not work in 2013. If you happened to own Office 2013 Pro Plus (but not Office 2013 Professional), there is an Power Pivot add-in that lets you create DAX measures. But without CONCATENATEX in the formula language, that won't help you. Eight years ago, DAX and Power Pivot were basically version 1 features. This is a case where upgrading to capture the eight years of progress might make sense. You would also get access to more features in Power Query.
There is a limit of 32768 characters in any cell in a pivot table. If you go over that, the pivot table will not render. I’ve seen cases where removing the grand total row and the grand total column fixes the problem.
MrExcel, Thank you for the tip, it is really useful, however, it is not working for me, if I would like to use it fore more than 10K rows. Symptom: It does not want to make the drag and drop to the value field. I had to split my dataset, to make it work. Do you have any idea what to do with 10K+ rows? (I did not tested out what could be the max, but it working with 2K records, but not with 3K)
There is a newer formula shown at www.mrexcel.com/excel-tips/excel-2020-text-in-the-values-of-a-pivot-table/ By wrapping the first argument in the VALUES() function, you can eliminate the duplicate text in a cell.
The Add Measure is not available in Excel 2013. It is not there for any version of Excel on a Mac. If you are in Excel 2016 or later, did you check the "Add to Data Model" box when creating the pivot table?
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
I found the answer to the this problem in the comments here: sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table Basically, the combination of all of the values in the Grand Total row may exceed Excel's max character limit. Removing the Grand Total row before putting the measure in the Values box works!
It is working for me. I was initially concerned that they would give me the underlying date serial numbers, but it seems to work. However... it would be much better if they could summarize the dates, something like 12/1/2021-12/15/2021 instead of listing all 15 dates!
The instruction worked great. I was able to get it done. However, the result showed multiple duplicated data. How can I make it display just unique value?
It is difficult to diagnose without seeing the DAX Measure. If the data is not confidential, send me the workbook. My e-mail is listed in the "About" tab just below the video.
aha - it can be done, but it is tricky. 1) Edit the measure in the video. Where I have ", " as the third argument, delete the comma and the space. 2) With the insertion point sitting between the two quotation marks, press Shift+Enter. You will see the second quotation mark more to a new line 3) Add the measure to the pivot table. You will see MelonGuavaBananaDate and be disappointed. 4) Select the values area from the pivot table (For me, it is B5:C8) and turn on Wrap Text You will now see Melon Guava Banana Date The Shift+Enter is completely foreign. In Excel, we would normally use CHAR(10) or Ctrl+J.
@@MrXL That is very helpfull! Can you display only the filled in values (filter in the dax for blanks)? If you have blank lines, you will get something like: Melon Guava Banana For my needs I would need to report the total sum of numeric values but only highlight the added text. Thank you!
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!
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!
I found the answer to the this problem in the comments here: sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table Basically, the combination of all of my values in the Grand Total row exceeded Excel's max character limit. Removing the Grand Total row before putting the measure in the Values box works!
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!
Oh I am so sorry that you are using Excel on a Mac. The Add This Data To the Data Model does not work with any of these editions: Excel for Android Excel for iOS Excel Online Excel for Surface RT Excel for TI-99/4A Excel for Mac It seems wrong that they are allowed to call the software that you are using "Excel", when it does not have all of the capabilities of Excel for Windows.
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. :)
I just tried it in Excel 2013. Short answer: No. The "Add Measure" back in Excel 2013 was called "Insert Calculated Field" in Excel 2013 and was hidden on the Power Pivot tab. But even if you pay the extra $2 to get the Power Pivot tab, the version of DAX back then did not offer CONCATENATEX. The whole Power BI stack is evolving so quickly, with new features introduced every year. A five-year-old version of Excel does not have the latest DAX functions.
Thanks for the clarification. At home I work on Ex 2016 but unfortunately in the company on Ex 2013. It's a pity. But at least I know there is nothing to worry about. Thank you once again and best regards.
There is a bug that you can not display more than 32,768 characters in one cell in a pivot table. Try removing the Grand Totals, since the Grand Total cell tends to have all of the information in the data set.
THIS IS GREAT! THANKS. IN THIS EXAMPLE, FIG APPEARS MORE THAN ONCE ON REGION EAST, IS IT POSSIBLE TO RETURN IT JUST ONCE EVEN IF IT IS ON THE DATA MORE THAN ONCE? SO WHAT I'D LIKE TO SEE FOR THE EAST REGION IS SIMPLY GUAVA,FIG,DATE,CHERRY...
Thank you so much, this has helped me tremendously! I do have a question regarding the formula used in the calculated field. Would it be possible to sum all numbers that fall into the field then concatenate the remaining text? For example: I am using this to view weekly activity of working crews. The calculated field is grouped by week. The data in the calculated field consists of many "1"s and time off codes (VACA, HOL, etc.). Using the original formula I would get something like this for a 6 day work week; 1,1,1,1,VACA,HOL. Then using the updated formula I would get 1,VACA, HOL. Would it be possible to sum the "1"s then follow them up with the concatenated texted like this 4,VACA,HOL?
I am thinking of a way to be able to : Provide historical data per month (April, May, June etc...) Project 1 Project 2 Project 3 And then show it in the pivot ... so far it just combines it all ... I wonder how I can show the text using the measures but only per month ( so no combining)...
@@MrXL I've managed to do it ... it wasn't working at first... =CONCATENATEX(VALUES(History_Query[Comment]),History_Query[Comment],"-") Now I need to think how to combine a table with FTE's per periods and to be able to switch between FTE and History view ... Note that I format my raw data first through a power query and basically Unpivot the dates and then format as dates so that the pivot table recognizes them as actual periods ... So not sure how to combine both as I cannot have the same period names within the table for FTEs and for the historical comments ... So it works right now but with 2 pivots which is not ideal ...
just FYI made it work using lots of Queries ... I would unpivot the FTE columns as well - then compare if the FTE Period matches the period for the History and if true return the History period. Then us this new Period as the main period for the Pivot. It's sad that Calculated fields appear to not work when data models are being used from Query .... so all such calculations need to happen within the Query but alas ... I have combined it all :)
Thank you brianxyz. I have used it a couple of times since posting the video. The hassle comes in when you get too many things concatenated. It would be great to get a CONCATANATEX of the distinct items.
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!!
Awesome Bart Titulaer! That is an EXCEL-lent idea. Thanks for posting it. There are many times that I have Account Number and Customer Name and I just need the First of Name or Last of Name, because all of the names will be the same. Do you have to hide the Grand Total (or format with white?). Distinct Count rocks.
This was a very good question. I had to pay dearly for the answer. I will be personally delivering a bottle of my favorite rum to Rob Collie in Indiana for breaking the code. At the 2:41 mark, change: =CONCATENATEX(Table1,Table1[Code],”, “) to =CONCATENATEX(Values(Table1[Code]),Table1[Code],", ") The VALUES function returns a new table with the unique values found in a column.
***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.
I can't try the PowerBI option, but your first alternative does not accomplish displaying the text values instead of a count. It only changes the format of the display.
Hi sir I don't have option to write comments that's why I write here and my question is that, I have two columns with text data and I want to pivot on same with so can plz help me in that
If you like this tip, please consider subscribing and clicking the bell icon!
The article for this video is at: www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/
You can do this without any code or adding 'measures': its not at pretty but super quick.
• drag "Markets data" to the 'Rows' area
• drag "REP" to the 'Rows' area as well
GO TO RIBBON --> DESIGN --> You will see "REPORT LAYOUT"
Change the Report Layout to "Tabular Form" or "OUTLINE FORM" ; then Click "Subtotals" and turn off subtotals. your can also remove 'blank lines' from this ribbon as well.
You can also do this quite easily in PowerBI, I do this all the time in PBI its literally faster to load, drag, and export then it is to create tables and code. even better if you have multiple sheets and tables to merge. the second Easiest way is to "FUZZY LOOKUP" create the pivoted data, create a table out of your pivot data, then join the text fields to the pivoted data using fuzzy lookup.
You saved my LIFE with this! You have a new fan & subscriber!
Just found your channel. Liked and subscribed. I’ve been using ConcatX to show the residents census status for each period (use tabular form) for each resident. I added row grand total in text. I want to add column total in value for each period, how do I go about it?
@@MrAlivallohow do I add count for column grand total in value for each period for all the residents? I have. Row grand total in text for census status for each period (in column in tabular format and resident names in rows) for each resident? Please help. Thank you!
@@sallyho146 TO add a COUNTOF something, create a new column. Use the MAX function to retun MAX of TOTAL COUNT. [Count of TOTAL] and [Count of SUM of TOTAL] will be calculated differently in DAX, which is why they have (COUNTX) in DAX. Everything else you mentioned is just dragging the data to the proper field in the pivot GUI.
Not sure but, I think you would actually want to just "Reference" the [TOTAL Cell] for that column, and do a HIPSTER pivot...which is what I call a pivot of meta data, or a pivot of calculated pivot numbers, commonly the TOTALS data. Essentially you would reference all the total columns in another sheet then pivot again. Excel will get very slow very fast doing this.
But its Easily done in PowerBI with a Function and a 'Card' for that. If working with Big census data, I'd use PowerBI instead.
An Example: COUNT of 'Grand Total' will give you a number like 5, meaning there will be 5 COUNTS or 5 Grand Totals. To get this I would create a column and use the MAX function to get the max number of COUNT of that [melon], then reference that number in the pivot table. You may want to post your question to to Quora, I have 14+ years of excel answers on there with 178k followers. However; I'm not clear on this question; the pivot table in the video is (region) and (type), [east] and [melon] for example. The video is about showing the "String Value" in the Pivot table instead of the Total Count or Total Sum, which are numbers. @MrExcel.com; utilizes the Excel Engine to Concatenate, and to accomplish this. There are many ways to accomplish this, I prefer another method out of scope for this reply. If this is mission critical to your JOB, I might be open to a screenshare. Let me know and best of luck.
So awesome - DAX and Power Pivot Data Model really up the level of PivotTable fun : ) Thanks for the CONCATENATEX fun : )
ExcelIsFun Absolutaly nice Mike dax brings intelligence to excel :-) thanks for mr excellll
Thank you ExcelIsFun. They have made some nice improvements in DAX. =MEDIAN and now =CONCATANATEX.
My table has mostly number data and 1 column of free text. Is your method able to work with both in a pivot table? When I use your method, I am then unable to change the numbers values from count to sum or average in the field settings. Any help is appreciated!
I have the exact same problem. My pivot table has year results so some have 12 items and all it will do is count.
I really appreciate your video, I thought this could not be possible. You have no idea how grateful I am
Finally, the Rosetta Stone that we have been looking for: How to get a Pivot Table to speak "text". Thanks!
Thank you gorflunk. I used to put 1's and zero in the pivot table and then use a custom number function to get Yes and No to appear. But this DAX trick makes it far more versatile.
I thought this was the answer to my Excel dreams until it didn't work! I was able to get through all the steps, but when I tried to add the "list" to the values section it doesn't work. Can you help, am I doing something wrong?
I'm having the same exact issue
Same issue here too
@@stephaniepollaro3332 I had the same issue, for me it worked when I used a semicolon instead of comma
Totally amazing! I would never think it is possible to add text into Values field like this! Thanks for CONCATENATEX !
Thank you Malina C.. DAX keeps making pivot tables more powerful.
I just came across your channel and have sticked on it. I am learning your presentation using my mobile. Thank you for your good work.
Is there an equivalent solution in power query for this method?
change:
=CONCATENATEX(Table1,Table1[Code],”, “)
to
=CONCATENATEX(Values(Table1[Code]),Table1[Code],", ")
The VALUES function returns a new table with the unique values found in a column.
This worked well for me.
THANK YOU! Really needed this.
Mr. Excel!!!! This is Bob from the parties. Yep another name. 😁 Excellent tutorial, easy to follow. Just what I was looking for! THANKS!!!!
Hi there this is brilliant!
It was years that I had been trying to do it!
I just wanted to ask you whether is possible to separate the values with a line break rather than a comma? I would really appreciate your help on this :)
Thanks in advance
I think you might have found the answer by now. If not, find below the solution.
character 10 or
represent line break in excel. So, use character 10 or
in place of comma.
I did not find the " Add Measure" option to complete the procedure.
Thanks it's help me so much, but i'd got some errors likes columns had been showed just 3 columns and nos i have more than 10 columns, would u mine to solve this? Thanks
This is amazing. I do not know how you figured out how to do this, but this is incredible. Subscribed!
when I put in the formula "Table1[Code]" does not come up. what am I doing wronge?
If i want to replace the comma seperated value in table with total number and when do drilldown it will show name is it possible
I cant do it because when I'm already typing the CONCATENATEX formula, code[table1] wont show in my option...Please help.. Thank you
How can I do it on Mac? I can’t find the check box for “add data to data model”.
Mac Excel keeps getting closer to Win Excel. But the lack of a data model is still the biggest gap.
Add this data to the data model is missing for me :(
I want to add computations in Excel for survey result for alphabetic data
Hello Sir, can pls guide me on how to resolve a pivot error - this pivot table field isn't available because it exceeds the total number of characters
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.
Hello Bill, followed your steps and fx(ListOfCodes) can't be put in values. Tried to drag it also and nothing...
I found the answer to this problem in the comments here: sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table
Basically, the combination of all of the values in the Grand Total row may exceed Excel's max character limit. Removing the Grand Total row before putting the measure in the Values box works!
@@leahmartellmoore thaaanks that helped me!!
@@leahmartellmoore Got exactly the same. Thank you so much !!!!
Hi. This works in excel for MAC? When I add the pivot table, don´t show the option "add this data to the Data Model". There is another way? Thanks.
Tiago
No. Nothing on this channel will work with Excel for the Mac, Excel for the iPhone, Excel for Android, Excel for Windows NT, Excel Online, Excel for the Commodore 64. The Data Model is limited to the Windows 32 version of Excel.
When I drag the measure to values it doesn't show anythingg :(
good video i tried this but it works for only 2550 Rows of source data table not beyond.
is it excel restriction can we extend this limit.
Followed exactly but the measure never returned any values. Just spun on "reading data" then nothing... does this still work?
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.
What if I don't want to concatenate any text, but just want to return what appears as text from a particular record?
HI BOSS.. I WANT KNOW HOW CAN GET PDFS FILE LIKE VLOOCKUP FROM EXCEL NUMBER LIKE 1252. PLEASE ADVICE ME
Great formula, thanks. One question, how it's possible to do the same thing but it will display only unique values ? I have tried to incorporate DISTINCT into the formula but it didn't work no matter where have I put the Distinct.
Thank you!
Did you find a solution to this?
After i added the text to values i cant able to create a dashboard for that. Suggest any ideas plz
Tell me more. Why is your dashboard not working? Is it expecting numbers?
I want to insert 1 USD=87 BDT and this BDT 87 can be used for calculation, can anyone help?
How to manage this when data set is large and draggin in the field just processes then doesn't work?
Read the video description. There is an updated formula that uses VALUES function to remove duplicates. This might solve the problem.
My version of Excel doesn't give me the Table1code option, only Unicode … I'll keep looking!
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.
exact same issue
this formula have a limit of rows?
Hope you can help me with this, The data are repeating in the column, how can we remove the duplicates ?
Yes. After the video was published, I found a workaround to remove duplicates. Look at the video description. There is a link to an article about how to remove the duplicates.
@@MrXL thank you :)
Great feature! Thanks
Is it possible to show those text values as data labels on pivot chart? I tried do this and I couldn't find a way to do this.
My table isnt showing when I add to data model.
Does this method work on Excel 2013? Thanks
No - the CONCATENATEX function was not introduced until somewhere around 2017.
For some reason, I keep getting an error message that reads "This PivotTable field isn't available because it exceeds the total number of characters that a cell can contain" whenever I try to drag the new measure into the values section. Is there a fix for this by chance?
A cell can’t contain more than 32768 characters. Try removing the grand total … this cell often contains all text from the column.
Also there is a better formula linked in the description to remove duplicate values using VALUES()
@@MrXL thank you! Removing the grand totals helped 😊
How do you ensure only unique values are shown? Your results have duplicated in the Grand Totals
Can this be done in Office 365? We have Office 2013 at my office, but a coworker needs to be able to do exactly this. I read in other comments that Power Pivot in 2013 does not work for this either.
I'm using Excel 2013 and I can't find the measure box. Do you have any suggestions?
The DAX formula language in Excel 2013 does not include CONCATENATEX, so this will not work in 2013. If you happened to own Office 2013 Pro Plus (but not Office 2013 Professional), there is an Power Pivot add-in that lets you create DAX measures. But without CONCATENATEX in the formula language, that won't help you. Eight years ago, DAX and Power Pivot were basically version 1 features. This is a case where upgrading to capture the eight years of progress might make sense. You would also get access to more features in Power Query.
@@MrXL it means to upgrade the software 😀
Thanks MrExcel.com
HI IM UNABLE TO GET THE LINKS MY DAX FORMULA AND ALL WENT EXACTLY AS PER THE VIDEO. CAN YOU PLS HELP?
Just pulled this out in a meeting (IT wanted all access roles grouped by user) to an audible "Wow." Hopefully you can have some of this contact high.
Love hearing the “wow”
My mind is blown. Thank you, as always, for these professional tips!
Thank you timsim83. I am glad to hear the video was helpful.
For those looking for distinct (unique) values only, try this.
=CALCULATE(CONCATENATEX(DISTINCT('Table1'[Code]), 'Table1'[Code], ", "))
Thanks for posting this Chris!
Thanks for your help! I combined with TOPN and selected the biggest results
Why this function is not working on big data i.e. 15000 row?
There is a limit of 32768 characters in any cell in a pivot table. If you go over that, the pivot table will not render. I’ve seen cases where removing the grand total row and the grand total column fixes the problem.
I have two sentences 50-100 words. After applying Pivot and using Slicer. the slicer is not showing the full text. What is the solution
Did you try applying Wrap Text to the cell.
Is there a way to hide all the words when a row is minimized?
MrExcel, Thank you for the tip, it is really useful, however, it is not working for me, if I would like to use it fore more than 10K rows. Symptom: It does not want to make the drag and drop to the value field. I had to split my dataset, to make it work. Do you have any idea what to do with 10K+ rows? (I did not tested out what could be the max, but it working with 2K records, but not with 3K)
Hey Champ, thanks a lot. But I still a problem. It shows some text 2 to 4 times in a cell. How can I avoid the double counting?
There is a newer formula shown at www.mrexcel.com/excel-tips/excel-2020-text-in-the-values-of-a-pivot-table/ By wrapping the first argument in the VALUES() function, you can eliminate the duplicate text in a cell.
@@MrXL Amazing, it worked like a charm. Thanks a lot!
For whatever reason, it won't let me "Add Measure" when I'm right clicking the table name. Any thoughts?
The Add Measure is not available in Excel 2013. It is not there for any version of Excel on a Mac. If you are in Excel 2016 or later, did you check the "Add to Data Model" box when creating the pivot table?
Is there any way to get them as unique items?
its great ! but i don't have the option of " + add measure" :(
This debuted in Excel 2016.
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
I found the answer to the this problem in the comments here: sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table
Basically, the combination of all of the values in the Grand Total row may exceed Excel's max character limit. Removing the Grand Total row before putting the measure in the Values box works!
@@leahmartellmoore lifesaver. Confirmed this IS the fix
Hi Bill, when I tried I don't get the dad measure function? Please help me understand how to fix this issue. Thanks.
Is it possible to do this when using a date as the value?
It is working for me. I was initially concerned that they would give me the underlying date serial numbers, but it seems to work.
However... it would be much better if they could summarize the dates, something like 12/1/2021-12/15/2021 instead of listing all 15 dates!
The instruction worked great. I was able to get it done. However, the result showed multiple duplicated data. How can I make it display just unique value?
formula did not work and cannot drag into pivot despite it being correct
I follow the video and I do the sameting after that I try to add the valu but not showing
Kindly help me out
It is difficult to diagnose without seeing the DAX Measure. If the data is not confidential, send me the workbook. My e-mail is listed in the "About" tab just below the video.
Love this but it would be more awesome if you can create a run-down list rather than all of them bunched into 1 cells
This is great! Is there a way to display the values under each other by column, rather than beside each other separated by comma?
aha - it can be done, but it is tricky.
1) Edit the measure in the video. Where I have ", " as the third argument, delete the comma and the space.
2) With the insertion point sitting between the two quotation marks, press Shift+Enter. You will see the second quotation mark more to a new line
3) Add the measure to the pivot table. You will see MelonGuavaBananaDate and be disappointed.
4) Select the values area from the pivot table (For me, it is B5:C8) and turn on Wrap Text
You will now see
Melon
Guava
Banana
Date
The Shift+Enter is completely foreign. In Excel, we would normally use CHAR(10) or Ctrl+J.
Thank you, I will try this!
I can't think of one. There could be a way, but I don't know enough DAX to say for sure.
@@MrXL That is very helpfull! Can you display only the filled in values (filter in the dax for blanks)? If you have blank lines, you will get something like:
Melon
Guava
Banana
For my needs I would need to report the total sum of numeric values but only highlight the added text.
Thank you!
Rodrigo Santiago great question but I don’t know a definitive answer. My DAX skills are not where they need to be.
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!
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!
I found the answer to the this problem in the comments here: sfmagazine.com/post-entry/july-2018-excel-reporting-text-in-a-pivot-table
Basically, the combination of all of my values in the Grand Total row exceeded Excel's max character limit. Removing the Grand Total row before putting the measure in the Values box works!
Is there a Maximum data input?
A single cell can not contain more than 32,768 characters. With a large data set, the Grand Total cell using this method can get above 32768.
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!
Oh I am so sorry that you are using Excel on a Mac. The Add This Data To the Data Model does not work with any of these editions:
Excel for Android
Excel for iOS
Excel Online
Excel for Surface RT
Excel for TI-99/4A
Excel for Mac
It seems wrong that they are allowed to call the software that you are using "Excel", when it does not have all of the capabilities of Excel for Windows.
Yauzers!
That is a shame. Would you know any work arounds, other than, switching to Windows?
Thanks for your speedy response.
@@MrXL I am in same situation... :(
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. :)
Hi, I am using excel 2018 in MAC, I cannot find DAX formula language in the PIVOT table options. Please help me.
same
@@calebbarry3977 DAX, Power Pivot, and the Data Model do not exist in Excel for the Mac.
@@MrXL any alternatives?
@@calebbarry3977 Other than running Excel for Windows via Parallels, no.
Awesome!!! great help, thank you :) is there a way to exclude the empty cells?
Super video!
Is this to be done in the older version of Excel (Ex2013)?
I just tried it in Excel 2013. Short answer: No. The "Add Measure" back in Excel 2013 was called "Insert Calculated Field" in Excel 2013 and was hidden on the Power Pivot tab. But even if you pay the extra $2 to get the Power Pivot tab, the version of DAX back then did not offer CONCATENATEX. The whole Power BI stack is evolving so quickly, with new features introduced every year. A five-year-old version of Excel does not have the latest DAX functions.
Thanks for the clarification. At home I work on Ex 2016 but unfortunately in the company on Ex 2013. It's a pity. But at least I know there is nothing to worry about. Thank you once again and best regards.
Hi Sir,
Can I use it in 2013 Version
2013... almost nine years old. I am fairly sure that this functionality was added in 2017 era.
Just used this - THANK YOU! :-)
Excellent. It is a hot little trick! I was happy to discover that it worked.
When I right click the table name, I don't see "Add Measure" ... Any idea why?
Jeff Starr this feature was added in 2016. It does not work in 2013, 2010, or on a Mac.
Mr Excel you have done it again. I used to use an access cross tab first trick to achieve that!
In Excel 2010 not found function ConcatenateX ?
It was introduced after Excel 2016.
Was searching for an hour, you're a lifesaver - subsrcibed!
Little information can do But if a lot of information Can't do it Can you help me
There is a bug that you can not display more than 32,768 characters in one cell in a pivot table. Try removing the Grand Totals, since the Grand Total cell tends to have all of the information in the data set.
@@MrXL Thank you
THIS IS GREAT! THANKS. IN THIS EXAMPLE, FIG APPEARS MORE THAN ONCE ON REGION EAST, IS IT POSSIBLE TO RETURN IT JUST ONCE EVEN IF IT IS ON THE DATA MORE THAN ONCE? SO WHAT I'D LIKE TO SEE FOR THE EAST REGION IS SIMPLY GUAVA,FIG,DATE,CHERRY...
IT seems this does not work in combability mode -
This makes sense. The .XLS format did not have the structure to store the data model.
I'm so happy to get your video and it helped me to finish one of the biggest work. Thank you.
Thank you so much, this has helped me tremendously! I do have a question regarding the formula used in the calculated field. Would it be possible to sum all numbers that fall into the field then concatenate the remaining text? For example: I am using this to view weekly activity of working crews. The calculated field is grouped by week. The data in the calculated field consists of many "1"s and time off codes (VACA, HOL, etc.). Using the original formula I would get something like this for a 6 day work week; 1,1,1,1,VACA,HOL. Then using the updated formula I would get 1,VACA, HOL. Would it be possible to sum the "1"s then follow them up with the concatenated texted like this 4,VACA,HOL?
Thanks! For years I've been looking for this feature.
I am thinking of a way to be able to :
Provide historical data per month (April, May, June etc...)
Project 1
Project 2
Project 3
And then show it in the pivot ... so far it just combines it all ... I wonder how I can show the text using the measures but only per month ( so no combining)...
Vicente Pallamare you would have to add month as a row field.
@@MrXL I've managed to do it ... it wasn't working at first...
=CONCATENATEX(VALUES(History_Query[Comment]),History_Query[Comment],"-")
Now I need to think how to combine a table with FTE's per periods and to be able to switch between FTE and History view ...
Note that I format my raw data first through a power query and basically Unpivot the dates and then format as dates so that the pivot table recognizes them as actual periods ...
So not sure how to combine both as I cannot have the same period names within the table for FTEs and for the historical comments ... So it works right now but with 2 pivots which is not ideal ...
just FYI made it work using lots of Queries ...
I would unpivot the FTE columns as well - then compare if the FTE Period matches the period for the History and if true return the History period. Then us this new Period as the main period for the Pivot.
It's sad that Calculated fields appear to not work when data models are being used from Query .... so all such calculations need to happen within the Query but alas ... I have combined it all :)
@@vicentepallamare2608 Thanks for reporting back.
Thanks for this! Really helpful took me forever to figure this out until I found you on TH-cam! Thanks!
Thank you soo Much... Very nicely explained with example
Thanks! This will open up a lot of possibilities for summarizing tables of text.
Thank you brianxyz. I have used it a couple of times since posting the video. The hassle comes in when you get too many things concatenated. It would be great to get a CONCATANATEX of the distinct items.
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!!
Awesome Bart Titulaer! That is an EXCEL-lent idea. Thanks for posting it. There are many times that I have Account Number and Customer Name and I just need the First of Name or Last of Name, because all of the names will be the same. Do you have to hide the Grand Total (or format with white?). Distinct Count rocks.
Sir, can we show only unique matches?
I am working on this... checking with people in my network. So far, nothing. But I am not giving up yet.
This was a very good question. I had to pay dearly for the answer. I will be personally delivering a bottle of my favorite rum to Rob Collie in Indiana for breaking the code. At the 2:41 mark, change:
=CONCATENATEX(Table1,Table1[Code],”, “)
to
=CONCATENATEX(Values(Table1[Code]),Table1[Code],", ")
The VALUES function returns a new table with the unique values found in a column.
Awesome! Now it seems so simple and elegant. So thankful to you both. So much to learn from you, especially your child like curiosity and energy.
MrExcel.com Was looking for this exact answer! This is brilliant, and open up a whole new playground for reporting :)
Thank you Christoffer Müller. I am glad to hear the video was helpful.
This is not working now
Excellent! I have learned 3 new things that I didn't know. Thanks a lot
This video content helped me today.....
Subscribed 👍
I just had this question 2 hours ago and was still trying to figure it out without using any vlookups! Thanks.
Thank you jwall62. What amazing timing!
***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.
I can't try the PowerBI option, but your first alternative does not accomplish displaying the text values instead of a count. It only changes the format of the display.
@@leahmartellmoore Thanks; I have edited out that comment. Fuzzy Lookup works best for me.
Hi sir I don't have option to write comments that's why I write here and my question is that, I have two columns with text data and I want to pivot on same with so can plz help me in that
*Very cool Mr. Excel. Love the DAX trick. Thank you.👍*
Thank you James Perry. I am glad to hear the video was helpful.
TXS so much! Exactly what I was looking for!!
I don't need repetitions using this dax formula. help will be appreciated.
=CONCATENATEX(Table1,Table1[Code],", ")
=CONCATENATEX(Values(Table1[Code]), Table1[Code], ", ")
fount it.
@@omairbinenam6337 Correct. See the note in the Description for this video that will point you to the correct formula.