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, 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?
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.
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)
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.
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!
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?
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 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.
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
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 :)
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.
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 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.
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 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.
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!!
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 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
@@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.
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 >> ua-cam.com/video/J-U722V1Fig/v-deo.html
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?
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
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 :)
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.
Amazingly helpful! thanks
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!
Thank you for this wonderful tutorial
You’re welcome 😊
Excellent Trick! Thank You!
Hi Learn Excel, thanks for the comment!
you are a rock star!!!
you solve my problem. Thank you!!
You’re welcome, glad it helped!
Thank you so much!!! Works like a charm!
You’re welcome!
Solve my years of problem... like and subscribed..thanks
Thanks!!
Amazing! Thank you so much for this video. I have used it twice now.
Glad you liked it!
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?
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
Thank you! Great solution for generating RACI charts (which is what I did with it).
Excellent!
Thank you. Really very helpful
You’re welcome!
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!
Just found in this video what I'm looking for, thank you🤩
You’re welcome 😊
Great video! It helps me a lot! Thanks!
Glad it helped!
Awesome, thank you.
You're welcome!
You sir, are a legend!! Thanks very much.
Hi Pensky File, 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
Your powers are like that of a god. Thanks for this lesson
Thanks 😊!
Thank you so much. you give me a solution
Happy to help!
This is great! I have been wanting to know how to do this forever!
Glad it was helpful!
Thanks, very good.
Thank you too!
Exactly what I needed. Thank you!
You're welcome!
Thank you this is really helpful
Glad it was helpful!
Such a useful tip, thanks so much!
You’re welcome!
Thanks!
Welcome! Thanks for the support!!!🙏
Excellent tip! Thanks for sharing!
Hi nboisen, glad you liked it, thanks for commenting!
Hello. Is this only workable on small sized data? Tried on a big sheet of data and it doesn’t work :(
This is awesome, can you do on MacBooks? Im struggling to do this on apple version?
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!
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 ua-cam.com/video/7PVjvY4a_V8/v-deo.html
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!
Smooth trick.
Hi mirrr velll, thanks for the comment!
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?
very useful
Glad to hear that!
What should be value for new line as a delimiter ??
Very informative. Great work
Hi Rafia Usmani, glad you liked it, thanks for commenting!
Wonderful. Many thanks for sharing!
Hi jksan927...you're welcome, glad you liked!
Very nice work.
Thanks!
thank you :)
You're welcome!
Thank you so much.. Excellent..very useful..
Thanks yarram kartheek reddy, glad it helped!
Thank you so much, you save me!!!
You're welcome!
I have similar text repeated in the columns, what can be done to avoid copying of the text
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!
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?
Great video. Just what I was looking for!
Hi Victor Espinoza, glad you liked it, thanks for commenting!
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 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.
Awesome trick. Thanks for sharing.
Hi CA.Lakshmi Narayan Reddy Jambula, glad you liked it, thanks for commenting!
Can you do it with removing region
That is great - amazing tip. Thanks!
You're Welcome!
Life saver
Hi Yaniel Suarez, thanks for the comment!
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
Does this work on Excel 2010
Thanks bos
You’re welcome!
In Macos there is no 'add to data model' checkbox
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!
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 :)
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.
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
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!
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.
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.
Extremely impressive thanx alot
Hi ibrahim ezz, thanks for the comment!
Great I put, thanks a lot.
You're Welcome!
Super useful! Thanks :-)
Glad to hear that!
Wow.. Totally saved me! 😊
Thanks Suyash Bansal, glad it helped!
Help please, with this method I can't group the rows
Something new. Thanks
You're Welcome!
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?
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!!
You know this video is legit when there are no dislikes! 👍
Hi Yzeuthium, thanks for the comment!
amaaaaazing!
Thanks!
Excellent Bro :)
Hi Riyaz Baba, thanks for the comment!
What if count value is 2 or bigger. The text will repeat x times....
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 ua-cam.com/video/sJqmqKBKwGs/v-deo.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
Dude talk so fast had to play at .75x ; appreciate it homie!
No problem 👍
what if the data is on the different tab, then excel doesnt recognize the table?
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 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
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?
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...
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.
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
ua-cam.com/video/40wE-oKG2zw/v-deo.html
ua-cam.com/video/pjr5cljnvxU/v-deo.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.
It doesn’t give me the “add this data to the data model” box
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.
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 >> ua-cam.com/video/J-U722V1Fig/v-deo.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.
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!
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
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.
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
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.
Stunning, good job man!
Hi Martin Mana, thanks for the comment!
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!