What is Flash Fill in Excel?

How to Turn On Flash Fill in Excel? (Step by Step)

How to Use Flash Fill in Excel? (with Examples)

Example #1 – Extract FY From the Invoice Number

We have data on invoice numbers, and we want to extract the FY year from this list. Applying many complex formulas will be a tough task for the intermediate user. But, the “Flash Fill” tool can save us here.

  • We must first go to “File.” Then, choose “Options.” Go to the “Advanced” checkbox and select the “Automatically Flash Fill” option.

  • Step 1: Firstly, we need to inform Excel what we are doing, so we must type the first FY year in cell B2.

  • Step 2: Now, we must go to “Data” > “Flash Fill.”

Shortcut key to apply Flash Fills is:

  • Step 3: After typing the first FY into the cell, Excel understands the fill pattern. We can do the “Flash Fill” in two ways.

Firstly, drag the first entry until the end. Now, click on the AUTOFILL option in excelAUTOFILL Option In ExcelAutoFill in excel can fill a range in a specific direction by using the fill handle.read more and select the “Flash Fill”.

Consequently, it will insert all the FY years from the invoice numbers columns.

Secondly, after typing the first FY year into the cell, we must press the “CTRL + E” key. It will extract FY from the invoice numbers columns.

Example #2 – Extract First Name & Last Name

This task is common as we all have done in the past. The “Flash Fill” tool can help us here too.

We want to get the first and last names from the above list.

So, we must first type the first name in the B2 cell and the last name in the C2 cell.

We will now go to the B2 cell and press “CTRL + E.“

Then, we will go to the C2 cell and press “CTRL + E.“

Example #3 – Format Numbers Using Flash Fill

The Flash Fill feature extracts a portion of the cell and helps us format the cell value. Now, let us take a look at the below example. We have phone numbers, and we want to format them like this: 9056-2358-90.

So firstly, we must type the format in the first cell.

Example #4 – Combine Two Values Using Flash Fill

The Flash Fill tool separates the first and last names and combines them. So,  now we have a first name and last name in two different columns. We want the full name in the next column.

To do that, we must type the required pattern name in cell C2.

Now, we must press the shortcut key to “Flash Fill.”

Limitations

The Flash Fill feature has its limitations too. It is very important to know them:

  • The Flash Fill data tool is not dynamic. Remember, it is not a formula to change the resulting cell if any changes.The Flash Fill tool can wrongly insert the data. Take a look at the below example.

In the above image, we wanted to extract the middle name. So, we typed the middle name in the first cell and pressed the “CTRL + E.” But for a few names, there are no middle names, so it has extracted the first name itself.

Things to Remember

The Flash Fill tool works based on the pattern of the data. If there is no pattern, Excel may show the below error message.

This article is a guide to Flash Fill in Excel. We discussed using Flash Fill in Excel 2013 and 2016 using the data tab, shortcut key, AutoFill option, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Excel Fill HandleFilter Shortcuts in ExcelExcel Circular ReferencePower BI Aggregate Functions