What is Flash Fill in Excel ? How to use flash fill in Excel ?

In this article, we will discuss the flash fill feature of Microsoft Excel. As the name mentioned, Flash fill executes tedious tasks in a flash, it is one of the amazing features introduced by Microsoft in 2013.

Before introducing flash fill functionality in excel, Microsoft excel uses used complex formulas to do the required tasks easily. But after introducing the flash fill tool, it reduced the complexity and execution time.

For example: before introducing the flash fill property, to split the first name from a full name excel users used a formula,

To extract the first name “Sachin” from “Sachin Tendulkar” we need to use a formula

= left (A2, search (“”, A2,1))

Similarly, users need to write another formula to extract “Tendulkar” from “Sachin Tendulkar”.

Where is Flash Fill Located in Excel?

In Microsoft Excel, the Flash Fill tool is in the path

Data > Data Tools > Flash Fill as shown below.

What is the shortcut key for Flash Fill?

You can easily access this tool using the shortcut key, select the required cell in excel and click the below keys,

Ctrl + E

How to use Flash Fills Usually Flash Fill starts automatically, and you only need to provide a pattern. Here's how: 

1. Insert a new column adjacent to the column with your source data. 

2. In the first cell of a newly added column, type the desired value. 

3. Start typing in the next cell, and if Excel senses a pattern, it will show a preview of data to be auto-filled in the below cells. 

4. Press the Enter key to accept the preview. 

5. Done!

Flash Fill Examples:

Here we can look at how these tasks are achieved by using the flash fill tool or functionality easily.

Example 1: How to Extract First Name from Full Name using Flash Fill in Excel? 

In this example, we created two columns “Full Name” which keeps all the full names, and column “First Name “which keeps all the extracted first names. Column “Full name” is the source column.

Step 1. For this click the first cell of the column “First Name” and type the first name of the first cell of the “Full Name” column, which is “Pedro”.

Step 2. After filling in the first cell and press Enter.After filling in the first cell and press Enter.

Step 3. Click the Flash Fill button on the Data tab or press the Ctrl + E shortcut.

Finally, you can see the output as shown in the figure.

Similar way you can extract the second name from the “Full Name” column values.