Excel Magic Trick 1118: MODE for Text Values, Extract Word That Occurs Most Frequently

[@excelisfun]

Excel Magic Trick 1118: MODE for Text Values, Extract Word That Occurs Most Frequently Download Excel File: people.highline.edu/mgirvin/ExcelIsFun.htm See how to Extract Word That Occurs Most Frequently: 1. (00:07 min) MODE calculation for text. 2. (00:57 min) COUNTIFS function for basic mode calculation 3. (01:38 min) INDEX, MODE and MATCH function (with function argument array operation) to extract the word that occurs most frequently when column of data contains text only. 4. (06:17 min) INDEX, MODE, MATCH, IF and ISTEXT function to extract the word that occurs most frequently when column of data contains text only. 5. (09:32 min) Formula to extract multiple text / word modes values that uses the functions, INDEX, SMALL, MODE.MULT, MATCH. Bill Szysz comes to the rescue again!!!!

[@christopher5148]

You are a genius my friend

[@excelisfun]

Juts having fun with Excel ; )

[@dharmendarrana4191]

Great Sir

[@excelisfun]

Glad it helps!

[@myrabbitispink]

i like how far you break it down. you are definitely knowledgable on this subject

[@excelisfun]

Glad it helps, C S!!!!

[@rp8253]

amazing, and explained so simply.

[@pmsocho]

This is awesome. I talked to Bill about that yesterday and booom - we got the video from you! Thanks ExcelIsFun and Bill Szysz!

[@excelisfun]

It is great to be part of our Awesome Online Excel team!!! Woo Hoo!!!

[@stephenmorgan5535]

This is great thanks. One question, is it possible to work this formula over multiple columns. I have a similar sheet but my 'Survey Data' are inputted over 3 columns. For various reasons I cant collate the 3 columns into one.

[@jpguitar588]

Can i use the mode for text with sentences?

[@rajanrajan5734]

Hi. How do we list the second most frequently used text value instead of the first?

[@myrabbitispink]

this works great for when each cell only contains one word/ one reoccuring string of words and nothing else. however when you have different full sentences in each of your cells and try to filter out the most frequently used word across those it doesnt work any longer. any suggestions?

[@PlanetNinetyTwo]

This is incredible

[@cermitwithac173]

GENIUS!!!!!!!! Thank you so much🔥

[@excelisfun]

You're welcome!

[@friedchickenbucket]

Hi, would you know how to apply this to a situation where I need the most occuring text within a specified date range? If Survey Data also had a date column, I want to know what was the most common occuring word in the year 2016. If you can help, that will be great. Thanks

[@jacquesjacques6455]

thanks, this video really saved me

[@brettconway1378]

Absolutely brilliant, just what I needed, probably one of the best Excel Video's I have watched!! Thanks for such a clear explanation ;o)

[@excelisfun]

Are you subscribed? Maybe not, because I just posted an even better way to do this in yesterday's video and you would have seen it if you were subbed. Here it is for you: kzbin.info/www/bejne/pYq6e62jmayhsJo Let me know if this newer method works for you : )

[@ExploringExcelAmey]

Mode Function is awesome

[@excelisfun]

Glad you like the video!

[@ExploringExcelAmey]

yes i like all your videos

[@KutlwanoM9152]

Thanks bro You are a genius 👊

[@sndp743125]

Hi mike you are awesome, hey i have a question that is that any array formula which can return random selected values from a column without using helper column.

[@sumx1972]

Fabulous stuff. Magic right there! Tx

[@excelisfun]

Glad you like it!

[@citizencr4o]

Thank you so much!

[@excelisfun]

You are welcome so much!!!

[@davidbartonjr]

Crazy! I Love it! Don't really need it, but I honor and salute guys like you! Thanks!

[@marthadeojeda6616]

Thank you!!! So easy to follow.

[@osmondmolina7322]

does this require a specific version of excel? Not working on my file now.

[@asandaswaartbooi8538]

I am using excel 2013, when I click the left arrow button, the position is not switched to the left direction.

[@MohammadTaha]

Sir, when I click on the link below the video to download a copy of the excel sheet I receive a "save as" dialogue box and it saves a web page that does not open, Why couldn't I download the excel sheet? did you make any changes to the platform you upload your files on?

[@excelisfun]

I am not sure. Did you try to download with Internet Explorer?

[@MohammadTaha]

I will try that now

[@MohammadTaha]

ExcelIsFun Ah! that worked, thank you for the great video

[@jwb4291]

My range is all text and I altered this formula to fit my range butttt it kicks back "0" every time. No idea why.

[@bobwiebe4019]

This is awesome! I cam up with =INDEX($A$11:$A$640,MODE.MULT(IF(ISTEXT($A$11:$A$640),MATCH($A$11:$A$640,$A$11:$A$640,0)))) and it works great. But I am using it on a filtered list, how can I add the subtotal function or a function like it where it will change the list when filtering?

[@leonidkoyfman814]

If in case of the mixed value types list we desided to treat all values as a text, the formula will be =INDEX($C$19:$C$25&"",MODE(MATCH($C$19:$C$25&"",$C$19:$C$25&"",0))) and it doesn't need SCE. We also might need to show something more descriptive than just #N/A when all values are unique and there is no mode.

[@BillSzysz1]

Cool !!! :))))

[@BillSzysz1]

Thanks Mike :)))

[@excelisfun]

No, Thanks to you!!! :)))))))

[@Amatureithelpstation]

Hello. If I submit a video question would you possibly have time to answer it?

[@excelisfun]

Not right now, I am on vacation, away from my computer with Excel and video recording. For urgent questions: mrexcel.com/forum

[@jawwadchanna]

Not working. The first two steps work completely fine when I add Index function I get an error: There's a problem with this formula

[@duanedonaldson2262]

Only guessing but Bills lasy name looks like a "Call SIgn", sounds like German for SSSSSSSHITTTTTTT if you know a little German, IMHO. Thank you for all the awesome lessons.

[@abdulazizalzahrani7757]

Thank you Mike -- Great Trick as always. :) Quick Question: What programs do you use to record your lessons ? And how can you show the buttons on the screen (like Ctrl + C etc.) ?

[@excelisfun]

I use the program Camtasia. The Caption feature in Camtasia allows you to enter a keyboard.

[@ShanmugaVelondigital]

Savior 🙏

[@2moomoo11]

"=INDEX(M:M,MODE.MULT(MATCH(M:M,M:M,0)))" I keep gettting #N/A, Column M: Data Water Water Water Water Apple What could be the issue? New version?

[@NotAnYoutubeChannel]

I loved this video so much I could marry you