I’ve been playing around with the Excel 2013 preview, and damn. It’s nice.

Excel newbs and ninjas alike will find Flash Fill – one feature new to Excel 2013 – intuitive, powerful and super convenient. Flash Fill takes the grunt work out of list-making and data manipulation, relying on clever algorithms to recognize patterns and complete your lists for you, formula-free. That’s right, sans formula. And since list-making and data manipulation are two popular reasons many people use Excel to begin with, this will definitely be a hit.

Data Manipulation? What?

When I refer to data manipulation in this post I’m talking about any instance where you use Excel to change or modify the way your data looks. More often than not the data exists in lists too large for manual editing. Here are some examples to illustrate situations where you might use Flash Fill:

• Converting phone numbers from XXXXXXXXXX to (XXX) XXX-XXXX
• Formatting social security numbers to XXX-XX-XXXX
• Removing incorrect or extra spaces from text
• Formatting names in many ways (like Doherty, Ryan J.)
• Converting dates from YYYYMMDD to M/D/YYYY
• Inserting the leading zero back into Postal Codes (like 04101)

Flash Fill in Action

Let’s say you have a list of email addresses in the format [email protected], and you want to quickly create a Name column from that list.

In Excel 2010 and earlier, this simple task would require using either Text to Columns, or some combination of the LEFT, FIND and CONCATENATE functions. It would likely need multiple steps. Or manual data entry, and nobody wants that.

In Excel 2013, I created the Name column and just started typing the first and last name in cell B5, like so: Jerry Peters. When I hit Enter to move down to cell B6 and typed “N,” which is simply the first letter of the next persons first name, Flash Fill did the rest. In the screen shot above, the ghost-like list from cell B7 down shows Flash Fill’s suggestion. You can go the other way, too (create email addresses from Name columns).

Phone Numbers

Start typing your data in the format you prefer. In the phone number example, I typed “(555) 123-4567” into cell B5. When I hit Enter and typed “(” into B6, Excel recognized that format and Flash Fill suggested how to finish the list for me. If I had typed “555.123.4567” into cell B5, Flash Fill would have suggested completing my list in that format instead (XXX.XXX.XXXX). (Note: since we’re using numbers mixed with text, Excel treats it all as text instead of numbers, so you will automatically get suggestions).

Flash Fill Treats Numbers as Text

My note above brings up a good point. Flash Fill already handles text like a pro, but definitely leaves room for improvement when it comes to numbers. Our friends from the Excel team sum it up best:

The algorithm engine we’re using looks at everything as text. So it doesn’t know any special rules that go along with numbers (for example, it’s OK to drop leading zeros when dealing with numbers, but not so much when you’re dealing with Postal Codes or other IDs).

So when it comes to using Flash Fill for dates, zip codes or other numeric-only data, we’ll still need a few extra steps to get what we want (like first formatting the target column as TEXT). Either way, it’s still a step forward.

Some Flash Fill Tips

• To convert lowercase values to UPPERCASE, just start typing in UPPERCASE.
• To remove extra spaces from values like addresses, just start typing the addresses without spaces.
• The Flash Fill algorithm for pattern recognition treats numbers and digits the same as text. When working with numbers only, always format the target column as TEXT.
• To get automatic suggestions:
  • You have to edit right next to related data (i.e. the next column over from your source data with no blank columns between).
  • You have to make two sequential edits, one right after the other.

My experience using Flash Fill so far has been mostly positive, with a few quirks now and then when it doesn’t work quite how I though it would. Come across a pattern that doesn’t work well with Flash Fill? Or think of a unique way to make use of this new feature? Let me know!

Data Cleaning with Flash Fill