HOW TO USE FLASH FILL IN EXCEL?

Table of Contents

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?

We need to use the functions to get this done.

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.



WHAT IS FLASH FILL IN EXCEL?

Flash fill is an AI BASED feature in Excel.

FLASH FILL SIMPLY LEARNS THE PATTERN AND OFFERS YOU THE RESULTS FOR REST OF THE DATA.

For example, If we are separating first 3 letters from the words, After filling one or two cases as the sample, Excel will learn the pattern and suggest the solution. If we agree, we can choose.

We can choose flash fill for the following cases:

  • Separating First name and Last name.
  • Separating any specific number of letters from the word.
  • Joining the words in many columns into one column such as Names of persons.

And any situations where we can find a patter, Excel can detect this and help you with Flash Fill.



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.

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.

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.

EXCEL:STEPS TO USE FLASH FILL
KNOWLEDGE BYTES: The keyboard shortcut for FLASH FILL is CTRL+E.



LIMITATIONS OF 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.
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.