Excel’s Flash Fill tool is so cool that you’ll be looking for reasons to use it! Introduced in Excel 2013 to rave reviews, Flash Fill uses a form of machine-learning techniques to reformat data automatically based on one or two examples you give it.
No more complex formulas or VBA, with Flash Fill you'll be able to split, reformat and combine text in a flash! Sorry, I couldn't help myself 😉
Watch the Video
Download Workbook
Enter your email address below to download the sample workbook.
Excel Flash Fill Example
Let’s say you have a list of names separated into columns. Some have middle initials, some have incorrect capitalisation. The point is they’re not consistently formatted. Not to worry, I’ll give Flash Fill a couple of examples and leave it to do the rest:
Mind blown? I’m sure you’ll agree it’s amazing. Let’s give it a harder challenge. This list was actually sent in by a member who had spent some time writing complex formulas to handle most of it, but Flash Fill does it in a cinch. Notice some names have spaces between the first and last and others are hyphenated:
I've given it 4 examples in column B to cover all permutations which helps Flash Fill know what I want.
Flash Fill Reformat Numbers or Text
You can also reformat numbers and text by adding a character to the string like this:
And if your data isn’t consistent then all you need to do is complete the examples for each permutation, even if they’re not the first or consecutive items in your list:
Add or Append text to a List
It’s easy to create a list of email addresses from a list of names, even if the first and last names are in different columns:
Split Data
It can also split data into multiple columns:
Convert Text to Dates
Easily convert text to dates, although you need to enter the first date or two, and format the cell as a Date for it to work in this example:
Turn Flash Fill On
Flash Fill should be turned on by default, but you can check in the Excel Options:
File tab > Options > Advanced:
It should automatically start filling your data when it detects a pattern but if it doesn’t you can start it manually via the ribbon: Data tab > Flash Fill (or CTRL+E):
Tips for Using Flash Fill
- To use Flash Fill you need to be in the column adjacent to the column(s) containing your original data.
- Format your headers different to your data to help Excel know that the top row is a header so it won’t use it in determining the pattern. This will also help trigger it automatically as you can see below:
- Give Flash Fill an example of the final result you want for every permutation in your data. This will help it accurately determine the pattern.
- Beware, sometimes it gets the pattern wrong, other times it’ll leave them blank. If it leaves blanks then you can just go and add an example for the remaining items and it’ll finish the job:
- If it makes a mistake you can just correct one of the entries and it will fix the rest:
- Don't forget you need Excel 2013 or later for Flash Fill. It's almost worth upgrading but with Excel 2016 around the corner, I'd wait and get the latest and greatest.
History of Flash Fill
Flash Fill is the result of a serendipitous meeting between a senior researcher at Microsoft and a business woman on a flight. You can read the story here and learn more about how it was developed.
And if you liked Flash Fill please leave a comment below and give it a score out of 10. It's a 10/10 for me.
Excel Forms – Insert, Update and Delete
Jon PInney
This was just what I needed.
Mynda Treacy
Great to hear, Jon!
Raj Arora
Lovely content!
Mynda Treacy
Thanks so much, Raj!
Duncan Williamson
When using Ctrl+E, you do not need to select the whole column. Your cursor can be anywhere in the column when you press Ctrl+E.
A small time saver
Mynda Treacy
Nice tip, thanks for sharing, Duncan 🙂
Raymond Smith
Such a nice article thanks we liked it.
Mynda Treacy
Thanks, Raymond 🙂
Julian
To learn Excel’s advanced features in depth, it’s the best way not miss every blogs put here on the hub.
Jon Acampora
Great article Mynda! Flash Fill is one amazing tool and I really like all the examples you showed. Awesome!
Mynda Treacy
Thanks, Jon!
René
Wow, looks great!! Looking at the examples you give, I would give a 10/10!
Unfortunately, at work we work in office 2007… (at home I am on a Mac). An extra reason to make an effort to get them make an upgrade!
cheers, René
ZUR
9/10
Mynda Treacy
Tough audience. Wonder what it needs to get 10/10…maybe being available in all versions!
Mynda
Bud
Wow what a great feature. Since my daily job is entirely filled (pun intended) with crafting formula to effect the data manipulation being acheived via Flash Fill, I can appreciate the short-cuts and savings of time.
Of course there obviously are blind delimiters like capital letters or the third character in a string, which apparently must be consistent in the data for Flash Fill to work as expected on all data in a range. But CTRL-H or =Proper(A:A) to capitalize the first letter in a name of all lower case, can assist.
Bottom line I think those who may be intimidated by formula for string manipulation will find a ready friend in Flash Fill. I don’t have 2013 on my work computer, but I do at home. Hmm, I’ll not tell the boss less she suggest I take work home.
Mynda Treacy
Hi Bud,
Glad you liked it.
“Of course there obviously are blind delimiters like capital letters or the third character in a string, which apparently must be consistent in the data for Flash Fill to work as expected on all data in a range” ….. which is why you give it a few examples in an attempt to cover all permutations of formatting, or fix it before Flash Fill. Which ever is quicker.
Maybe tell your boss how great Flash Fill is and she will buy Excel 2013 for you! Probably a dream, I know.
Mynda
Jude
Love and shared it! 10/10
Mynda Treacy
Fantastic! Thanks, Jude.
Ted
Ok, I finally figured it out
Do not format the column as a date format. Format it as general
Then it will take 13/10/29 and make it 10/29/2013 and turn the cell into a date format
Then drag down your box and hit Ctrl e or E and it will give you the correct dates.
Then you can go to format and it will be in date format so you can choose the specific date format you want.
If you make the column in a date format first, you will get garbage.
Mynda Treacy
That’s interesting, Ted. Thanks for sharing.
When I experimented with converting text to dates I found that formatting the cells as ‘Date’ before Flash Fill worked. I didn’t try general, though so this might work too.
Mynda
Ted
I downloaded 6,000 lines from a website that entered the dates in general format
I formatted my column as a date, choosing March 14, 2012.
13/10/29 October 29, 2013 my entry 10/29/13
13/09/05 September 5, 2013 my entry 09/05/13 I then did Ctrl e and got zilch
So I deleted the above and ran it with just one example
13/11.26 October 39, 2013
13/12/08 October 89, 2013
This formula worked well with no errors
=DATE(2000+LEFT(F2,2)*1,RIGHT(LEFT(F2,5),2),RIGHT(F2,2))
The flash fill worked ok on the other examples.
I give Flash Fill a 5, since I need it for dates more than anything.
Julie Malinosky
I was excited to try Flash Fill, but discovered that it is not available in Excel 2010 which is the current version I am using.
Mynda Treacy
Yes, unfortunately it was only introduced in Excel 2013. A good reason to upgrade 😉
MF
Even though I haven’t tried Flash Fill, your demonstration persuades me to a score of 10/10.
Cheers,
Mynda Treacy
Yay! Thanks, MF. Glad you like the look of Flash Fill 🙂
pmsocho
I’ve been using this mechanism for a long time but you pointed out one thing I didn’t know:
“If it makes a mistake you can just correct one of the entries and it will fix the rest”.
Great article. Thanks!
Mynda Treacy
Thanks, Pmsocho! Glad I was able to teach you something new.
Mynda