Excel 2013 - Flash Fill

One of the best features of Excel 2013 is Flash Fill.  Imagine manually typing data, hour after hour, day after day, but yet not wanting to write a macro or come up with a complicated formula that may or may not ever be used again.  With the addition of Flash Fill in Excel 2013, your days can now be spent outside of manual data entry repetition.

If you are previous user of Excel, whether you are a power user or a beginner, you may have heard at some point or another, “Excel is for Number Crunching”, “Excel is only as smart as the [formulas; code; data] you put into it”, and so on.  Well, I have to say the Microsoft just stepped up the bar with this one.  The Flash Fill function itself is hard to describe, other than referring to it as ‘The Magic Button’, so I will have to try to break it down a bit further.

Imagine that Excel had its own processing center and could anticipate your moves in order to help you fill out your repetitive tasks.  Splitting data into columns and formatting misshapen input has always been a bother, but Flash Fill is able to pick up on your ‘hints’ and ‘examples’ and once you can show a 'method' to your pattern, it will finish your work for you!

Let’s jump into a very basic example:  In my worksheet, I have a column of names that reside in the A1:A10 range that were imported as Full Names (i.e. First Name Last Name).
I would like to process this information into separate columns as Last Name | First Name, respectively; so adding in a new row above my data, I am going to list out my headers.  Once this is done, I will start to fill out the first row of data as I expect it to look.
Now that I have my layout defined, I will highlight the first cell I added data into, and the use the Flash Fill button, which can be found under the Data Tools section of the Data Tab.  As soon as I select the Flash Fill option, Excel automatically anticipates my layout and populates cells B2:B11.  Now that the Flash Fill is done to my liking, I can use the available range menu to “Accept Suggestions”.  Moving on to the next Column, I will again select my cell that I added data to, and select Flash Fill.  Again, once the output is to your liking, select the “Accept Suggestions” option.
Now, instead of having to manually enter each person’s Last Name and First Name in the appropriate columns, we provided one example and used the Flash Fill option twice to populate the rest of fields based on our needs.
The Flash Fill option does not work explicitly with formulas, but does however work in conjunction with a formula.  Now that I have all my data displayed, I would like to add a new column where I will create the Customer ID.  The Customer ID is going to be a combination of Last Initial, First Initial and the amount of characters in their full name.  So, first we need to setup a formula to grab the amount of characters in their Full Name, using a LEN formula.  In cell D2 we will put ‘=LEN(A2)’ and copy down to D11.  Now that we have all of the requirements for our ID, we can add a new column and start to populate the output we are looking for.  In cell E2, we use the Last Initial (G), First Initial (I) and the values of the LEN formula in D2 (12) to get our Customer ID of ‘GI12’.  
Using the Flash Fill button seems to have misinterpreted our layout.  Not to worry, we just need to ‘prove’ the pattern we are looking for.  Moving to cell E3, we would type in ‘HT12’.
And it looks like Excel is starting to catch on to our pattern now, but hasn’t quite got the number figured out.  Moving to cell E4, we add in the value of ‘WH14’.  Now, Excel has caught on and has changed our values accordingly.
Once we have our data the way we want it, use the “Accept Suggestions” button.
Now, using a little more complicated collection of data, we can run a test to see how long it takes to populate some columns using the Flash Fill feature and using the below data (data is in range A1:A85):
From Start (import) to Finish (final output), supplying one row of data and using the Flash Fill option four times (once for each column) took a grand total of 2 Minutes!

NOTE:  Flash Fill may be available for tasks that you were not aware of. If Excel picks up on a pattern while you are working, Flash Fill will anticipate your entries and pre-populate your cell(s) with data as you type.  To complete the list during one of these ‘anticipations’, press Enter, or [Esc] to remove it and continue working on your own.