Hello everyone, this video covers the topic of flash fill (Ctrl + E as a shortcut) in Excel. This will not replace the need for all formulas but it can be a great way to complete simple tasks that follow a pattern. I appreciate all the feedback and comments below and will look to implement your thoughts. Thank you for the support and wishing everyone a great day.
Yes, definitely not a replacement for formulas. It's also kind of dangerous. You assume that the information is a formula and yet when you correct the one of the last or first name fields, the name doesn't get updated. Worse, if you don't know this, you won't notice it.
As is, I consider it a novelty and not something I would want to have to spend time thinking about as to when I was willing to accept it versus putting in a formula. I would be much happier if it gave the option to put in the formula that it "determined" was what you wanted. It is sort of like writing with a pen rather than with a pencil and eraser. You just know that if you use it, someone is going to want a tweak made.
+TheMightyNoseGoblin You're so lucky to have found this tip so early in your learning process! A lot (most?) of us have been doing these things without Ctrl + E for _decades._
That's good introduction. But also you could have mentioned the technical name for this which is "Flash fill" in excel. This will help people to explore it in more detail.
The fact that it fills the fields with the current values rather than dynamically recalculating them means the process is human-controlled every time. Formulae take a little more time to setup, but will recalculate every time.
Congratulations! You just broke the minds of my Teams group chat. Now we are all calculating how many hours we have collectively lost by not knowing this... :P Thanks for the video!
I've been using Excel for years and never saw this cool shortcut until your video. Thank you! I was set to retire this August, but I've decided to put it of for another year, just so I can use your trick at work!
Thank you so much for this helpful tip. As a teacher, I can see where this application will replace CONCAT for extracting Given and Family names but also LEFT and RIGHT which I use to parse student numbers. Excellent tip! Great for setting up Mail Merger to generate test results. After sorting by score and inserting a row between grades, I was able to quickly make grade-based comments, and then delete the blank rows.
I've used this often when doing data cleanup (prior to even basic knowledge of Power Query [much better name than "Get & Transform"]). The main problem I see with it is that it's not sustainable or easily extensible. It's great for quick and dirty (worked great on my 20,000 row sheets of data). But repeated use makes it quite difficult. If you're dealing with large amounts of data requiring lots of manipulation and repetition, Power Query is a much better way to go (in general). Now, if you can use this "formula-by-example" approach, where you just enter examples based on data, and you can get a formula produced, that would be something. Regardless, it's always helpful to have a nice toolbox full of handy tools. You just need to know limitations and functional expertise (what works best in particular situations or types of data). Practice and experiment with COPIES of data (I call this play so it seems more fun).
I knew that if I went to the trouble of entering a formula, I could copy that cell and paste it down the column and it would use relevant new cells to perform the same function but this Control+E is nice - Thanks.
As always in Excel, the simpler the solution is (if there is a feature on the Ribbon or a shortcut key combo), the less flexible or useful it is if the data changes.
At time of writing, Flash fill is really great, until the source data changes and the non-dynamic flash-filled column doesn't update until you Flash Fill again. Only formulas are guaranteed dynamic. Also it only seems to work with contiguous cells and not with large gaps between columns.
And it only works on the right side. Plus, why in hell would one ever use a spreadsheet to generate a form letter? A word processor document that uses the raw data is the better choice.
A spectacular shortcut that I wish I would have learned about years ago! I will share this with my team and look for other videos from you. Great presentation, and in the perfect amount of time to make the point. I've read through many of the comments and learned there are some limitations (like when the data changes, the values don't update which is also helpful to know. Well done!
When I was splitting time that didn't allow me to use Textsplit for some reason.... this would've saved me more time than I am willing to admit.... Thank you
I'm guessing that Ctrl-E is not dynamic so if i add extra rows to my data table or replace the table with new data it won't update automatically? It is a handy tip but need to be aware of any limitations
Hey there, thanks for the feedback. You are correct in that it won't dynamically update. It is similar to pasting as values after a formula is entered. Helpful for quick tasks but won't replace need for formulas in many situations. Have a great holiday weekend =)
I was born in 52...that's 1952...AD...by the way, and never got into this while going through life. However, I've done a lot of traveling to see motor races, and put together a spreadsheet of race venues. It covers states in the US, provinces in Canada, and any other country, which is a total of 215 venues. I used the formulas method, because that's all I knew what to do. Any time I add a venue, it automatically adds to the current total of which country, and the Grand Total. It was fun to set up, and "feels" like I made a program, even though I know that's not what it is. But, that wasn't my first thought when you showed what "Ctrl+E" is for. My first thought was, how the hell do you remember all of these shortcuts?
Thank you for watching and I hope the video helped. This stuff is my daily job so I have built up muscle memory over time. I still remember when a friend showed me VLOOKUP years ago and I couldn't comprehend it. Now Excel is one of my favorite programs and I use it all the time to built reporting templates and make work easier. Thanks for watching and if you run into any questions send me a comment and I'll take a look. All the best,
Holy sht my man. Been using Excel for many years and this has to be the best tip ever. Can you share a vid with the "Top ten Quick tips" for 2022, 2023, etc.? Sub
Very clever..........i only used Excell many years ago to produce a yearly budget accounting for my household needs and that took me many days to get it together.......I'm not computer literate at all and only scratched Word and had a short dabble with Excell, but I have the books on Excell and Word for Dummies etc.
With real world data, it often requires double checking and adjustment. Particularly when selecting a substring from a string. After several manual entries it usually gets the right way to do the task, but if you have multiple rows of data, you still have to check that out for everything right.
I reckon I know my way around excel (formulas, macros, and all other advanced functions) but this was absolute magic to me haha how I never knew this before is simply laughable. Would have made my life so much easier back in the day when cleaning up massive data sets. I do need familiarise myself with all the new excel functionality though. Thanks for this 👍🏻
I've gone through these comments and for the life of me cannot understand why there are so many fools that walk this earth. Great video. Succinct, clear, audio/video was crystal clear, explanations spot on. I have to admit I saw this pop up on my home page and I was reluctant but came out pretty darn amazed. Thank you, great job.
One pretty important missing piece of information i believe is from which office/excel version is this function implemented. In other words back to which version is it compatible. happen to work or have worked at places that still have a very old version of excel. a place used office 97-03 my current place has office 2010 but i manage to uncouple my pc from the network controlled limits and installed the 2021...
Thank you! Although I usually need the output to change when inputs are changed, added or deleted, this is another quick way to do something on the spot.
So now I will have to change my "activate developer options"-Key in my Excel sheet at work to another combination. I think, this can be very handy for me in some situations. Thanks!
Oooo! I'll be using this today! I'm trying to find out, how I can have a pivot table target data from tables on multiple (specific) tabs. Any ideas for that?
Hey there, thank you so much for the comment and for watching. You asked a great question and I plan to make a video on this in the future. However, to answer your question and for timing I found some existing videos on UA-cam that may help you. This video gives a couple of different methods which might be helpful: ua-cam.com/video/Yv7QBZXEDDc/v-deo.html You can also try this video as well: ua-cam.com/video/p_YP5JiE8sE/v-deo.html Let me know if this helps and I look forward to creating more content that can assist. I hope you have an amazing day =)
Hello everyone, this video covers the topic of flash fill (Ctrl + E as a shortcut) in Excel. This will not replace the need for all formulas but it can be a great way to complete simple tasks that follow a pattern. I appreciate all the feedback and comments below and will look to implement your thoughts. Thank you for the support and wishing everyone a great day.
Yes, definitely not a replacement for formulas. It's also kind of dangerous. You assume that the information is a formula and yet when you correct the one of the last or first name fields, the name doesn't get updated. Worse, if you don't know this, you won't notice it.
As is, I consider it a novelty and not something I would want to have to spend time thinking about as to when I was willing to accept it versus putting in a formula. I would be much happier if it gave the option to put in the formula that it "determined" was what you wanted.
It is sort of like writing with a pen rather than with a pencil and eraser. You just know that if you use it, someone is going to want a tweak made.
What is the easy way for combine Birth (in cell A1) and Date (in cell A2) into Birthdate (in cell A2)?
@@BW022 Yup, this is pretty much worse than using formulas. Goes right into my "never use this shit" bin
What about the opposite?
I have been learning Excel for a few weeks now and this is one of the best tips I have seen.
Thank you for watching. Let me know if you have any questions.
+TheMightyNoseGoblin You're so lucky to have found this tip so early in your learning process! A lot (most?) of us have been doing these things without Ctrl + E for _decades._
Been working with Excel for 20 years now. Did not know this… Wish I could like your video more then once
Ditto!
That's a relatively recent feature, so no :)
Flash fill was introduced in Microsoft office, 2013.
I'm shocked! Use professionally excel for years, but this one is one of the best!!! Thanks!
That's good introduction. But also you could have mentioned the technical name for this which is "Flash fill" in excel. This will help people to explore it in more detail.
nice
The fact that it fills the fields with the current values rather than dynamically recalculating them means the process is human-controlled every time. Formulae take a little more time to setup, but will recalculate every time.
@creanero Correct. One DOES NOT actually replace the other, especially considering the distinct functionality...
Exactly. Ctrl+E is for this type of one off situation that comes up very often.
formulae?
@@neverhit17 _Formulae_ is an acceptable plural of formula however _formulas_ is normally used in general conversation like this.
I saw the same thing. Without dynamically updating, I have a hard time finding a use-case for this.
Congratulations! You just broke the minds of my Teams group chat. Now we are all calculating how many hours we have collectively lost by not knowing this... :P Thanks for the video!
I've been using Excel for years and never saw this cool shortcut until your video. Thank you! I was set to retire this August, but I've decided to put it of for another year, just so I can use your trick at work!
Thank you =)
Let me know if there are any other tricks you'd like to see.
🤯 MY MIND HAS JUST BEEN BLOWN BY THIS TIP!
Eureka! I wish I knew this decades ago...
Holy smokes. Never knew this existed and I have been using Excel for 20 years. Awesome video. Thanks. 😊
Thanks so much!
Wow, the Ctrl+E is soooo powerful. Saved me a million years in using functions. Thank you so much for sharing this. 👍👍
You've just Ctrl + E'd my life. Thank you 😊
Wow, concise with no filler. I'm now a subscriber. Thank you
Wow! That was the most productive 2:30 minutes of my whole day! Thank you 🙏🏻 for sharing!!!
Really appreciate the comment, thank you for watching =)
While not as much *fun* as functions 'n' formulas, I have always *loved* CTL+E's magical instant-ness!
Thank you so much for this helpful tip. As a teacher, I can see where this application will replace CONCAT for extracting Given and Family names but also LEFT and RIGHT which I use to parse student numbers. Excellent tip! Great for setting up Mail Merger to generate test results. After sorting by score and inserting a row between grades, I was able to quickly make grade-based comments, and then delete the blank rows.
This is really really good. I used to think I a master of Excel. Never knew of this. Thanks for sharing
I've used this often when doing data cleanup (prior to even basic knowledge of Power Query [much better name than "Get & Transform"]). The main problem I see with it is that it's not sustainable or easily extensible. It's great for quick and dirty (worked great on my 20,000 row sheets of data). But repeated use makes it quite difficult. If you're dealing with large amounts of data requiring lots of manipulation and repetition, Power Query is a much better way to go (in general).
Now, if you can use this "formula-by-example" approach, where you just enter examples based on data, and you can get a formula produced, that would be something. Regardless, it's always helpful to have a nice toolbox full of handy tools. You just need to know limitations and functional expertise (what works best in particular situations or types of data). Practice and experiment with COPIES of data (I call this play so it seems more fun).
I know, why the hell would they drop the powerquery name, if you need help online is easier to use powerquery name, is a unique identifier, the irony!
I knew that if I went to the trouble of entering a formula, I could copy that cell and paste it down the column and it would use relevant new cells to perform the same function but this Control+E is nice - Thanks.
This is great feature I'm pleased to know about. I can see me using it in specific situations but it will never replace formulas if it's not dynamic.
UNREAL.. this is just incredible.. more please
This is truly a godsend ! I had never heard of this versatile tip. Thanks all the way from Denmark !
@@suresh1957 Welcome from Denmark =)
As always in Excel, the simpler the solution is (if there is a feature on the Ribbon or a shortcut key combo), the less flexible or useful it is if the data changes.
At time of writing, Flash fill is really great, until the source data changes and the non-dynamic flash-filled column doesn't update until you Flash Fill again. Only formulas are guaranteed dynamic. Also it only seems to work with contiguous cells and not with large gaps between columns.
I was wondering about that, thanks
And it only works on the right side. Plus, why in hell would one ever use a spreadsheet to generate a form letter? A word processor document that uses the raw data is the better choice.
Wow….I don’t know about this before…thanks for sharing this tips 😊
OMG! You have just made my life a bzillion times easier!!!!
Hey wanted to say thank you for the super chats =)
Let me know if you have any content topics you want me to cover and thank you for watching.
I have a hard time remembering formulas. Thank you for this!
Formulae. I am not at all surprised you struggle, you are very probably an "American".
Now that’s extremely helpful.
That is brilliant! i've been using Excel for many years, but never knew about this 😃👍
Appreciate it!
Thank you love the bite sized video and straight to the point with example 😊
Wow. Can’t wait to try this
Thank you!
A spectacular shortcut that I wish I would have learned about years ago! I will share this with my team and look for other videos from you. Great presentation, and in the perfect amount of time to make the point. I've read through many of the comments and learned there are some limitations (like when the data changes, the values don't update which is also helpful to know. Well done!
Thank you for the comment =)
Very crisp and clear. Thanks
Thank you as well =)
GREAT explanation! I'm an excel noob and this was quick and to the point. Thank you!
Thank you!
When I was splitting time that didn't allow me to use Textsplit for some reason.... this would've saved me more time than I am willing to admit.... Thank you
Thank you for commenting
I've been using spreadsheets for thirty years and I think this is magic!
I currently use free version of WPS Office and they call it Flash Fill.
Onenoff solution is helpful, i prefer formula for a repetitive worksheet. Great tip, i learned something new
This tip has changed my life! Thank you
oh wow thank you for sharing this new tricks
You don't have to ask for a subscribe, I find it very helpful.
Note: Flash Fill (Ctrl + E) is only available in Excel 2013 and later.
Wow wow wow. So happy I clicked here!
I'm guessing that Ctrl-E is not dynamic so if i add extra rows to my data table or replace the table with new data it won't update automatically? It is a handy tip but need to be aware of any limitations
Hey there, thanks for the feedback. You are correct in that it won't dynamically update. It is similar to pasting as values after a formula is entered. Helpful for quick tasks but won't replace need for formulas in many situations. Have a great holiday weekend =)
Definitely helpful to people who don't like to memorize many formulas
The last is very good thx a lot 😊
Love it thanks for the tip
pretty useful in small stuffs
but it's not dynamic so any changes and you have to delete and CTRL+E again not feasible in large dataset
Holy crap THIS CHANGES EVERYTHING!!
Just WOW!!! I struggle daily with excel, this will be a game changer- Thank you
It is call “flash fill” can use in many situations.
This has made my life much easier. Great 1!
Brilliant - ctrl + E is my new mantra
Thank you for commenting =)
Thank you 👍
That was awesome! Thank you!
Thank you, really appreciate it =)
This is the first and onbly thing I like about Excel
I feel like you just saved me a lot of time. Thank you
Really appreciate it =)
Soon Excel will do what we think. I've said it often, it's an amazing program that I wish I knew more about.
Really appreciate the comment.
That was great. Thanks
Thank you as well. Have a great day =)
This is a good UA-cam video. Thanks
I love this simple method....Thank You
Thank you!
Thanks. As an educator this will be helpful to my students.
I was born in 52...that's 1952...AD...by the way, and never got into this while going through life. However, I've done a lot of traveling to see motor races, and put together a spreadsheet of race venues. It covers states in the US, provinces in Canada, and any other country, which is a total of 215 venues. I used the formulas method, because that's all I knew what to do. Any time I add a venue, it automatically adds to the current total of which country, and the Grand Total. It was fun to set up, and "feels" like I made a program, even though I know that's not what it is. But, that wasn't my first thought when you showed what "Ctrl+E" is for. My first thought was, how the hell do you remember all of these shortcuts?
Thank you for watching and I hope the video helped. This stuff is my daily job so I have built up muscle memory over time. I still remember when a friend showed me VLOOKUP years ago and I couldn't comprehend it. Now Excel is one of my favorite programs and I use it all the time to built reporting templates and make work easier. Thanks for watching and if you run into any questions send me a comment and I'll take a look. All the best,
I guess you never used UNIX. Talk about shortcuts.
Thank you! I can't tell you how many times I used text-to-columns and then concatenate and thought I was the boss!
OMG, where was this tip 15 years ago? I use data sets where this would be insanely useful. Thanks!!!
Note: Flash Fill (Ctrl + E) is only available in Excel 2013 and later.
Holy sht my man. Been using Excel for many years and this has to be the best tip ever. Can you share a vid with the "Top ten Quick tips" for 2022, 2023, etc.? Sub
Thank you!
That's awesome, I'm sad I can't do this in Libre Calc.
Broo. this is game changer...
Holy smokes, this blew my mind. What a game-changer it's going to be for me. Thank you!
Very clever..........i only used Excell many years ago to produce a yearly budget accounting for my household needs and that took me many days to get it together.......I'm not computer literate at all and only scratched Word and had a short dabble with Excell, but I have the books on Excell and Word for Dummies etc.
the last use of ctrl+E that i was looking for like crazy, 2:20
thank you sir!!!
Absolutely amazing tips! Thanks!
Thank you so much!
That's awesome! Thank you so much for sharing. Does this continue working if you add new entries above/below?
What a time saver! Thank you for sharing this great insight!!!
@@oliverphotography1 Really appreciate the comment =)
Wow that tip is literally gold !!
What genius! Incredibly helpful! Thank you.
Appreciate it =)
What sorcery is this!?? I am subscribed! 🤣😂😅
With real world data, it often requires double checking and adjustment. Particularly when selecting a substring from a string. After several manual entries it usually gets the right way to do the task, but if you have multiple rows of data, you still have to check that out for everything right.
This is cool. I'm sort of a power user and didn't know about this.
I reckon I know my way around excel (formulas, macros, and all other advanced functions) but this was absolute magic to me haha how I never knew this before is simply laughable. Would have made my life so much easier back in the day when cleaning up massive data sets. I do need familiarise myself with all the new excel functionality though. Thanks for this 👍🏻
Thank you so much for the comment, glad this video helped =)
I've gone through these comments and for the life of me cannot understand why there are so many fools that walk this earth.
Great video. Succinct, clear, audio/video was crystal clear, explanations spot on. I have to admit I saw this pop up on my home page and I was reluctant but came out pretty darn amazed. Thank you, great job.
Thank you for watching =)
One pretty important missing piece of information i believe is from which office/excel version is this function implemented. In other words back to which version is it compatible.
happen to work or have worked at places that still have a very old version of excel. a place used office 97-03
my current place has office 2010 but i manage to uncouple my pc from the network controlled limits and installed the 2021...
Is there a version of this for Google Sheets? Currently, CTRL-E bring up a Google Search in Chrome.
Simply a great no fuss tip - thanks
That was cool, hope to try that soon. tHanks for the video
New to your channel from India, found it really worth it to subscribe. You have gain new one subscribed today. 🎉
Thank you and welcome =)
Certainly enjoyed it. very good tips.
Great 👍 thanks,
Love from India
Welcome to the channel, thank you for the support =)
Thank you for explaining and usage of flash fill option
Thank you! Although I usually need the output to change when inputs are changed, added or deleted, this is another quick way to do something on the spot.
Great. I can not wait to try it.
I am from Pakistan. your lecture is too too good.
Welcome from Pakistan =) Thank you for watching.
Thanks for saving me an hour
@@littletv00 thank you so much as well =)
@@CareerSolutionsforToday you are welcome sir
This is a great find. Thank you very much.
So now I will have to change my "activate developer options"-Key in my Excel sheet at work to another combination. I think, this can be very handy for me in some situations. Thanks!
Very useful.... Learned something new... Thank you
Ctr+E excellent 👍 short cut
so control+E does static filling but how about wanting to do with formula? coz dataset is dynamic so formulas are easier.
Excellent tip. Thanks.
Oooo! I'll be using this today!
I'm trying to find out, how I can have a pivot table target data from tables on multiple (specific) tabs. Any ideas for that?
Hey there, thank you so much for the comment and for watching. You asked a great question and I plan to make a video on this in the future. However, to answer your question and for timing I found some existing videos on UA-cam that may help you. This video gives a couple of different methods which might be helpful: ua-cam.com/video/Yv7QBZXEDDc/v-deo.html
You can also try this video as well: ua-cam.com/video/p_YP5JiE8sE/v-deo.html
Let me know if this helps and I look forward to creating more content that can assist. I hope you have an amazing day =)