I'm late to the party but found your video after a search of trying to do such a thing and some sites over complicating it with VBA . You've just made one of my jobs easier so THANKS!
Great tip. I've always wanted to find a way to do this. As a side note, the before/after can be swapped using drag and drop and the pivot table will automatically swap the columns. To do this simply select "Before" cell on the Pivot Table, then drag the edge of the cell to after the "After" cell and the Pivot Table will swap everything else around for you.
Wow! all the times I was like that can't be done. I've always done another work around. I've saved this link. Now I'm trying to think of problems to solve.
Great, thanks. However i got an error message: '' This pivot table field isn't available becauss it exceeds the total number of characters that a cell can contain. '' I only wanted to move dates in pivot table values. Only idea why i got this message?
Very usefull and my most searching and waiting soluation. I try to found this since 8 year. Sir 1 question Can we sort concatenate value result in cell, Like (white,Red) sort in (Red,White)
Question to all, please.... I have Excel for Mac, hence I do not have the option to "Add this data to the Data Model" (because Excel for Mac does not have Power Pivot). Do you know a way to do the same that you showed but in Excel for Mac? Thanks! THANKS DOUG H for such great tutorial!
My table has numbers and text. Using this method I am able to get the text to come over correctly but it won't let me change the data values from Count to Sum or Average. Is there a way to show both text and values?
Christina I had the same problem and after a lot of trial and error I believe it may be due to too much data in the table. My first table had >4500 rows and it wouldn't work. I deleted some historical data and got it down below 1000 rows and then it worked. Hope that helps. Good luck
You should make sure that Grand Totals are turned off for rows and columns. With grand totals on, Excel tries to cram each and every text string you have into one cell, and refuses to do so unless you have not a lot of rows (as Bruce commented below). turning those off allowed excel to accept it!
Thank you for the video, I tried this step by step. I added measure properly, when I drag function to the Values section it's not going in. Why it can't be checked? I am using Microsoft Excel 2016.
This is a life saver, one problem / question. My original item in Rows is a grouped one so it may appear multiple times, but the measured field is adding all of the measure items with commas but I only want it to show it once, instead of all of them...how can I do that (ie just take the value from the first occurance) Also very often when I have large pivot tables, nothing happens and it won't show the value. Is there a limit?
@@gabrieldibella9418 Yeah it happens all the time when I have large data sets - I usually just have to use PQ to change the layout. I always turn off totals and sometimes it'll work but if there are too many rows it will not work.
@@jamespyle6398 Hey James this works for me. I don't know why or how but adding values to the formula seems to let it handle larger amounts of raw data. Concatenatex(Values(Table1[unitcode],Table1[unitcode]),",") Obviously table1 is just the name of my pivot table and unitcode is the information I want as text
@@gabrieldibella9418 Yeah! That's a solution I just found like a couple days ago and used it in another instance and it worked. Will have to keep that one in my mind for future projects :)
Thanks for the video. Very helpful. How do you apply formatting? For example my data has leading zeros which are cut off when I follow your concantenatex formula. How do I get excel to display my data as 00002019 and not 2019? Thanks for any advise you can give.
Hello, This is what i have been looking for!!! But...i am on Microsoft 16.5 for mac, and i dont seem to have all the options you have! Any chance you would know? thank you!!
Perhaps this is because I'm using Office 365 Beta, but if you click on little down arrow to the right of Period in the field list, you can sort that value without having to change the values. Also, if you click on "Before" in the Pivot Table and hover over one of the edges of the cell, you can move the cell to the left of After without sorting. I do seem to remember this being available further back than January 2019. I didn't see any other comments on this below. That said, great technique for getting text into the table values.
Thanks a lot. This would save a lot of time. But beware, this doesn't work if the table is very large. When you apply this to large data set, the formula in the "Grand Total" Cell would concatenate all the texts and when it exceeds 32768 characters, it will not work.
Idea: First remove duplicates from Table1 using Power Query Editor and using the updated table instead. Steps: 1. > Data tab > From Table/Range 2. > Remove Rows > Remove Duplicates 3. Rename the Query, say Table2 4. Follow the same steps as describe in the video except using Table2 instead of Table1
I followed the steps and there isn't a mistake in the formula, but when I try to drag the new field over to the value section, I get the error message that "this pivot table field isn't available because it exceeds the total number of characters." Any ideas on how to fix? The total character count of anything at appears in the column is 12 characters at most
Thanks for this. After data model, cant use "Group" option. Also, after complete the formula, the "fx-text" is not getting added to the values section. Would you be able to help with that?
@@DougHExcel Thanks for the reply man! My problem is actually when i concatenate, similar values get duplicated. I was actually hoping that when it only lists down unique values in the cell. for example if Red shows up three times, it only puts one iteration of Red.
Did you turn it into a table first (CTRL+T) ? I had this issue when first trying it with my data set. Once I converted into a table with headers it allowed the option
Depends on version of Excel too --> support.microsoft.com/en-us/office/tutorial-import-data-into-excel-and-create-a-data-model-4b4e5ab4-60ee-465e-8195-09ebba060bf0
PRO TIP: A very simple way to show text values is by right clicking on your pivot table, choose Pivot Table Options, then check Classic Pivot Table layout. That's it! Your row labels will show text values and not number values of the sum, count, etc.
Thanks for sharing, may please ask a question though, I was wondering how come I do not have the "Add measure" option when I created my pivot table? Appreciate your response :)
In my pivot table I have 5 years worth of data so I have values sometimes that are 12 in 1 year and all it'll do is count this seems to only work in a very basic pivot table.
I have a kind of similar situation the only difference is that I want the number format that is "111-1234-1234" in the value column. Please suggest to me how to do it.
Try to format the cell for your display output. This vid covers for phone numbers but the concept is similar >> ruclips.net/video/J-U722V1Fig/видео.html
I have a problem here if you could help me with. I followed exactly all the steps, and the new measure is created but then when i try to drag it into the values section or even mark it, nothing happens at all. i mean it doesn't show up in the values section
Really cool. I have an query. Let there is 3 column data, column A has names and one name occurs more than one time, column B has numbers, column C has yes or no entries. Now I make a pivot of name and column C. I add a measure for column C. But for a particular name it shows yes, yes, no, yes, no because that name repeats 5 times in base data. But I don't want repetitive values. I just want yes or no or yes,no in that measure. How it can be done??
I wonder how the way to use another workaround like you said in description " One involves custom formatting, but it's limited to three text entries (that are aligned to positive, negative and 0 values)"
So the database must to have postive, negative, dan zero value. and these three option can refer to one word that we want. after we pivot table it.. we change again the formatting in pivot table right ?
Hi Doug, I am unable to add measure after checking the tick in data model, no option available while I am right clicking over the table to insert the measure. Can you advise the reason of the same, using the office 2013 professional.
When using a normal 'count function' in a pivot table any blank cells are recognised and the whole line is removed, helping to condense the data. When using the CONCATENATEX this doesn't seem to happen, the blank cells and lines are also brought into the table. Do you know if there is a way around this?
A very simple way to show text values is by right clicking on your pivot table, choose Pivot Table Options, then check Classic Pivot Table layout. That's it! Your row labels will show text values and not number values of the sum, count, etc.
The issue with this solution Doug is the Grand total. hen you are working with data in which you need to see the grand total the concatenate X tab is totaled
![[Limbus Company - PV] Chapter.7 - Oblivion](http://i.ytimg.com/vi/VUpkXPkvoxU/mqdefault.jpg)
Hello. Is this only workable on small sized data? Tried on a big sheet of data and it doesn’t work :(
This was amazing. I searched through 20 different sites before I came to yours. This was exactly what I needed and has saved me hours of work!!
Thanks @michaelpease4996, glad it helped!
Hi Doug, It is very honest of You to acknowledge Mr.Excel in Your Video. Clap-Clap!
Many thanks!
Thank you so much for this. Needed to do this for my job but couldn't figure out how. Lifesaver!
Glad it helped!
Can't agree more with everyone, excellent work and so easy to implement after viewing your video.
Thank you, the detail you go into has made this so easy for me.
Glad it was helpful!
I'm late to the party but found your video after a search of trying to do such a thing and some sites over complicating it with VBA . You've just made one of my jobs easier so THANKS!
Glad I could help!
Works like magic
Thanks a bunch
Amazingly helpful! thanks
Great tip. I've always wanted to find a way to do this. As a side note, the before/after can be swapped using drag and drop and the pivot table will automatically swap the columns. To do this simply select "Before" cell on the Pivot Table, then drag the edge of the cell to after the "After" cell and the Pivot Table will swap everything else around for you.
Hi Andreas Altmann, thanks for adding to the thread!
Thanks for the tip
Wow! all the times I was like that can't be done. I've always done another work around. I've saved this link. Now I'm trying to think of problems to solve.
Hi Freemarkets1236, thanks for the comment!
Thank you! Great solution for generating RACI charts (which is what I did with it).
Excellent!
Just found in this video what I'm looking for, thank you🤩
You’re welcome 😊
Brother, Thanks a lot, even I not advance in english but thank you, I really appreciate it, it save my time so much
You are welcome
Great, thanks. However i got an error message: '' This pivot table field isn't available becauss it exceeds the total number of characters that a cell can contain. ''
I only wanted to move dates in pivot table values. Only idea why i got this message?
Really great thanks to you in my work area i needed like this so many times i try to get this in youtube your the one helped me thanks a lot
Glad I could help!
Amazing! Thank you so much for this video. I have used it twice now.
Glad you liked it!
Very usefull and my most searching and waiting soluation. I try to found this since 8 year.
Sir 1 question
Can we sort concatenate value result in cell,
Like (white,Red) sort in (Red,White)
seems like a custom sort? See ruclips.net/video/7PVjvY4a_V8/видео.html
Thank you for this wonderful tutorial
You’re welcome 😊
This is awesome, can you do on MacBooks? Im struggling to do this on apple version?
You sir, are a legend!! Thanks very much.
Hi Pensky File, you're welcome!
This is great! I have been wanting to know how to do this forever!
Glad it was helpful!
Question to all, please.... I have Excel for Mac, hence I do not have the option to "Add this data to the Data Model" (because Excel for Mac does not have Power Pivot). Do you know a way to do the same that you showed but in Excel for Mac? Thanks!
THANKS DOUG H for such great tutorial!
Thank you so much!!! Works like a charm!
You’re welcome!
Excellent Trick! Thank You!
Hi Learn Excel, thanks for the comment!
My table has numbers and text. Using this method I am able to get the text to come over correctly but it won't let me change the data values from Count to Sum or Average. Is there a way to show both text and values?
What do I do if when I get to the point where I pull the new Fx item into the value box and it doesn't accept it?
Christina I had the same problem and after a lot of trial and error I believe it may be due to too much data in the table. My first table had >4500 rows and it wouldn't work. I deleted some historical data and got it down below 1000 rows and then it worked. Hope that helps. Good luck
@@bdaniels72 Hi! I found that turning grand totals off helped this too.
You should make sure that Grand Totals are turned off for rows and columns. With grand totals on, Excel tries to cram each and every text string you have into one cell, and refuses to do so unless you have not a lot of rows (as Bruce commented below). turning those off allowed excel to accept it!
@@bdaniels72 Yes, I am having the same problem, I have 41,000 rows (!) which I think I'll have to make ≈ 41 individual pivot tables for.
Hope you got it from the others responses in the thread...thanks to them!
Exactly what I needed. Thank you!
You're welcome!
you solve my problem. Thank you!!
You’re welcome, glad it helped!
Great video! It helps me a lot! Thanks!
Glad it helped!
Thank you so much...that is just impressive and so well explained...helped me tremendously🥰🥰🥰...thank you very much!!! New subscriber.
You are so welcome!
Thank you. Really very helpful
You’re welcome!
Excellent tip! Thanks for sharing!
Hi nboisen, glad you liked it, thanks for commenting!
Thank you for the video, I tried this step by step. I added measure properly, when I drag function to the Values section it's not going in. Why it can't be checked? I am using Microsoft Excel 2016.
Such a useful tip, thanks so much!
You’re welcome!
you are a rock star!!!
Thank you so much. you give me a solution
Happy to help!
Solve my years of problem... like and subscribed..thanks
Thanks!!
Awesome, thank you.
You're welcome!
This is a life saver, one problem / question. My original item in Rows is a grouped one so it may appear multiple times, but the measured field is adding all of the measure items with commas but I only want it to show it once, instead of all of them...how can I do that (ie just take the value from the first occurance)
Also very often when I have large pivot tables, nothing happens and it won't show the value. Is there a limit?
Hi James I know this is from 10 months ago but were you still struggling with nothing happening for large pivot tables? I may have a fix for you.
@@gabrieldibella9418 Yeah it happens all the time when I have large data sets - I usually just have to use PQ to change the layout. I always turn off totals and sometimes it'll work but if there are too many rows it will not work.
@@jamespyle6398 Hey James this works for me. I don't know why or how but adding values to the formula seems to let it handle larger amounts of raw data.
Concatenatex(Values(Table1[unitcode],Table1[unitcode]),",")
Obviously table1 is just the name of my pivot table and unitcode is the information I want as text
@@gabrieldibella9418 Yeah! That's a solution I just found like a couple days ago and used it in another instance and it worked. Will have to keep that one in my mind for future projects :)
@@gabrieldibella9418 Yep this works well :)
Your powers are like that of a god. Thanks for this lesson
Thanks 😊!
Great video. Just what I was looking for!
Hi Victor Espinoza, glad you liked it, thanks for commenting!
Thanks, very good.
Thank you too!
Thanks for the video. Very helpful. How do you apply formatting? For example my data has leading zeros which are cut off when I follow your concantenatex formula. How do I get excel to display my data as 00002019 and not 2019? Thanks for any advise you can give.
I have similar text repeated in the columns, what can be done to avoid copying of the text
May I ask what if I want to show more value section? This one only shows Color value, what if it has , for example State or Region? Thanks
What should be value for new line as a delimiter ??
Hello, This is what i have been looking for!!! But...i am on Microsoft 16.5 for mac, and i dont seem to have all the options you have! Any chance you would know? thank you!!
The data model might not be available on the Mac version but may come in the future
@@DougHExcel No wonder!! Thank you!!
Perhaps this is because I'm using Office 365 Beta, but if you click on little down arrow to the right of Period in the field list, you can sort that value without having to change the values. Also, if you click on "Before" in the Pivot Table and hover over one of the edges of the cell, you can move the cell to the left of After without sorting. I do seem to remember this being available further back than January 2019. I didn't see any other comments on this below. That said, great technique for getting text into the table values.
Hi Jerry Dellasala, thanks for the comment and adding to the thread!
Very informative. Great work
Hi Rafia Usmani, glad you liked it, thanks for commenting!
Thanks a lot. This would save a lot of time.
But beware, this doesn't work if the table is very large. When you apply this to large data set, the formula in the "Grand Total" Cell would concatenate all the texts and when it exceeds 32768 characters, it will not work.
Hi Saravanan Dilipkumar, thanks for the comment!
This is exactly what happened to me! Thank you for your confirm. Is there another option then?
Wonderful. Many thanks for sharing!
Hi jksan927...you're welcome, glad you liked!
Thank you so much, you save me!!!
You're welcome!
Great Video! do you know any way to have only unique values show up in the pivot table if there are duplicates in the data set?
you might want to try this ruclips.net/video/sJqmqKBKwGs/видео.html
Idea: First remove duplicates from Table1 using Power Query Editor and using the updated table instead.
Steps:
1. > Data tab > From Table/Range
2. > Remove Rows > Remove Duplicates
3. Rename the Query, say Table2
4. Follow the same steps as describe in the video except using Table2 instead of Table1
Thanks!
Welcome! Thanks for the support!!!🙏
Very nice work.
Thanks!
I followed the steps and there isn't a mistake in the formula, but when I try to drag the new field over to the value section, I get the error message that "this pivot table field isn't available because it exceeds the total number of characters." Any ideas on how to fix? The total character count of anything at appears in the column is 12 characters at most
Thank you so much.. Excellent..very useful..
Thanks yarram kartheek reddy, glad it helped!
Smooth trick.
Hi mirrr velll, thanks for the comment!
You know this video is legit when there are no dislikes! 👍
Hi Yzeuthium, thanks for the comment!
Awesome trick. Thanks for sharing.
Hi CA.Lakshmi Narayan Reddy Jambula, glad you liked it, thanks for commenting!
1:37 "Add this data to the Data Model" is not an option that shows up when I want to add a pivot table 😕 Yet my excel ip up to date (on MacOS)
Same
Thanks for this. After data model, cant use "Group" option. Also, after complete the formula, the "fx-text" is not getting added to the values section. Would you be able to help with that?
Hi Grace L, sorry 🙁....but try a post on the mrexcel.com forum!
very useful
Glad to hear that!
Dude talk so fast had to play at .75x ; appreciate it homie!
No problem 👍
Thank you for this! :) Isi t possible to show the concatenated values as a drop down list?
or make it so it same values does not repeat?
maybe one of these can give an idea
ruclips.net/video/40wE-oKG2zw/видео.html
ruclips.net/video/pjr5cljnvxU/видео.html
@@DougHExcel Thanks for the reply man! My problem is actually when i concatenate, similar values get duplicated. I was actually hoping that when it only lists down unique values in the cell. for example if Red shows up three times, it only puts one iteration of Red.
Whenever i try to use add data model.. There is no add measure whenever I right click :(
Did you turn it into a table first (CTRL+T) ? I had this issue when first trying it with my data set. Once I converted into a table with headers it allowed the option
@@cstrickley I did turn my data into a table first (ctrl + T) and I'm experiencing the same issue. No +add measure option when right clicking.
@@PAllen77 same with me
Depends on version of Excel too --> support.microsoft.com/en-us/office/tutorial-import-data-into-excel-and-create-a-data-model-4b4e5ab4-60ee-465e-8195-09ebba060bf0
i followed every step and was able to create the measure. But when i drag it to the values area it does not accept. Do you know why?
That is great - amazing tip. Thanks!
You're Welcome!
thank you :)
You're welcome!
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.
Thirisangu Kannan, that is very interesting...thanks for letting me know.
PRO TIP:
A very simple way to show text values is by right clicking on your pivot table, choose Pivot Table Options, then check Classic Pivot Table layout. That's it! Your row labels will show text values and not number values of the sum, count, etc.
Thanks.
Thanks for sharing, may please ask a question though, I was wondering how come I do not have the "Add measure" option when I created my pivot table? Appreciate your response :)
Try again and see if the "add to data model" check box was checked
I'm trying this process however it isn't working. Does this max out at a particular number of rows?
Hi Marketing_AW, thanks for the comment! Max out? not that i know of...
amaaaazing!!! exactly what i needed!
Hi +paulovable17, glad you liked it, thanks for commenting!
Hi +paulovable17, thanks for the comment!
Hi +paulovable17, glad you liked it, thanks for commenting!
Thanks so much for a great solution to a problem I've been looking to solve for a while. Does this also work in Power BI?
sorry haven't tested in Power BI...
Excellent tip! I tried but I am not able drag that fx color tab to value. It is not showing in Value. Please help.
Can you do it with removing region
Hi, I'm using this with dates, should it work the same?
I can't get it to work
Excel recognizes dates as values. If you want to turn it into text go to the source cells and put an apostrophe before the date.
In Macos there is no 'add to data model' checkbox
Nice tip. Would it be possible to show color instead of color name?
I haven't tried this but maybe look into conditional formatting ruclips.net/video/meogoA_MXmg/видео.html
In my pivot table I have 5 years worth of data so I have values sometimes that are 12 in 1 year and all it'll do is count this seems to only work in a very basic pivot table.
Thanks for the solution.
Could u please give a solution hoe to add calculated column with custom text in the pivot table.
Thank you
Hi Geddla Vittal, thanks for the feedback, maybe another video in the near future to cover
Does this work on Excel 2010
I have a kind of similar situation the only difference is that I want the number format that is "111-1234-1234" in the value column. Please suggest to me how to do it.
Try to format the cell for your display output. This vid covers for phone numbers but the concept is similar >> ruclips.net/video/J-U722V1Fig/видео.html
@@DougHExcel I appreciate the answer however I already have the number in this format and I would like to copy it as is when I create a pivot table.
I have a problem here if you could help me with. I followed exactly all the steps, and the new measure is created but then when i try to drag it into the values section or even mark it, nothing happens at all. i mean it doesn't show up in the values section
Maybe the add to data model checkbox was not checked?
Really cool. I have an query.
Let there is 3 column data, column A has names and one name occurs more than one time, column B has numbers, column C has yes or no entries. Now I make a pivot of name and column C. I add a measure for column C. But for a particular name it shows yes, yes, no, yes, no because that name repeats 5 times in base data. But I don't want repetitive values. I just want yes or no or yes,no in that measure. How it can be done??
I wonder how the way to use another workaround like you said in description " One involves custom formatting, but it's limited to three text entries (that are aligned to positive, negative and 0 values)"
So the database must to have postive, negative, dan zero value. and these three option can refer to one word that we want. after we pivot table it.. we change again the formatting in pivot table right ?
These videos might give some insight
ruclips.net/user/dough517search?query=custom+format
Hi Doug, I am unable to add measure after checking the tick in data model, no option available while I am right clicking over the table to insert the measure.
Can you advise the reason of the same, using the office 2013 professional.
You might need to download powerpivot and enable from microsoft.com
When using a normal 'count function' in a pivot table any blank cells are recognised and the whole line is removed, helping to condense the data. When using the CONCATENATEX this doesn't seem to happen, the blank cells and lines are also brought into the table. Do you know if there is a way around this?
Hi Tom Hayes, not clear on your experience, but you may want try to post on the mrexcel.com forum!
Hi! great video, does this work on mac? I cant find the add measure option as i cant see the table name in the pivot table
not sure...I don't have mac version!
What should I can mention in the delimiter instead of comma if I want the values in next line of the same cell.....
that would be a Ctrl+J to have a line break. I haven't tried it but give it a try.
Hi Doug, I can't add the new measure to values after successfully adding measure. What do you suggest? Cheers!
Sorry, don’t understand the comment/ question... but should be able to add to the source of the pivot and then refresh
A very simple way to show text values is by right clicking on your pivot table, choose Pivot Table Options, then check Classic Pivot Table layout. That's it! Your row labels will show text values and not number values of the sum, count, etc.
The issue with this solution Doug is the Grand total. hen you are working with data in which you need to see the grand total the concatenate X tab is totaled
Help please, with this method I can't group the rows
What if count value is 2 or bigger. The text will repeat x times....
Fantastic video! Is there a way I can add an editable/dynamic comments column to the pivot?
maybe included something like this as a source for a pivot ruclips.net/video/wHgv_gWw7iQ/видео.html
Extremely impressive thanx alot
Hi ibrahim ezz, thanks for the comment!
Wow.. Totally saved me! 😊
Thanks Suyash Bansal, glad it helped!
Life saver
Hi Yaniel Suarez, thanks for the comment!
Super useful! Thanks :-)
Glad to hear that!