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.
PREREQUISITES TO USE FLASHFILL IN EXCEL
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.
BUTTON LOCATION FOR FLASH FILL
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.
STEPS TO USE FLASH FILL IN EXCEL
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.
STEPS TO USE FLASH FILL IN EXCEL:
- Fill one line manually. For example, in the table quoted by us above, we’ll type the first name in the firstname column, last name in the lastname column and email id as decided firstname.lastname@example.org.
- After filling the first row completely, we’ll check if flashfill can give us the correct options or not.
- Now go to the column we want to flash fill that is, the first name column.
- Click the cell.
- Press CTRL+E or click DATA TAB>DATA TOOLS>FALSHFILL button.
- The complete column will be filled with the values which EXCEL sensed from the above value.
- If the filled data is correct, go to the second column and third and repeat the process.
- If the data is not as per expectation, fill one more row manually to give EXCEL more sample to understand the pattern.
The complete process is shown in the animated picture below.
KNOWLEDGE BYTES: The keyboard shortcut for FLASH FILL is CTRL+E.
PROBLEMS WITH FLASH FILL OPTION IN EXCEL
Although it is a lovely option in EXCEL , it has got some serious issues too which are worth mentioning here so that you might not land up in trouble later on.
- It doesn’t sense or reflect the change in the original data. In our example, if we change the name of any of the five employees, even if there is a slight change, that is not going to reflect on the column which are flash filled. This is kind of serious and we need to apply flash fill again.
- FLASH FILL is not fool proof and reliable as in the case when we apply some function to concatenate or any other operation on the text. It might sense wrongly if there is any special case and it’ll fail at that point. We should always have a rough look over the data which is flash filled.
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.