Flash Fill Magic in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Flash Fill is a simple way to split and join text automatically in Excel for Windows or Mac.

Flash Fill was added in Excel 2013 for Windows. It’s both useful and looks good in the demonstrations.

In Excel there’s often need to join, separate or reformat strings of text. Flash Fill copies your actions at retyping data from nearby cells and automatically does the same for matching cells. It can also learn about exceptions to the rule when a different change is needed.

Excel is ‘learning by example’. It’s converting your typed examples into computer code (regular expressions) that can be applied to the other cells in the same list.

Joining text into a single cell

Here’s a simple example to start with. You have two columns for First and Last name and want a column with the two elements combined.

Flash Fill simple demo image from Excel 2013 Flash Fill at Office-Watch.com

As you type the second name (the ‘Br’ in Bruce) in Column C, Flash Fill works out that you’re trying to join the words in column A and B so it prompts with a greyed list of the rest of column C filled in. Just type Enter to confirm the Flash Fill or keeping typing to ignore it.

There’s a tooltip menu item that gives you explicit choices if you need them.

Flash Fill menu image from Excel 2013 Flash Fill at Office-Watch.com

Flash Fill is smarter than that simple example, check out this list with some middle names.

Flash Fill with middle name image from Excel 2013 Flash Fill at Office-Watch.com

The names are filled as in the first example with the middle names ignored. But if I go back and ‘fix’ the first entry with a middle name ….

Flash Fill editing a value image from Excel 2013 Flash Fill at Office-Watch.com

As soon as I revise the cell by adding the middle name, Flash Fill kicks in and updates the other cells to also add a middle name.

Flash Fill with middle names image from Excel 2013 Flash Fill at Office-Watch.com

There’s other examples of this when the initial Flash Fill doesn’t properly work out that last name prefixes like ‘Al’ and ‘van’ should be included. Edit the results and Flash Fill should pickup the revised pattern and copy it down the list.

Splitting text into separate cells

Flash Fill also works the other way, taking a long text string and splitting into separate cells.

Here’s a list of names. Add a column for the first/given name and start typing examples. Flash Fill should kick in with the second row.

Then repeat the process for the last or surname.

Just like with joining text, you can correct Flash Fill’s initial attempt for exceptions like surnames prefixed with ‘Al’ or ‘van’.

Email addresses

Flash Fill doesn’t just work with names. Any text string will work, like email addresses.

Split email addresses into username and domain like this, starting with the username

Then another column for the domain name.

Or go the other way to convert names into email addresses.

Phone Numbers

Phone numbers can also get the Flash Fill treatment. For example changing unformatted numbers into something more readable.

Or changing the format to another style.

Turning on or off

Normally automatic Flash Fill is on by default, but to check this, go to File | Options | Advanced.

Start Flash Fill Manually

Sometimes, automatic Flash Fill doesn’t work, or you may have it turned off in the options for some reason. You can, however, make it happen manually, and there are three ways to do that.

Again, we need to first type in the data in the first line. Then, select the whole column, and do one of the following:

  • Use the keyboard shortcut Control + E.
  • Click the Flash Fill button under Home | Editing | Flash Fill.
  • Click the Flash Fill button under Data | Data Tools.

(that’s right … the same feature, Flash Fill, has buttons on separate ribbons. The only example we can find of a feature duplicated across Office ribbons).

Repeatable Flash Fill in PowerQuery

Flash Fill isn’t much help with data that changing, it’s a ‘once only’ feature.

If you like the ‘learn by example’ method, try PowerQuery in Excel 365 for Window with it’s Column from Examples option. That’s like Flash Fill but is saved and reapplied each time the data source is refreshed.

Flash Fill gotchas

There are some things to keep in mind with Flash Fill.

Flash Fill is a once-only deal not a dynamic listing. If you change some of the source cells then the results are NOT automatically updated.

Reformatting data that’s changing needs other approaches such as a string manipulation formula or PowerQuery.

If you have multiple columns of similar data (maybe info in different formats), be careful that Flash Fill is copying from the column you expect. It’s usually, but not always, the immediate left column.

It’s intended to help people separate out details from info that’s in some order but not fully ready for Excel to use. For example details pasted into Excel from other sources.

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address