Show Text in the Values Section of Pivot Table
Vložit
- čas přidán 18. 08. 2024
- If you're familiar with Pivot Tables, then you'll know that numbers only show up in the values section...most of the time. However if you wanted to put text in the values section, there is a workaround. One involves custom formatting, but it's limited to three text entries (that are aligned to positive, negative and 0 values). Another workaround will be covered in this video and it involves using the Data Model and a DAX function called CONCATENATEX. See the video to learn about this tip.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain...
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
Hi Doug, It is very honest of You to acknowledge Mr.Excel in Your Video. Clap-Clap!
Many thanks!
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!
Can't agree more with everyone, excellent work and so easy to implement after viewing your video.
Thank you so much for this. Needed to do this for my job but couldn't figure out how. Lifesaver!
Glad it helped!
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
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!
you are a rock star!!!
This is awesome, can you do on MacBooks? Im struggling to do this on apple version?
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 czcams.com/video/7PVjvY4a_V8/video.html
Solve my years of problem... like and subscribed..thanks
Thanks!!
Just found in this video what I'm looking for, thank you🤩
You’re welcome 😊
you solve my problem. Thank you!!
You’re welcome, glad it helped!
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?
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 for this wonderful tutorial
You’re welcome 😊
Thank you so much!!! Works like a charm!
You’re welcome!
Amazing! Thank you so much for this video. I have used it twice now.
Glad you liked it!
Awesome, thank you.
You're welcome!
Thank you! Great solution for generating RACI charts (which is what I did with it).
Excellent!
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?
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?
Your powers are like that of a god. Thanks for this lesson
Thanks 😊!
Thanks, very good.
Thank you too!
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!
Great video! It helps me a lot! Thanks!
Glad it helped!
Thank you so much. you give me a solution
Happy to help!
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
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!
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!
Exactly what I needed. Thank you!
You're welcome!
Excellent Trick! Thank You!
Hi Learn Excel, thanks for the comment!
Hello. Is this only workable on small sized data? Tried on a big sheet of data and it doesn’t work :(
Thank you. Really very helpful
You’re welcome!
This is great! I have been wanting to know how to do this forever!
Glad it was helpful!
Such a useful tip, thanks so much!
You’re welcome!
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!
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!
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.
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 :)
What should be value for new line as a delimiter ??
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.
Thank you this is really helpful
Glad it was helpful!
Thanks!
Welcome! Thanks for the support!!!🙏
I have similar text repeated in the columns, what can be done to avoid copying of the text
Great video. Just what I was looking for!
Hi Victor Espinoza, glad you liked it, thanks for commenting!
Smooth trick.
Hi mirrr velll, thanks for the comment!
Very nice work.
Thanks!
very useful
Glad to hear that!
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.
Thank you so much, you save me!!!
You're welcome!
Very informative. Great work
Hi Rafia Usmani, glad you liked it, thanks for commenting!
thank you :)
You're welcome!
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
Wonderful. Many thanks for sharing!
Hi jksan927...you're welcome, glad you liked!
Thank you so much.. Excellent..very useful..
Thanks yarram kartheek reddy, glad it helped!
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
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 czcams.com/video/sJqmqKBKwGs/video.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 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!!
Can you do it with removing region
You know this video is legit when there are no dislikes! 👍
Hi Yzeuthium, thanks for the comment!
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.
That is great - amazing tip. Thanks!
You're Welcome!
Does this work on Excel 2010
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
czcams.com/video/40wE-oKG2zw/video.html
czcams.com/video/pjr5cljnvxU/video.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.
Awesome trick. Thanks for sharing.
Hi CA.Lakshmi Narayan Reddy Jambula, glad you liked it, thanks for commenting!
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!
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
Super useful! Thanks :-)
Glad to hear that!
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!
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
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.
Wow.. Totally saved me! 😊
Thanks Suyash Bansal, glad it helped!
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?
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 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
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 czcams.com/video/wHgv_gWw7iQ/video.html
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.
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.
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! 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!
Life saver
Hi Yaniel Suarez, thanks for the comment!
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...
In Macos there is no 'add to data model' checkbox
Excellent Bro :)
Hi Riyaz Baba, thanks for the comment!
Great I put, thanks a lot.
You're Welcome!
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??
Help please, with this method I can't group the rows
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!
Extremely impressive thanx alot
Hi ibrahim ezz, thanks for the comment!
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
Thanks bos
You’re welcome!
Something new. Thanks
You're Welcome!
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 >> czcams.com/video/J-U722V1Fig/video.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.
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.
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
czcams.com/users/dough517search?query=custom+format