Excel 2013 Flash Fill

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

A clever addition to Excel 2013 will help split and join data automatically.

Flash Fill is a new Excel 2013 feature that’s both useful and looks good in the demonstrations you’re bound to see. It’s not on any menu in the preview but works if you know what to do.

In Excel there’s often need to join or separate strings from cells. Flash Fill copies your actions at retyping data from nearby cells and automatically does the same for matching cells.

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%20Fill%20simple%20demo - Excel 2013 Flash Fill

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%20Fill%20menu - Excel 2013 Flash Fill

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

Flash%20Fill%20with%20middle%20name - Excel 2013 Flash Fill

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%20Fill%20with%20middle%20name%20in%20progress - Excel 2013 Flash Fill

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%20Fill%20with%20middle%20name%20completed - Excel 2013 Flash Fill

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.

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.

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.

This is a demo from beta software so it’s not clear how this will work in the final release and we won’t talk about the many peculiarities in Flash Fill until the final release.

Microsoft is demonstrating some clever Flash Fills that extract details from long strings (eg full name) into parts (First and Last names) or even more complex details like parsing product codes. In other words the reverse of our example above.

For the moment, you might want to try out Flash Fill. Now you’ve seen it, setup some columns and give it a try.

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