PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO USE FLASH FILL IN EXCEL?

INTRODUCTION

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.

BUTTON LOCATION FOR FLASH FILL IN EXCEL
EXCEL:BUTTON LOCATION FOR FLASH FILL

STEPS TO USE FLASH FILL IN EXCEL

EXAMPLE DETAILS

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.

JOHN SMITH

ALEXA JONES

ASHU TOSH

SWATI SHARMA

STEVE WILLIAMS

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.

EXAMPLE FOR FLASH FILL IN EXCEL
EXCEL:FLASH FILL EXAMPLE DATA

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 bothnames@gyankosh.net.
  • 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.
How to use flashfill in Excel
EXCEL:STEPS TO USE FLASH FILL

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.
 
ISSUES WITH FLASH FILL IN EXCEL
EXCEL:FLASH FILL PROBLEMS

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

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: