Flash Fill

When I train my classroom-based Excel for Comp Professionals and Advanced Excel & Dashboards classes, I ask each delegate at the end of each day, what learning did you find the most valuable today? The answers are always interesting and not always what I expect, but more of that another day :)

One answer I do expect and regularly get is - Flash Fill. This was introduced in Excel 2013 with little fanfare, so many people don’t know about it. Flash Fill is one of the smartest and most intuitive tools in Excel, and is especially valuable in the reward space, where we often need to break columns up, combine columns or change case.

To demonstrate, say in column A you have a whole lot of names, headed Name, including both first name and surname, and the first name is Jane Smith. Insert two columns to the right of this and head them First Name and Surname. In the First Name column, type Jane, then press Ctrl+E or click Data, Flash Fill. In the Surname column type Smith and do the same. Voila! You have separated name and surname in seconds and without using any formulas.

With Flash Fill, Excel looks for a pattern in your data and autofills the column accordingly. You can use it to change case, combine columns, extract names from e-mail addresses etc. It is very clever - start using it today if you are not already doing so!