Flash Fill
If you download data or use a lot of Text functions, Flash Fill is a good trick to know. It is a feature that sometimes allows you to avoid using Text functions and copying the results.
This feature is only available for users of Excel 2013 and higher.
Flash Fill looks at your data entry and tries to duplicate your pattern.
For example, in this screen shot, I clicked in cell B2 and typed the domain name millcreekpublishingllc.com and then clicked on the Data tab and selected the Flash Fill icon on the tool bar.
So, I didn’t have to worry about using text functions such as SEARCH, MID, LEFT or RIGHT. Super easy right? It doesn’t replace text functions, but it may lessen your reliance on them
particularly if you are doing something quick.
Here are a couple of things to be aware of though.
Be careful as it only works if
it detects the pattern on existing text. If I inserted a row between row 3 and row 4 or
entered a new email on row 5 and then tried to copy down, it would just copy
what was above it so in these case I would have 2 domains showing gmail.com.
If you right-click, you
should see Flash Fill on the short cut menu.
Flash Fill will also allow you to create a pattern.
For example, in this screenshot, when I downloaded phone numbers, just the
numbers downloaded. So, I can type in the format that I want, in the top cell, and then use Flash Fill.
Now, it is not perfect.
Notice that Flash filled in the text above the cell I was in as well. So
my heading now has parentheses after the first 3 characters and the hyphen has
been applied as well. Also, in this
case, it assumed that all the cells contain the same number of characters.
If I add in a phone number that does not have an area code and use Flash Fill, Excel
will still follow the pattern as best it can.
Leading zeroes can also create issues so be careful of that as well. This is definitely a feature to try. It could be a big timesaver.
