Show Text in the Values Section of Pivot Table

[@ankursharma6157]

Hi Doug, It is very honest of You to acknowledge Mr.Excel in Your Video. Clap-Clap!

[@DougHExcel]

Many thanks!

[@michaelpease4996]

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!!

[@DougHExcel]

Thanks @michaelpease4996, glad it helped!

[@fredatang4058]

I had watched this long time ago and now I need this again. So lucky to find you. Thanks. Now I am going to save it. Thank you soooo much. Very clear.

[@DougHExcel]

Glad it was helpful! Stay tuned as I'll have another video out soon that does this but using newer functions.

[@constantinosmavris]

Can't agree more with everyone, excellent work and so easy to implement after viewing your video.

[@frankmarinero6431]

Thank you so much for this. Needed to do this for my job but couldn't figure out how. Lifesaver!

[@DougHExcel]

Glad it helped!

[@veronicateem404]

Hello. Is this only workable on small sized data? Tried on a big sheet of data and it doesn’t work :(

[@fgsanz]

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!

[@abusohaib]

Thank you, the detail you go into has made this so easy for me.

[@DougHExcel]

Glad it was helpful!

[@cstrickley]

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!

[@DougHExcel]

Glad I could help!

[@damienmacquart8557]

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)

[@Understandable-z2k]

Same

[@sammulholland871]

This is awesome, can you do on MacBooks? Im struggling to do this on apple version?

[@blazerdurando1006]

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?

[@kylebryson486]

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?

[@oyuntuulbadmaarag5628]

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.

[@andreasaltmann4214]

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.

[@DougHExcel]

Hi Andreas Altmann, thanks for adding to the thread!

[@fulufhelomasutha1840]

Thanks for the tip

[@jamespyle6398]

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]

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.

[@jamespyle6398]

@@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.

[@gabrieldibella9418]

@@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

[@jamespyle6398]

@@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 :)

[@jamespyle6398]

@@gabrieldibella9418 Yep this works well :)

[@christinahughes6086]

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?

[@bdaniels72]

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

[@samuelray7271]

@@bdaniels72 Hi! I found that turning grand totals off helped this too.

[@samuelray7271]

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!

[@jaypatd69]

@@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.

[@DougHExcel]

Hope you got it from the others responses in the thread...thanks to them!

[@rohitsaini9467]

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)

[@DougHExcel]

seems like a custom sort? See kzbin.info/www/bejne/bYG5m6mPacaSjJo

[@soobiedoobie5614]

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.

[@fredatang4058]

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

[@gerhardsiagian6524]

Just found in this video what I'm looking for, thank you🤩

[@DougHExcel]

You’re welcome 😊

[@manjulagd6398]

Thank you for this wonderful tutorial

[@DougHExcel]

You’re welcome 😊

[@sholomblock7274]

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?

[@DougHExcel]

you might want to try this kzbin.info/www/bejne/qXvUnqSBd7CqfdU

[@llau0203]

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

[@Ryanrenata01]

Brother, Thanks a lot, even I not advance in english but thank you, I really appreciate it, it save my time so much

[@DougHExcel]

You are welcome

[@radhaganesan3336]

I have similar text repeated in the columns, what can be done to avoid copying of the text

[@Freemarkets1236]

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.

[@DougHExcel]

Hi Freemarkets1236, thanks for the comment!

[@dianal3627]

Amazing! Thank you so much for this video. I have used it twice now.

[@DougHExcel]

Glad you liked it!

[@qlau]

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!!

[@DougHExcel]

The data model might not be available on the Mac version but may come in the future

[@qlau]

@@DougHExcel No wonder!! Thank you!!

[@JohannJosefy]

Thank you so much!!! Works like a charm!

[@DougHExcel]

You’re welcome!

[@pransis.d]

Thank you for this! :) Isi t possible to show the concatenated values as a drop down list?

[@pransis.d]

or make it so it same values does not repeat?

[@DougHExcel]

