Introduction
Ever heard of Excel Flash Fill?
From my experience and conversations with hundreds of professionals – 95% of them are not aware of this tool!
In my humble opinion, knowing when and how to use this tool may save you so much time, and trust me – Your co-workers and friends will think you are an Excel Guru (Which you obviously are… If you are reading this post )
Flash Fill is used to extract or join data from a cell or multiple cells based on a pattern.
All we have to do to use Flash Fill is to provide Excel with an example – and based on that, Excel automatically completes this action for the rest of the data – Just by looking at the example provided, identifying the pattern, and understanding the logic required for the next rows.
And from my experience, Excel usually guesses correctly what I intended to do!
Examples for Flash Fill tricks
Before we start, please be aware that Flash Fill is available only from Excel 2013.
Now, let’s take a look at a couple of examples that show how crazy cool this feature is!
Example #1 – Extracting names and company details from email addresses
Let’s say we have a list of email addresses that are all in the same format.
Can we quickly extract the First Name, Last Name, and Domain from these addresses? Yes!
All we have to do is show Excel the example for the first row, then we drag the example down and select Flash Fill – Or we can just use the Ctrl + E shortcut, in the cells beneath the example pattern provided.
By using Flash Fill here, you don’t need to use text functions such as LEFT, MID, and RIGHT!
By the way, we can trigger the Flash Fill tool from the Home Tab:
Example #2 – Text to Columns using Flash Fill
Instead of using the Text to Columns tool to convert a certain column into multiple columns by a certain delimiter, we can do it using Flash Fill.
For example – Let’s extract the first and last name using the space separator to separate between the columns. We will use here the CTRL+E shortcut:
Example #3 – Concatenation of cells and text strings
Instead of using text combination functions such as TEXTJOIN or Concat, we can use the Flash Fill tool!
Again, we are using the CTRL+E shortcut to trigger the Flash Fill!
Practice Flash Fill
Click Here to Download Practice Sheet
Don’t forget to share this magic tool with your friends and colleagues!
No comments: