No doubt, Nyuklehead, Flash Fill is one of the most beloved features. But know your consistent pattern, because if you do not know you data and the pattern in you data , sometimes Flash Fill will give the wrong answer : ()
The last example was so good. I have always used =LEFT() or =RIGHT() to get the numbers, but they always came out as strings. Now I know how to get them. Thanks!
Yes, Flash Fill is great for simple extraction!!! Also, if you use LEFT and RIGHT to get a number, it is easy to convert text to number with (as an example): =LEFT(A2,2)+0
Sir, Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. You are the best. Thanks a lot to keep love and patience in your teaching field.
That is cool to hear, and it makes sense that you like it the most. Before Microsoft invented it, they searched forms for most common type of questions, and basic cleaning data in a column was one of the most common type of questions, so they worked hard to invent Flash Fill. Thanks for your support, Devesh!!!!
Great video. I'm sure that the flash fill function will be coming in handy! I used to use Ctrl+Shift+L to reset filters, however, I found that when you have many columns in a table it's easier to use Alt+AC to clear the filter because using Ctrl+Shift+L will automatically scroll the entire worksheet to the last column of the table when you try to reset (which can be frustrating). Alt+AC keeps the table positioned on the column you're actively filtering. Looking forward to continuing this series!
You have answered all my problems. I have suffered at work because of every thing that you are teaching here. Thank you for such well detailed , well explained videos.
Very well said, meghna!!! It is so true that so many of the working people in the world have to suffer through Excel tasks. This is because education has failed most graduates and they become employees with the necessary Excel skills that so many workers on the planet earth need. That fact is what drove me to create classes like this that tell a story that tries to reveal the power of Excel through examples that come straight from all the problems that people have with Excel. Simple things like Number Formatting, Locking Cell References, ROUND Function, Sorting and PivotTables, that almost none of the Excel users in the world have, are taught here, and taught with the correct concepts and whys, for example Number Formatting as Facade, Locking References to Save Time, Sorting the whole table rather than a single column (wrecks the whole table) or ROUND to prevent those devious Number Formatting "Few Pennies Off" events.
@@excelisfun Yes you are so right. Thanks to you I feel bit confident & I know If I get stuck somewhere I can come back to these videos & take help. Thank you for being so generous with your knowledge. :)
Flash Fill is amazing.... I dint even know the option was existing and always used to go with "&" Amazing.... i am gone start using flash fill going forward. Excellent videos and examples... learning a lot from your videos... Thank you...
You are welcome! Glad that Flash Fill will be handy : ) Thank you for the support going forward with comments and Thumbs Up on each video that you watch!
When flash filling copied data that contained Watch Time and %, using Flash Fill after just inserting first data point produced a wrong answer, it filled the % instead of watch time. So in that case, insert multiple data points so Excel can better grasp what you rly want... As you showed in the video, and it worked. I mean, kind of intuitive but you know:) Great stuff as always.
@@excelisfun Hi Mike, I had not seen it until now. Thanks for letting me know. I will likely implement the solution this week. Keep up the fantastic work.
ExcelIsFun absolutely but i mean watching you on a video after video is not enough for me because you are the best teacher I've ever met thank u so much Mr. Mike
ExcelIsFun You deserve it, deserve a lot more, if I must say. This is the least I could do. I have learned a lot, and still am learning from your videos. So keep up the good work Mike the EXCELlent :P
Another great video! Thank you for your continued efforts. I also wanted to ask you if you can tell me how I can get the keyboard shortcuts to appear in the ribbons like I see you have? I want to learn more shortcuts and I think having them show up when I use the ribbon would help me. Thank you so much!
I think what you mean is pressing alt which will preview the buttons necessary to select a tab/enact a feature. This is done in succession i.e., you press alt, then press the tab key (if needed), then press the function key. E.g., to make a pivot table, have a cell selected in your proper data set then press ALT, then N, then V!
Flash Fill is awesome, but not available in the Mac version. Mike it might be an idea to preface your videos with which OS version the tutorial applies to. I use the Mac version at home and thankfully the Windows version of Office 2016 at work for data modelling... I thumbed up!
As for Mac, I will not preface because Mac is no longer relevant in the Business World. All classes at Highline College must teach the relevant tool for the business world. Lucky you have Windows version at work. As you probably know for the last five years, Apple Excel has not received the great new Business Analysis Tools (like Flash Fill, Power Query, Power Pivot, DAX Formulas and so on), but Windows Excel has. It has been a sad journey for me because I started my business career off running a business with Mac Excel (many years ago), but slowly got chased off in my Accounting and Teacher careers. It is too bad that the Mac Excel and Windows Excel are not the same : (
I am not sure how to force the Filter Feature to do it. But you can add a Helper Column with the OR Function, then filiter the Helper Column to show only TRUE.
ExcelIsFun it is possible for it not to work. It won't work if your data is not filtered. Try filtering your columns and then using it. It's just the same thing as the Clear button in the data tab.
Trust it when the pattern is consistent. There are still times when it does not work. Although, I think that Microsoft has improved the algorithm, because the example with multiple names in this video used to not work, but now it seems to work with just a single example...
Michael, I'm wondering what the best way to link a notes page to a daily log. Something that would be displayed when I bring up a past day. I thought about using the comments on each day, but I would like this information to be searchable as well.
@ExcelisFun Team- Love your videos i have a small correction in Excel file "EB23-SortFilterFlashFillStart" Date is 09-01-2013 not 09-01-2011 which i want to bring to your notice. Sheet HW(3) "Filter to show Bellen Boomerangs sold on 1/9/11, then copy and paste onto the sheet named "PasteFilter".
@@excelisfun Hi Mike this issue still prevails in HW(3) tab.Isn't it supposed to be 1/9/13? In the HW(3an) yes it was corrected but we aren't supposed to peak into our HW answers before we solve, no 😜? Thank you for this RUclips channel. Forever grateful!
I totally agree. AND... This has been true for 20 years. BUT... the sad news is that most computer users on the planet earth who are require to know this content, and most do not. That is why we still have to teach it in college. Thanks for your support, Jeffrey, with your comment, Thumbs Up and SUb : )
On the last example, I jumped the gun and used Ctrl + E to Flash fill after ONLY entering the "Watch Time" of 15 for India. Excel then Flash Filled the wrong time of '21' for United States. It extrapolated the wrong numbers the rest of the way down! Curious how it decides.
It decided on only one example. The rule for Flash Fill is; 1) you must know all the patterns in your data, 2) you must give it enough examples to cover all the patterns, 3) then you use Ctrl + E. In your case with only one example, the behind-the-scenes "Program By Example" (as Microsoft calls it), did not have enough examples to build the program to clean and transform your data.
hi #ExcelFun love your channel. you have done great work. i am working in manufacturing and LLC company more than thousands items (eg. item code starts 000001, 00001, 0001, 001 )all are different type quantity and price item please give advice how to data analysis foe these type of item code items.
Many in Australia. So far this month, 21,365 views from Australia. What is incomplete about the data set? You can use various RANK functions. I have many videos on ranking, here is one : ruclips.net/video/MSrfXRcH4YM/видео.html
Do you mean that the videos come out too quickly for you to study? If so, the good news that they will be posted for years and years to come so you can watch any time : )
Thank you, this was incredibly helpful! Can't wait to use flash fill now that I know about it.
No doubt, Nyuklehead, Flash Fill is one of the most beloved features. But know your consistent pattern, because if you do not know you data and the pattern in you data , sometimes Flash Fill will give the wrong answer : ()
Thanks for making me look awesome at work everyday. Best classes I have taken.
You are welcome! That is great news that you are looking good at Work! Thanks for making the videos look good with your comment and Thumbs up : )
Yes, they are!
Thanks, pmsocho!!!!
Although I used to use these features a lot so far and today I learned quite useful information form you, thank you!
You are welcome!
Flash fill is superb deadly concept!!!!!!!!!!!!!
: ) : ) : ) : )
best teacher ever!
2 More to Go. Forget binge-watching Netflix. Binge-watching ExcelisFun
No Netflix. No Mowing... : )
Really useful video and course, absolutely brilliant! Thank you so much for all your efforts, I am learning so much on your channel! :)
I am so glad my free video classes help!!! I do charge a thumbs up on each video ; )
The filter option and flash fill are amazing! Thank you Mike!!!!!
Yes, Flash Fill is a game changer : ) At least in comparison to how we used to do it...
I always learn something new from your vedioes 😊
Yes!!!!
Woo! Another down! Thanks, man!
Gotta love that Flash Fill! Amazing!
For quick cleaning on consistently patterned data, there is nothing better! Thanks for the support!
The last example was so good. I have always used =LEFT() or =RIGHT() to get the numbers, but they always came out as strings. Now I know how to get them. Thanks!
Yes, Flash Fill is great for simple extraction!!! Also, if you use LEFT and RIGHT to get a number, it is easy to convert text to number with (as an example): =LEFT(A2,2)+0
THANK YOU TEACHER OF THE DECADE!
You are welcome!!!!!! : ) : )
Sir,
Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. You are the best. Thanks a lot to keep love and patience in your teaching field.
You are welcome for the videos, Excel and teaching! Thank you for your support : )
*Flash fill is a tool that I personally think I Liked the most!! thanks for sharing this tool with us.*
That is cool to hear, and it makes sense that you like it the most. Before Microsoft invented it, they searched forms for most common type of questions, and basic cleaning data in a column was one of the most common type of questions, so they worked hard to invent Flash Fill. Thanks for your support, Devesh!!!!
@@excelisfun Your most welcome @ExceIsFun
I'm learning more with your videos than school. Thanks Mike!
I learned many new things even for functions that I am using for years like a simple filter.
You are God!
Thanks, buddy.
You are welcome, Nima!!!!! Keep going - you are on a roll with all of your Excel Learning : )
Great video. I'm sure that the flash fill function will be coming in handy!
I used to use Ctrl+Shift+L to reset filters, however, I found that when you have many columns in a table it's easier to use Alt+AC to clear the filter because using Ctrl+Shift+L will automatically scroll the entire worksheet to the last column of the table when you try to reset (which can be frustrating). Alt+AC keeps the table positioned on the column you're actively filtering.
Looking forward to continuing this series!
Thanks for that useful tip, Elizabeth B!!!!
Thank you again, I learn every time
You are welcome! Thank you for your consistent support : )
You have answered all my problems. I have suffered at work because of every thing that you are teaching here. Thank you for such well detailed , well explained videos.
Very well said, meghna!!! It is so true that so many of the working people in the world have to suffer through Excel tasks. This is because education has failed most graduates and they become employees with the necessary Excel skills that so many workers on the planet earth need. That fact is what drove me to create classes like this that tell a story that tries to reveal the power of Excel through examples that come straight from all the problems that people have with Excel. Simple things like Number Formatting, Locking Cell References, ROUND Function, Sorting and PivotTables, that almost none of the Excel users in the world have, are taught here, and taught with the correct concepts and whys, for example Number Formatting as Facade, Locking References to Save Time, Sorting the whole table rather than a single column (wrecks the whole table) or ROUND to prevent those devious Number Formatting "Few Pennies Off" events.
@@excelisfun Yes you are so right. Thanks to you I feel bit confident & I know If I get stuck somewhere I can come back to these videos & take help. Thank you for being so generous with your knowledge. :)
Flash Fill is amazing.... I dint even know the option was existing and always used to go with "&"
Amazing.... i am gone start using flash fill going forward.
Excellent videos and examples... learning a lot from your videos... Thank you...
You are welcome! Glad that Flash Fill will be handy : ) Thank you for the support going forward with comments and Thumbs Up on each video that you watch!
When flash filling copied data that contained Watch Time and %, using Flash Fill after just inserting first data point produced a wrong answer, it filled the % instead of watch time. So in that case, insert multiple data points so Excel can better grasp what you rly want... As you showed in the video, and it worked. I mean, kind of intuitive but you know:) Great stuff as always.
Glad it all helps : )
Thank you!
You are welcome, Kizo!!!
Thanks a billion for your videos - you are God sent.
You are welcome a billion times!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Great video again mike!
Guru ji you are great . I am very happy that you are teaching me on RUclips.
You are welcome! Thanks for your support with a Thumbs Up and Comment on each video that you watch and a Sub : )
FlashFill is awesome!
I agree, Chris : )
These are great to know. Thanks Mike
Yes, there are !!!!! Didi you get my late comment on MSPTDA #8?
@@excelisfun Hi Mike, I had not seen it until now. Thanks for letting me know. I will likely implement the solution this week. Keep up the fantastic work.
Excellent video as always! Thak you!!
You are welcome. I am glad that they are EXCELlent for you ; )
This flash fill was amazing! Thanks again!!!
You are welcome, Nikoloz!!!
Tanq Sir one more nice video & only 2 more videos left for me to complete the basics course. :)
Wow! Flashfill is really amazing I didn't know about it before watching your video...
I love it especially the way you explain it very clearly........
You are welcome! Thanks for your support with comment and thumbs up : )
big THAAAAAANK YOUUU
A BIIIIIIG You are Welcome, Abdulaziz!!!!!
Thank you for the lectures.
you make the Excel fun
Keep 'em coming Mike!
You bet I will! Thank your for your consistent support with your Likes and comments : )
Such an amazing video. Plz keep making more videos
Many thanks for the lesson!
Many you are welcomes, Elmar!
Flash Fill. Amazing Feature!!!
Yes it is fun and easy and fast !!!
Thx 🙏
You are welcome - so glad to see you going through the whole class : )
Thank U Teacher.
You are welcome!!!! : ) : )
Thank you very very much
You are welcome, Chamchijjige!!!
i just wish you are my teacher so i can see you everyday giving me more and more info
Well... aren't I already your teacher, when you watch and learn ?
ExcelIsFun absolutely but i mean watching you on a video after video is not enough for me because you are the best teacher I've ever met thank u so much Mr. Mike
I wish you were in the classroom here at Highline too - it would be fun : )
ExcelIsFun oh my god unlucky guy
Lucky, I think...
late wish but still, Happy teachers’s day (from India).
Learn learnnn learnnnn.... Fun funnnn funnnnn
Awesommmmmmmmmmmmmmmmmmme!!!!
Thanks Mike, another great video!!!
You are welcome, Scott!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Excellent video. Thank you very much!
You are welcome very much! Thanks for your support with comment, Thumbs Up and Sub : )
Learn Is Fun
great video
Glad you like it : ) : )
Amazing, Amazing, Amazing
Thanks, thanks, thanks!!!! : )
yoo, thank you sm😭🫂
Another great video, thanks!
You are welcome! Thanks for your support!
you are excel @ teaching excel
I am NOT!!!!! Teach Excel is not excelisfun. That lame web site stole my name and bought excelisfun . com. Do not use there services!!!!!
This is so amazing. Thanks Mike
You are amazingly welcome! I really appreciate that you one of the few viewers that always comments and Thumbs Up : )
ExcelIsFun You deserve it, deserve a lot more, if I must say. This is the least I could do. I have learned a lot, and still am learning from your videos. So keep up the good work Mike the EXCELlent :P
Yes, I am so appreciative of your support : ) : ) ... because the majority of watchers don't take the time to support : (
Sadly, yes. In the beginning I was aslo one of them, but then I realized how wrong I was, so just trying make up for my part
Hey Mike. You thinking about writing a new book by the way? Also I've started teaching excel @ my workplace all thanks to you.
Thant is great that you are teaching others the fun of Excel! I have no plans of new books, cuz I like making videos more than writing books...
it is brilliant
Glad you like it!!!
Great video! Do you think about making some tutorials for Google sheets?
No. I don't have time, and I don't use them so without knowledge of how to use them I can't make videos : (
thank you Sir Excellent
You are welcome! Thanks for your consistent support : )
Another great video! Thank you for your continued efforts. I also wanted to ask you if you can tell me how I can get the keyboard shortcuts to appear in the ribbons like I see you have? I want to learn more shortcuts and I think having them show up when I use the ribbon would help me. Thank you so much!
I am not sure. Maybe in options? You might try posting here: try this great web site: mrexcel.com/forum
I think what you mean is pressing alt which will preview the buttons necessary to select a tab/enact a feature. This is done in succession i.e., you press alt, then press the tab key (if needed), then press the function key. E.g., to make a pivot table, have a cell selected in your proper data set then press ALT, then N, then V!
I want to paste data copied adjacent to filtered cells ONLY. But data is getting pasted into hidden cells also, any solution please.
Thanks alot
You are welcome a lot! Thank you for your support : )
Flash Fill is awesome, but not available in the Mac version. Mike it might be an idea to preface your videos with which OS version the tutorial applies to. I use the Mac version at home and thankfully the Windows version of Office 2016 at work for data modelling... I thumbed up!
Thanks for the continued support : )
As for Mac, I will not preface because Mac is no longer relevant in the Business World. All classes at Highline College must teach the relevant tool for the business world. Lucky you have Windows version at work. As you probably know for the last five years, Apple Excel has not received the great new Business Analysis Tools (like Flash Fill, Power Query, Power Pivot, DAX Formulas and so on), but Windows Excel has. It has been a sad journey for me because I started my business career off running a business with Mac Excel (many years ago), but slowly got chased off in my Accounting and Teacher careers. It is too bad that the Mac Excel and Windows Excel are not the same : (
Good job boss
Glad you like it : )
Hi Michael, love the videos - is there a way to do an OR filter across columns, say i wanted all entries by Alma and any that sold a Chevy? Thanks!
I am not sure how to force the Filter Feature to do it. But you can add a Helper Column with the OR Function, then filiter the Helper Column to show only TRUE.
Very logical, thank you!
Alt A C is better than ctrl L L for tables than are bigger than the viewing window
I can't seem to get Alt, A, C to work!?!?! I tried on a proper data set and on an Excel Table...
ExcelIsFun it is possible for it not to work. It won't work if your data is not filtered. Try filtering your columns and then using it. It's just the same thing as the Clear button in the data tab.
I should trust Flash Fill more often.
Trust it when the pattern is consistent. There are still times when it does not work. Although, I think that Microsoft has improved the algorithm, because the example with multiple names in this video used to not work, but now it seems to work with just a single example...
Michael, I'm wondering what the best way to link a notes page to a daily log. Something that would be displayed when I bring up a past day. I thought about using the comments on each day, but I would like this information to be searchable as well.
I am not sure. For back and forth dialog to get Excel solutions try this great web site: mrexcel.com/forum
@ExcelisFun Team- Love your videos i have a small correction in Excel file "EB23-SortFilterFlashFillStart" Date is 09-01-2013 not 09-01-2011 which i want to bring to your notice.
Sheet HW(3) "Filter to show Bellen Boomerangs sold on 1/9/11, then copy and paste onto the sheet named "PasteFilter".
Thank you very much! I have fixed it so that the Team will have more fun! Thanks, Shravan!!!
@@excelisfun Hi Mike this issue still prevails in HW(3) tab.Isn't it supposed to be 1/9/13? In the HW(3an) yes it was corrected but we aren't supposed to peak into our HW answers before we solve, no 😜?
Thank you for this RUclips channel. Forever grateful!
@@vishnu8899 I am sorry, I can not find the error in the worksbooks.
Are these 25 videos a college class, they should be a HS required class for tenth graders, this info is so prevalent in the world today.
I totally agree. AND... This has been true for 20 years. BUT... the sad news is that most computer users on the planet earth who are require to know this content, and most do not. That is why we still have to teach it in college. Thanks for your support, Jeffrey, with your comment, Thumbs Up and SUb : )
where could we find ms access videos
On the last example, I jumped the gun and used Ctrl + E to Flash fill after ONLY entering the "Watch Time" of 15 for India. Excel then Flash Filled the wrong time of '21' for United States. It extrapolated the wrong numbers the rest of the way down! Curious how it decides.
It decided on only one example. The rule for Flash Fill is; 1) you must know all the patterns in your data, 2) you must give it enough examples to cover all the patterns, 3) then you use Ctrl + E. In your case with only one example, the behind-the-scenes "Program By Example" (as Microsoft calls it), did not have enough examples to build the program to clean and transform your data.
Thanks for the support with your comments, Thumbs Up and Sub : )
hi #ExcelFun love your channel. you have done great work.
i am working in manufacturing and LLC company more than thousands items (eg. item code starts 000001, 00001, 0001, 001 )all are different type quantity and price item please give advice how to data analysis foe these type of item code items.
Glad you like the videos : ) I have zero idea how to answer your question. Sorry.
Do you have many watching from Australia 🇦🇺 or am I the only one now? Your data set looked incomplete. If so, how do we rank?
Many in Australia. So far this month, 21,365 views from Australia. What is incomplete about the data set? You can use various RANK functions. I have many videos on ranking, here is one : ruclips.net/video/MSrfXRcH4YM/видео.html
How did you activate flash fill?
The best version of Excel to get is Office 365.
I have some questions ( I need someone intermediate to answer)
Try this great Excel question site: mrexcel.com/forum
Aw crap flash fill is not there in 2007
It is only in 2013 or later.
a
Glad you are loving the Data Analysis in Excel : )
welcome..but ı couldnt keep up with your pace :)
I do not understand what you are trying to communicate. Can you re-state?
I mean that you are so fast that I dont have time to learn everything as fast as you teach..I appreciate your hardwork. I hope now, okey?
Do you mean that the videos come out too quickly for you to study? If so, the good news that they will be posted for years and years to come so you can watch any time : )
I always learn something new from your vedioes 😊
Thank you!
You are welcome!!!!