maybe one of these can give an idea kzbin.info/www/bejne/amHadmClgKxlsNk kzbin.info/www/bejne/ppvVZpain9Oprrc

[@pransis.d]

@@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.

[@hariharanc3815]

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

[@DougHExcel]

Glad I could help!

[@Quotar]

Works like magic Thanks a bunch

[@PranavPatil-t2t]

What should be value for new line as a delimiter ??

[@mimduzova]

Great video! It helps me a lot! Thanks!

[@DougHExcel]

Glad it helped!

[@forrobertrichard]

Thank you! Great solution for generating RACI charts (which is what I did with it).

[@DougHExcel]

Excellent!

[@harshulbhatia1130]

Amazingly helpful! thanks

[@penskyfile5290]

You sir, are a legend!! Thanks very much.

[@DougHExcel]

Hi Pensky File, you're welcome!

[@bsmackintosh]

This is great! I have been wanting to know how to do this forever!

[@DougHExcel]

Glad it was helpful!

[@erethz]

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 :)

[@DougHExcel]

Try again and see if the "add to data model" check box was checked

[@reka1231]

Thank you so much...that is just impressive and so well explained...helped me tremendously🥰🥰🥰...thank you very much!!! New subscriber.

[@DougHExcel]

You are so welcome!

[@gracel7840]

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]

Hi Grace L, sorry 🙁....but try a post on the mrexcel.com forum!

[@vittal255]

Thanks for the solution. Could u please give a solution hoe to add calculated column with custom text in the pivot table. Thank you

[@DougHExcel]

Hi Geddla Vittal, thanks for the feedback, maybe another video in the near future to cover

[@selwynbegata8477]

Thank you this is really helpful

[@DougHExcel]

Glad it was helpful!

[@senakaladduwahetty9983]

Such a useful tip, thanks so much!

[@DougHExcel]

You’re welcome!

[@AkaExcel]

Excellent Trick! Thank You!

[@DougHExcel]

Hi Learn Excel, thanks for the comment!

[@debthomas1293]

Exactly what I needed. Thank you!

[@DougHExcel]

You're welcome!

[@ossiangranit4891]

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

[@DougHExcel]

not sure...I don't have mac version!

[@awaiskhan9448]

you are a rock star!!!

[@EstrellaJian]

Hi Doug, I can't add the new measure to values after successfully adding measure. What do you suggest? Cheers!

[@DougHExcel]

Sorry, don’t understand the comment/ question... but should be able to add to the source of the pivot and then refresh

[@spankydecat1700]

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.

[@TheBanjaara]

Fantastic video! Is there a way I can add an editable/dynamic comments column to the pivot?

[@DougHExcel]

maybe included something like this as a source for a pivot kzbin.info/www/bejne/rXnKp5KdjNxqn7M

[@lenzypetty1097]

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?

[@DougHExcel]

sorry haven't tested in Power BI...

[@sushmithashetty2302]

Excellent tip! I tried but I am not able drag that fx color tab to value. It is not showing in Value. Please help.

[@Lucy-mw3zi]

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

[@GeoffreyMeedin]

Does this work on Excel 2010

[@mashailfurqan5150]

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.

[@DougHExcel]

Try to format the cell for your display output. This vid covers for phone numbers but the concept is similar >> kzbin.info/www/bejne/gF64aGVoi5Z5n8k

[@mashailfurqan5150]

@@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.

[@sazanbimo6551]

Very nice work.

[@DougHExcel]

Thanks!

[@hazmanch4]

Solve my years of problem... like and subscribed..thanks

[@DougHExcel]

Thanks!!

[@nboisen]

Excellent tip! Thanks for sharing!

[@DougHExcel]

Hi nboisen, glad you liked it, thanks for commenting!

[@kuldeepsharma7060]

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.

[@DougHExcel]

You might need to download powerpivot and enable from microsoft.com

[@sujithsundaramoorthy8022]

What should I can mention in the delimiter instead of comma if I want the values in next line of the same cell.....

[@DougHExcel]

that would be a Ctrl+J to have a line break. I haven't tried it but give it a try.

[@tomhayes1181]

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?

[@DougHExcel]

Hi Tom Hayes, not clear on your experience, but you may want try to post on the mrexcel.com forum!

[@andersenandthdcommunicatio9688]

I'm trying this process however it isn't working. Does this max out at a particular number of rows?

[@DougHExcel]

Hi Marketing_AW, thanks for the comment! Max out? not that i know of...

[@meijinxlisondra3008]

Whenever i try to use add data model.. There is no add measure whenever I right click :(

[@cstrickley]

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

[@PAllen77]

@@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.

[@MANTRIXESTIMATEPROGRAM]

@@PAllen77 same with me

[@DougHExcel]

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

[@ahmedamin9722]

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]

Maybe the add to data model checkbox was not checked?

[@albertjen15]

you solve my problem. Thank you!!

[@DougHExcel]

You’re welcome, glad it helped!

[@adamwelch7641]

Hi, I'm using this with dates, should it work the same? I can't get it to work

[@DougHExcel]

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.

[@rafiausmani124]

Very informative. Great work

[@DougHExcel]

Hi Rafia Usmani, glad you liked it, thanks for commenting!

[@bangheadwall]

this works great but I have a series of formulas in my column that has "blank" but not "blank" cells as a result. ie. I have many associated rows with a "" place holder for data organization. When I run this concatenate on the values column I get this for example. ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,text i need,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, Is there a way to have the concatenate only select for rows with actual text?

[@bangheadwall]

for anyone looking for an answer excel bonanza addressed this and added a values formula in front to only take unique values from the listed column instead of pulling from repeats. This will work with unique values in your column, it worked especially well for me as I am using it to list notes for specific days in the pivot. =CONCATENATEX(values('Column1'[NOTES]),'Column1'[NOTES],", ")

[@Zeljko1977]

Nice tip. Would it be possible to show color instead of color name?

[@DougHExcel]

I haven't tried this but maybe look into conditional formatting kzbin.info/www/bejne/o5bSmKJ3lLKLo8k

[@osiriset1]

Is this function available on Excel 2013?

[@DougHExcel]

I think the data model capability has been around since Excel 2010

[@educational6621]

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)"

[@educational6621]

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 ?

[@DougHExcel]

These videos might give some insight kzbin.infosearch?query=custom+format

[@Michael-xx8bw]

Awesome, thank you.

[@DougHExcel]

You're welcome!

[@jerrydellasala7643]

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.

[@DougHExcel]

Hi Jerry Dellasala, thanks for the comment and adding to the thread!

[@victorespinoza6210]

Great video. Just what I was looking for!

[@DougHExcel]

Hi Victor Espinoza, glad you liked it, thanks for commenting!

[@GiuseppeDeRosa76]

Thank you so much, you save me!!!

[@DougHExcel]

You're welcome!

[@bush288]

Hi, Is " Add this to Data model option " not editable in Excel 2016? I am unable to select that option. Kindly help

[@DougHExcel]

Check if your version supports PP it: support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b

[@vjrams]

hi Dough, I have PowerPivot but still i cannot see the option "Add this to DATA Model" if I insert Pivot table and I dont find CONCATENATEX formula. Am using Excel 2010 Please HELP!!!

[@DougHExcel]

Check if your version supports it: support.office.com/en-us/article/where-is-power-pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b

[@paullugowski9092]

@@DougHExcel PowerPivot is support in Excel 2010 Pro but "concatenatex" function not exist in this version, any ideas ?

[@thirisangukannan6459]

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.

[@DougHExcel]

Thirisangu Kannan, that is very interesting...thanks for letting me know.

[@harighotad]

This facility is not available in OFFICE 10 ???

[@DougHExcel]

The data model was available since Excel 2013.

[@tienphamthanh4923]

Can you do it with removing region

[@harighotad]

sir I have MS Office 2010 and I didn't find option "Add this data to the Data Model" in Create Pivot table. What I have to do?

