How Can I Show Text in the PivotTable Values Area Instead of Numbers? | Display Unique Text Values

[@fancypants2007]

Excellent instructions! Not too fast, and not too slow. And none of the important steps are glossed over. It is easy to watch it over and over to learn it, without wasting time or feeling that I need to skip ahead.

[@hobohunter666]

I cannot describe how much time you have just saved me, this excellent!

[@nabanitadutta6200]

I just can’t explain how much relieved I’m now… I was tryna fix it since evening so hopefully I’ll be able to finish my project by Sunday. Thank you.

[@Mauserratte]

Damn this was the best excel video I ever seen. Right to the point without any filler.

[@VeronicaSanchez-qt1zf]

I've been wanting forever to learn how to do this. Finally!!! Thank you

[@M4st3rm1nd_01]

This should be the most watched video in youtube! 😂 Thank you so much, friend!

[@ewawojciech-bu3be]

Thank you !! I was so overwhelmed with my new job and so stressed and your calming voice and simple instructions shows me that there is hope !! Have a wonderful day Sire! As you have just made mine ! :)

[@Odiiaras]

Additional tip for anyone using this trick: if you are doing this over a particularly large set of data, first build the pivot table off of a smaller sub-set of data in the table, perform all the steps, and then delete the "Grand Total" sections before adding the rest of the data to the table. The Grand Total sections can potentially run into the issue of exceeding the character limit for a single cell in Excel (32k), which will cause the Measure to fail out when added to the Values section. After you have cleared that out, copy the rest of the data into the table and refresh the pivot table, and you should be all set.

[@madhanhexp860]

Thanks Brother , This helped me out

[@rehanansari5674]

I am searching solution for this problem but I didn't understand what you are explaining. If you can elaborate further, will be thankful.

[@northretail]

THANKS SO MUCH ^^

[@theruxtonsmusic]

Thank you!!

[@swapna_learner]

Won't selecting defer layout help in such an instance to design and remove the grand total option?

[@trabin9]

Great Video. I"ve been looking for this feature for months! Very well described.

[@lijunchen]

This is a very useful video. I had thought text values could not be used in a pivot table. I used to categorize texts into numbers before using them in a pivot. This video proved me wrong. I wished I had known this earlier. I don't know why there is little information online about this useful feature.

[@shraddhabaeet3793]

Loved it! In the short time top notch! 💯💯❤❤

[@vladisan1]

This is extremely helpful and awesome as a video. Explanation and skill are next level.

[@VicSMeIsTeR]

Literally saved my life, thousand thanks

[@danfel00]

Wooowww, great video. Great instructive, super helpful. Thanks so much.

[@jenali5934]

Thanks, this helps me a lot with my reconciliation report. 👍👍👍

[@moonwillow167]

Amazing as usual - just one quick question, What if we want to show sum of sales made ,e.g @ 3:06 Dorie and Monica both made 3 sales, How can we show as 3Dorie, 3Monica etc. Thank you

[@9202100810]

Very useful, thank u for sharing. And the "values" trick, was EXTRA helpful

[@mariamalnauimi]

Don’t know how to say thanks really it is so helpful million thanks 🙏🏼

[@aileneiraluca8553]

Great video! Thank you for the explanations!

[@okiaulia3104]

how to remove the grand total. since in my pivot table have 2 values, so it shows 2 grand total. and i want to remove the concate text one.

[@patriciazimmerlin1957]

This worked perfectly for me! Thank you

[@narcisabolbos9164]

Many, many Thanks. Such great functionality & explanation!

[@roskam1069]

Thank you so much, this is awesome for me to learn today! Thank you, thank you, thank you 🎉❤

[@IvanCortinas_ES]

As always, excellent quality and knowledge in your videos. Thanks for this video with DAX, Chester!!

[@tsamt4889]

Been banging my head trying to work this out, thank you soooo much!!!!

[@HowToAnalyst]

Thank you Chester, very helpful

[@hoanphamquoc4711]

Really helpful, thank you so much.

[@SandraDeWit-vk4ox]

Very good instructions! Thank you. Would it also be possible to start each new item on a new line in stead of a delimiter like a comma?

[@amalmalkawi4076]

Thank you but how can we show each of the data in a separate line instead of having them all together in one line?

[@chinmayee9419]

Thanks!

[@xyz-yu3xm]

Excellent tutorial sir, many thanks!

[@trackspin5597]

Thank you very much. I have been searching the way to express lists in pivot. It's so much helpful.

[@simonewen9185]

This is great, thank you. I have a filter set up to change the year I am lloking at but each time the columns change width to max (255), is there anyway to prevent this to keep with column width?

[@MrPileggi86]

Is there any known way of rather than concatenated results, any additional text value is produced on an additional row?

[@pedrozaidhaft]

THANK YOU!!!!! You just saved the day!! Actually, it's been 3 days I was trying to find a way to do that.

[@her.telden]

Thank you so much, do we have a chance to sort of them?

[@RobertTate-y6o]

This is a big help - thank you. When I use the Subtotals or Grand Totals, the alpha values are aggregated. Is there a way I can have those values not display on my report? Thank you.

[@jorparts]

I'll try but i think you save my day. Thank you very very very much!!!!!!!!!

[@leighskilton4103]

Thanks, found this very useful.

[@BimsaraChandranath]

you are a life saver🎉🎉🎉

[@sallyho146]

Thank you. Love Dax concat. Question- I have periods (Jan- Dec) instead of product category in column field, use Dax formula to show the census status of the residents/patients whether they are admitted or Leave or etc in the column for each period. Then I have row grand total in text. If I want column total in value to show how many residents/patients we have each month, can I do that and if so, what formula can I use?

[@kirtylol94]

Worked perfectly. Thank you for sharing it!

[@GeraldWhalley]

Worked well for my requirement thanks. One problem I did encounter - I set conditional formatting for the results but the condition won't save for multiple cells and so wipes the formatting on refresh. This doesn't happen when the the values are row or column headers in a normal pivot table. Is there a way round this?

[@wilfredderksen]

I think the explanation is perfect... after I tried it on Windows. Of course Microsoft doesn't make this available in the Mac variant.

[@chrismeryportorreal8233]

This video is perfect!! Thank you so much! *Subscribed*

[@skeetabomb]

Been looking for a way to do this for years!

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

[@wecanwin2307]

I'm also using 2016

[@johnkoehl10]

I have one question - I am using this to organize some sales data with product names (in the cells) across product categories (columns). Is there a way to keep the list of product names in the same order as the sales data (Most sales to Least Sales) when the pivot is collapsed?

[@erxvlog]

Very helpful. Thanks.

[@ethanstock5088]

How do you make the Grand Total Count the unique names in the pivot table?

[@xiabrughx1382]

It's very useful and easy to understand. Thank you! May I ask whether there is a new way now to list down all the values as not concatenated? Like the rows will repeat based on the text values. I'm curious. Thank you and happy new year!

[@maggiefisk9271]

Looking for the same solution!

[@Reptile8488-j5u]

absolutely Brilliant. Thank you for this.

[@pookaloo0087]

Thank you...this is really helpful. The only issue I am having is when I collapse the rows, all the values are shown in the value fields, separated by a comma, which makes the collapsed pivot table look untidy. Is there away to hide the text in the summary?

[@karlagoris2802]

You are the best! You have saved me so much time, thank you!

[@ChesterTugwell]

Glad I could help!

[@jamespyle6398]

For some reason when I do this half the time it works, other times it doesn't. Is there a limit to data/rows that this can work with? I create my measure and it's correct, but when I drag it to values nothing happens (it acts like it's working but then the column never appears)

[@NealHartsChannel]

How do you add the data to the data module if you already created the pivot table? I didn't check off the "Add this data to the Data Module" when I created the pivot table.

[@jasonorr3121]

I can't seem to find the ability to create a measure, no table pops up for me to right click on (Transactions in your example). Is there a way around this?

[@cherifaboukheir4757]

i am trying this formula but the messages "The pivot table isn't available because it exceeds the total number of characters that a cell can contain" can someone help ?

[@denissehernandez842]

SAME ISSUE HERE :(

[@thereelaccountant9246]

Hey Chester quick question, how would one fix the grand total at the bottom so it doesn’t show a cluster of comments like shown in your video? I’m having the same problem.

[@plavali_znaem]

How do I know which delimiter to use?

[@azizaadnan6191]

this is great! However when I put something in "Filter" is does not allow me to actually filter, it only gives an "All" option, how can I fix this?

[@muhammadnazar2978]

Thanks for Video with DAX

[@halliemitchell4687]

Does this work with Macs? I'm not see the "add this data to the "Data Model".

[@her_bloem687]

How do you get the list to drop down when typing your formula?

[@christianday]

How does this work on a mac?

[@rohitsaini9467]

Can we sort this concatenat value . Please reply i need so much.

[@MichelleWigley-f9t]

How do I delete the Grand Total columns and rows?

[@EdoardoResta]

Thank you so much!

[@SP-ru3rb]

I don't have any 'add data model' in mac's :(

[@jamespyle6398]

Mac doesn't support it, sadly.

[@itmetine]

This is very helpful! Now my problem is, I'm doing a variance analysis report so I need the grand total. In the grand total of a "comments" column, is there a way to only show one specific comment among the list? I want to show only the main reason for the variance. (I find my question confusing) I hope someone can answer 🥺

[@zennonline816]

Thank you so much for this!

[@rehanansari5674]

Well your method is definitely working but there's a glitch. When i am showing grand total, even this "measure text column" is getting sumed up. Which is making entire effort ugly. I have almost 100+ rows and all the data is shown in the grand total cell of measure column which is making the entire report unusable.

[@franciscocampos6994]

saved my life

[@sequinsmtg]

One thing to add: Seems like you need to make sure your file is saved as a .xlsx otherwise you will not get the option to add data to the model when summarizing the pivot table.

[@doamsi]

hello i cant see the ADD MEASURE when i click right :-( help me pls

[@JenniferMedordi]

rt click on the table name at the top of the fields. The icon to the left resembles a calendar. (its a table)

[@AREARE1977]

Even i cant get this option, may be because it is excel 2013? Can someone reply?

[@wymanhaha]

you help me to fix the big problems

[@sudhakardevaraj6066]

Awesome sir

[@ydherdn]

Thank You

[@Peaknook]

Thank you ..

[@nakulkumardahiya4289]

Measure is not appearing

[@PFBruno3]

BRILLIANT!!

[@goldybaroa372]

EXCELLENT!

[@hemanthbatchu]

😃

[@ernessst91]

Thank you sir 🫡

[@juliabachini]

Thank you very Much!!