2 min
Updated: Jul 21, 2020
QuickBytes is a series of articles for simple and easy to implement techniques with exponential benefits
How well do you know Excel? It has been over 15 years when I started using Excel, and I am still learning!
Modern Excel comes with impressive features. Machine Learning is quite a buzz now-a-day, and modern Excel has quite a cool feature matching that. It is known as Flash Fill.
Introduced with Excel 2013, Flash Fill is less known but handy. It reads and understands the pattern of the user’s input and replicates it for the rest of the continuous rows in each data table. It was my first encounter with Machine Learning.
Using Flash Fill, we can do the data extraction without using any formulas. What would have required a sound knowledge of Excel text formulas, Flash Fill does it in style.
Let us understand it with an example.
Using Flash fill, I can concatenate without using formula. All it requires a demonstration by example and then use the keyboard shortcut Control E
I can reverse this as well (equivalent to Text to Column):
Flash Fill works beautifully for a continuous range. Using Flash Fill, we can extract First Name, Last Name, and can add more information. Just show it with one pattern, hit Control E, and rest is easy.
In case if it does not give desired results with one example, then add one more row of examples and then use the keyboard shortcut CTRL E.
You can perform this on extensive data set as well.
Flash Fill can extract part of the text, for example, numbers from a text or vice versa.
Just like the Autofill option in Excel, the data in the range must be continuous. If there are any blanks in between, it doesn’t work as expected. It works best in tables.
It ain’t an excel formula. For the new records in the table, it requires re-run for the new records.
Where will you use Flash Fill in your daily activity?