[@DougHExcel]

unfortunately I think you are out of luck. I think it was available in Excel 2013. You might need to download the power pivot add on from MSFT if it is still available.

[@harighotad]

@@DougHExcel Thanks for prompt reply

[@alfinnuha9733]

hello, can i make it distinct count text?

[@DougHExcel]

See if this fits >> kzbin.info/www/bejne/h57VgXlrp9WFj6ssi=ZhN2s4o1JKqtFy8C

[@jksan927]

Wonderful. Many thanks for sharing!

[@DougHExcel]

Hi jksan927...you're welcome, glad you liked!

[@johanatan788]

Thank you so much. you give me a solution

[@DougHExcel]

Happy to help!

[@FARUKYZ]

Thanks, very good.

[@DougHExcel]

Thank you too!

[@shikharrajgupta]

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??

[@slidetopoweroff]

Is there a way to delimit into a new row??

[@DougHExcel]

maybe one of these will help kzbin.info/www/bejne/h3y1hHltibJrbq8

[@slidetopoweroff]

@@DougHExcel thank you! Is there a way to delimit a list of commas in a ConcatenateX pivot table in a new row? Every time I try to use power query?

[@yarramkartheekreddy9163]

Thank you so much.. Excellent..very useful..

[@DougHExcel]

Thanks yarram kartheek reddy, glad it helped!

[@alfigilang4840]

Sir, I'm confused. In my excel, when I click right on the table instead of appears "Add Measure", but only appears "Remove From Active Tab". How to solve that sir?

[@DougHExcel]

when you were creating the pivot table was the add to data model check box is clicked?

[@Mei-yc9jy]

same! my data model check box is checked, but theres no 'add measures' option

[@Readersgirl]

@@Mei-yc9jy Have you solved this issue , coz im facing the same issue

[@alphauranium]

Your powers are like that of a god. Thanks for this lesson

[@DougHExcel]

Thanks 😊!

[@gotenksta]

Help please, with this method I can't group the rows

[@paulovable17]

amaaaazing!!! exactly what i needed!

[@DougHExcel]

Hi +paulovable17, glad you liked it, thanks for commenting!

[@DougHExcel]

Hi +paulovable17, thanks for the comment!

[@DougHExcel]

Hi +paulovable17, glad you liked it, thanks for commenting!

[@tomspreadbury2915]

Hi, I keep getting error messages saying the ‘x’ measure cannot be created because a column with the same name already exists, has anyone got a solve? Of course it exists, it’s the measure I’m using.

[@HuyenNguyen-pp4lv]

Can I get the multiple text values returned on separate cells instead of just a line break within a cell? for example: I have 4 columns, for each of which there are more text values to be returned. I'd like to have these text values returned each on one row on top of one another. I tried the "line break" trick in the DAX formular, it works that each text value is on a separate line but all in one cell. However, I'd like to have these each in one row. Is it possible?

[@DougHExcel]

I haven't tried it but, it could be possible. It's almost like making a "subtotal" row for each entry.

[@dantevale0]

Thanks!

[@DougHExcel]

Welcome!

[@quayzie]

Can this work with Mac Office 2016?

[@DougHExcel]

AFAIK PowerPivot is not available on Macs...🙁

[@yassirhusain]

@@DougHExcel OH GOD. What will happen to us Mac Users...

[@shubham0803]

In Macos there is no 'add to data model' checkbox

[@yzeuthium]

You know this video is legit when there are no dislikes! 👍

[@DougHExcel]

Hi Yzeuthium, thanks for the comment!

[@ca.lakshminarayanreddyjamb9087]

Awesome trick. Thanks for sharing.

[@DougHExcel]

Hi CA.Lakshmi Narayan Reddy Jambula, glad you liked it, thanks for commenting!

[@pipo441]

What if count value is 2 or bigger. The text will repeat x times....

[@buzzvoelker]

That is great - amazing tip. Thanks!

[@DougHExcel]

You're Welcome!