When we do some repetitive job in EXCEL, most of the times, we fix a formula and then drag it down to fill all the remaining columns. But is it so simple all the time.
Suppose we have first name and last name in one cell and we need to separate them to the different columns. Yes, we can do that by
but we need a delimiter. like a comma or hyphen or anything like that. But suppose we don’t have that too. then what is the solution left with us?
VBA?? Yes VBA is the ultimate resort for all problems.But what if we get something very interesting tool with us which senses our pattern and give us the result within an instant.
So, we have got this tool named FLASH FILL in the Microsoft EXCEL 2013 and later versions.
FLASH FILL SENSES THE PATTERN IN THE FILLED DATA AND PROVIDES THE OPTION TO FILL THE COMPLETE REMAINING COLUMNS IN JUST ONE CLICK.
In this article we’ll see what flashfill can do for us with the help of an example and what are its limitations.
No specific prerequisite is required for this feature. We just need to be vigilant about the data being filled as it is prone to errors if our data is not very clean or consistent.
The FLASH FILL button is located under the DATA TAB in the right side under the DATA TOOLS section. The following picture shows the exact location.
Suppose we have a column having the names of the employees.
First of all we need to separate the first name and last name into two different columns . After that we need to finalize the email ids for these employees in a particular format e.g. firstname+second name @ gyankosh.net
Here is the list of the names.
Let us take the list of just five names for now.
NOTE: The simple examples are taken to build up the concept. It doesn’t matter whether the formula or function is applied on 5 cases or 5000 cases.
The procedure to use FLASH FILL is really simple. Follow the following steps.
KNOWLEDGE BYTES: The keyboard shortcut for FLASH FILL is CTRL+E.
Although there are a few problems, even then flash fill performs those tasks which would need much complex formulas and functions if tried to do otherwise. So it is still very useful but just we need is a careful eye.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE