Wednesday, December 4, 2019

Flash Fill - A Good Trick to Know.



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.



 Flash Fill looked at the pattern and then duplicated  and then copied it down.  I did not even have to tell Excel what cells to look at. It kept going down until it did not see a pattern anymore.
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.