Excel has many powerful & time-saving features. Even by Excel’s standard, Flash Fill is magical. Introduced in 2013, Flash Fill is a rule engine to Excel’s fill logic. Every time you type something in a cell, Excel will try to guess the pattern and offers to fill up the rest of cells for you. That is some serious time saving magic.
Let’s understand what Flash Fill is and few sample use cases.
Flash Fill, a smart rule engine
Flash fill listens to your every key stroke and tries to guess what you are doing. Remember Clippy from Office 97? Think of Flash Fill as Clippy’s less annoying & invisible cousin. Once Flash Fill identifies a pattern in your data entry, it offers a way to type rest of the data for you. If you accept the suggestion, the rest of the cells are automatically filled up.
Flash fill may not be a convenient option for simple patterns (like 1,3,5… or a bunch of dates or month names). But once you go beyond the realm of simple patterns, Flash Fill can be very useful.
Especially, when it comes to cleaning data.
Example 1 – Extracting numbers from text
Let’s say you are looking at some text data and want to extract the number portion.
Now, there is no simple way to do this. Any formula or VBA approach can be tedious.
But see what happens when you unleash Flash Fill on this unruly data.
Example 2 – Extracting first name from list of names
Again, writing a formula can be tricky ( LEFT(name, FIND(" ",name)) should work – more here).
But Flash Fill is faster and simpler. Just type the first few names and let Flash Fill do its magic.
Example 3 – Writing a bunch of formulas
Humor me with a scenario where you have customer names and you must lookup some corresponding data. Obviously you plan to use VLOOKUP for this. But the lookup table has other plans. Instead of customer name, the lookup table has firstname-initial_of_lastname. So for Bill Gates, the lookup table lists the name as Bill-G.
Of course, you can write a complex VLOOKUP. But why bother? Use Flash Fill to do the dirty work for you.
See below illustration to understand how this works.
Once the lookups are written, you can use FIND REPLACE (Ctrl+H) to add = at the front.
Flash Fill tips & tricks:
- Press CTRL+E to trigger flash fill. Excel will look at previously typed data and guesses the rest.
- To ignore Flash Fill suggestion, press ESC.
- By default, Flash Fill will be always listening and offers suggestions whenever it can. If you want to disable this, Use File > Options > Advanced and uncheck “Automatically Flash Fill” option. Click here for a screenshot of this process.
Do you Flash Fill?
Flash Fill is a fun and powerful way to clean data and get what you want. I use it often, when dealing with complex datasets.
What about you? Do you Flash Fill? When do you use it? Please share your tips and use cases in the comments.
If you have never Flash Filled, go ahead and try it today. See the magic yourself and share your story in the comments.
Remember, your comments on this post qualify for $31 amazon gift card giveaway.
More fun & powerful ways to fill data:
If you like Flash Fill, check out below tutorials for more powerful ways to automate data entry & cleanup processes.
- Unleash pattern power fill Excel auto fill
- Quickly fill all blank cells in a table with data
- How to convert text to dates
This post is part of our Awesome August Excel Festival.
42 Responses to “Clean data quickly with Flash Fill”
Honestly never used it, great feature. You can use it to remove initials from full names.
I've been using Excel for decades, writing\converting cells long-hand. Thank you for insight into a truly useful tool.
How Funny.. I was just talking about Flash Fill yesterday. Time to retire =LEFT and =RIGHT and a few other text functions! This is fast and efficient and great to use when the data is not all the same length.
@Patricia
There are still lots of uses for Left and Right
Especially for Backward Compatibility and formulaic solutions
I have appreciated auto-fill for a long time. So I was excited to learn of Flash Fill. When I replicate your first example in Excel I type the first 2 cells and begin the 3rd I see the flash field results (which is totally awesome...) but the cells that started with a number and ended in text - brought the text in with it. Cells that started with text extracted the number perfectly. Is there something I missed? Even with the text behind the number - this is still a cool tool that I will be using.
Nice! I always thought the flash fill was just a fancy take on the fill option from previous versions of excel, rather than such a nifty tool. Cheers!!
Looks good, but I didn't upgrade from Office 10 to 13. Any Office 10 add-in available for this, or do I have to wait till Office 16 ships? Thanks.
This is infact interesting and saves a lot of time for people who are not good with functions or VBA coding. I recently used this to convert phone numbers which was within text to (xxx)-xxx-xxxx format.
Done in seconds ! 🙂
Hi ,
The only disadvantage would be that unlike a formula , which can be copied down to take care of data which might be entered later , this will work only where the data is already available , whether it is a normal range or an Excel table. Is this so ?
Is this available in VBA ?
Flash Fill is better than that, It is often said that Flash Fill in Excel is really useful, except when it's not.
If, for example, you do your Flash Fill thing and then realise there is an error in one of the rows of the data, Flash Fill will not update itself. However, the workaround I use in this situation is to delete the Flash Fill content up to and including the error row. I correct the error and then press Ctrl+E again at the current end of my Flash Fill List. It will update the whole list.
Do something similar with your new data, step your Flash Fill output back a row or two and, with your new data in place, press Ctrl+E agian
I meant to add that Flash Fill in Power Query is, to quote Chandoo, even more awesome! You should try it!
"you can use FIND REPLACE (Ctrl+H) to add = at the front"
Can anyone explain how we can do that with Find&Replace?
Find: VLOOKUP
Replace: =VLOOKUP
🙂
can any one tell me is this flash fill option supports in ms-office 2007,
because i couldnt able to find this enabling flash fill option
Flashfill first appeared in MS Office 2013.
Assuming your data is structured consistently, the following formula should work:
=RIGHT(A1, LEN(A1)-FIND("CHQ.#",A1)-5)
Hope this helps
@Syed
It is built in functionality in 2013 and later versions
It is not available in 2010
SETTLEMENT OF INV.# 2390, MCJV-25669, CHQ.# 17650
SETTLEMENT OF INV.# 10347,10556,10558, CHQ.# 17551 PIE-13516,
SETTLEMENT OF INV.# 717,758, GRL-16098,16104, CHQ.# 17553
can any one please help me how to extract only chq numbers mentioned in these three different cells
suggest me any convenient formula
@Syed
=RIGHT(A1,LEN(A1)-FIND("CHQ.# ",A1)-5)
Copy down
Its Awesome !!
Can we have a VBA script that will mimic the flash fill magic in excel. ( May be such VBA can be used prior to Excel 2013 , to get flash fill effect).
Ah, this is awesome, esp if we have this feature for 2010.
Loving your Aug series and all the daily posts so far, thanks for sharing!
@Candy
It is built in functionality in 2013 and later versions
It is not available in 2010
thank you chandoo! It's a pity that is not available for Excel 2010. Interesting also LEFT(name, FIND(" ",name)) . I usually use Left formula , but for simple extractions
I haven't used this yet. I am going to, now that I know it exists.
Flash fill seems great! I will try it. BUT, don't you think this may make many excel users slightly LESS awesome when they are less challenged to develop creative solutions for problems?
I like Flash Fill in concept; however; most of the time when it starts to suggest, it disappears before I can hit enter to accept it, and then I usually can't get it back. Has anyone else had this happen? It's really annoying.
Thanks!
Gary
I tried using this to separate surnames from a list which contained full names and titles. I also wanted them in Proper case instead of Upper case. It worked quite well, with one weird exception. The first name in the list was MCGEORGE, A.B., Mr., and I typed McGeorge. The next few names did not begin with MC, and Excel autofilled the whole column and I found that while most were exactly correct, I also had some where the first three letters were replaced with McG, eg RICHARDSON, A became McGhardson and INNES, B.C. became McGes. Meanwhile names that began with MC, eg McMILLAN, M became Mcmillan. So, not bad, but it pays to check the results. Can anyone explain why some names that didn't start with MC ended up with McG?
Good Morning,
Flash fill is very useful and time saving when dealing with more data.
It available in Excel 2013 only.
Is it possible to add in this feature in Excel 2010???
Ramesh.S
Thanks for highlighting this great feature! I can definitely use it at work tomorrow and save myself lots of time writing formulae.
Hi,
great feature but not in Excel 2010 🙁
I cannot find flashfill option in my Excel, I am using Microsoft Excel 2010 in the Package of Windows 7 Enterprise, 32 bit OS.
It first appeared in Office 2013.
Flash Fill the e-z way.
First, select the blank cells u want to flash fill down.
Enter data only of the first corresponding cell.
(Data can be number or single character or text or even blanks in between) and press Enter.
Then press Ctrl + E
Wow...that is a useful tool indeed. I regularly have to parse text to get the last name from a column of data. This will be a great time-saver for me!
Flash Fill is AWESOME!!! This will save me tons of time. Thank you Chandoo!!!!
Thank you! My fingers thank you too, so easy to use, I have to separate the first and surnames from some very long employee lists... this does the job in seconds! 🙂
Wow, gave it a try and it's pretty intuitive as well!!! Thank you!!!
One thing I found annoying was that it seems to make the suggestion (usually on the second cell entry) but as soon as you've typed the whole entry - as it would have suggested - and move on to the next cell, the suggestions stop! Thing is, many times you type so quickly that you don't even realise the suggestion came up or had time to see what it was trying to do.
At least now I know of the Ctrl + E shortcut, I'm hopefully going to remember to every now an then just hit that and see if Excel fills in something for me.
Why does this not work for a simple 1,3,5,7,... and does anyone know a quick way to do this. I'm currently forced to go through a rigmorale of Select two cells,Alt,H,F,I,S,T,Alt+T,Entr (a bit difficult to remember!)
I didn't know this existed.
Hi;
I knew flash fill feature, and in this tutorial I learned Example - 3
Thank you.
Great!
Never knew about this.
Gave it a try. Amazing
I love your helpful hints. I am, however, the spelling police 🙂
It's EXTRACTED not EXTRACED.
Thank you Chandoo, keep up the good work!
Wow! This is awesome! I started following Chandoo recently and his videos are super explanatory. Thank you Chandoo.