MS Excel QuickBytes # 3: Works in a flash... Flash Fill
Updated: Aug 7, 2019
How well do you know Excel?
With modern Excel, a lot of new features have been introduced which can save a lot of time and effort. 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 a relatively less known but very useful feature. It reads and understands the pattern of user's input and replicates it for rest of the continuous rows in a given 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, can be achieved through Flash Fill.
Let’s understand it with example.
Using Flash fill, I can concatenate without using formula. All I have to do is to demonstrate it by example and use 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 additional information. Just demonstrate it with one pattern, hit Control E and it will do for rest.
In case if it doesn't give desired results with one example, then add one more row of example and then use the keyboard shortcut CTRL E.
You can perform this on large data set as well.
Where will you use Flash Fill in your daily activity?
Are you more of a video person? Following is just for you :)