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.
Use case 1: Concatenate
Using Flash fill, I can concatenate without using formula. All it requires a demonstration by example and then use the keyboard shortcut Control E
Use Case 2: Text to Column
I can reverse this as well (equivalent to Text to Column):
Use case 3: Include added details
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.
Use Case 4: Extracting part of a text
Flash Fill can extract part of the text, for example, numbers from a text or vice versa.
Few important things
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?