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 😉
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:
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.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
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.