Wednesday, April 30, 2014

Flash Fill

It isn’t often that I get really excited over a new Excel feature, but this one is The Cat’s Pajamas (to use an old phrase from the 1920s…)!

Flash Fill enables the Excel 2013 user to Reformat lists of information with remarkable ease and speed.  This fabulous new tool was first introduced in Excel 2013, and automatically fills your data when it senses a pattern.  It works with lists of names, numbers, and a variety other data.

For Example, we will first look at how it works with names.  Let’s say you have imported a list of names that are in the format of Last Name, First Name with each name entirely in one cell.  For this example, we will assume that you wish to convert these names into two fields, with the first field being just the First Names and the second field just the Last Names. 

With past versions of Excel, you could do this by using somewhat complex formulas that are a bit intimidating to many users.  With Flash Fill, however, it is amazingly simple.  For our example, all you need to do is type the first name of the first entry in the field adjacent to your list, and go to Fill / Flash Fill on your Home ribbon.  Excel does the rest by automatically filling in the entire field of first names!  You can then extract the last names using the same approach.

Alternatively, you can just start typing the second first name in the new field, and Excel will sense the pattern and suggest filling the entire range (no need to go to Fill / Flash Fill).

One More Example:  Let’s say you have a field of Social Security Numbers that are not formatted with hyphens.  Simply type the first SSN (with the hyphens inserted) in the field next to the list, and use the same technique as we used for the names.  Voila!

 Flash Fill.  It’s Lightning Fast!

No comments: