Excel QuickBytes# 4: Flash Fill

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.


Creating email from text

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.


Trying to get the last two characters from the name

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?

Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING