Flash Fill in Excel

What is Flash Fill in Excel?

Flash fills are like the automatic fillers in the cells of the excel table, excel senses the pattern in the data from the previous fills of the excel, when we type some characters in the adjacent cells next to it automatically flashes the suggested data, it is available in the data tools section in the data tab or the keyboard shortcut CTRL + E.

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

By default, excel must have turned on the Flash Fill option in your system. Just in case it has not turned on then turn by following the below steps.

  1. Go to File > Options.


    Flash fill turn on

  2. Then Go to Advanced > CheckBox Automatically Flash Fill.


    Flash Fill turn on 1

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

You can download this Flash Fill Excel Template here – Flash Fill Excel Template

Example #1 – Extract FY From the Invoice Number

I have data on Invoice numbers. I want to extract FY from this list. Applying many complex formulas will be a tough task for the intermediate user. But Flash Fill save me here.

Flash Fill Example 1
  • Step 1: Firstly, we need to tell the excel what we are doing, so type the first FY in the cell B2.
Flash Fill Example 1-1
  • Step 2: Now go to Data > Flash Fill.
Flash Fill Example 1-2

Shortcut key to apply Flash Fills is:

Flash Fill shortcut key
  • Step 3: After typing the first FY into the cell, now excel understands the pattern of the fill. We can Flash Fill in two ways here.

Firstly, drag the first entry until the end and Now click on the AUTOFILL option in excelAUTOFILL Option In ExcelAutofill is a function where excel identifies the series and fills the data automatically for us when we drag down the data. For example, if a cell value 1 and another cell below have value 2, we can select both the cells and drag them down, and it gets automatically filled.read more and select the Flash Fill.

Flash Fill Example 1-3

Now it will insert all the FY numbers from the invoice numbers columns.

Flash Fill Example 1-4

Secondly, after typing the first FY into the cell, press CTRL + E; it will extract FY the invoice numbers columns.

Flash Fill Example 1-5

Example #2 – Extract First Name & Last Name

This is a common task we all have done in the past. Flash Fill can help us here.

Flash Fill Example 2

From the above list, I want to get first name & last name separately.

Firstly I will type the first name in B2 cell and last name in the C2 cell.

Flash Fill Example 2-1

Now I will go to B2 cell and press CTRL + E.

Flash Fill Example 2-2

I will go to C2 cell and press CTRL + E.

Flash Fill Example 2-3
Flash Fill Example 2-4

Example #3 – Format Numbers Using Flash Fill

Flash Fill not only extracts a portion of the cell but also helps us format the cell value. Now take a look at the below example. I have phone numbers, and I want to format like this: 9056-2358-90.

Example 3

Firstly, I will type the format in the first cell.

Example 3-1

By this time, excel already knows the pattern of the data series. Now I will type CTRL + E.

Example 3-2
Example 3-3

Example #4 – Combine Two Values Using Flash Fill

Flash Fill not only separates the first name and last name but also combines as well. Now I have a first name & last name in two different columns; I want the full name in the next column.

Example 4

Type the required patter name in the cell C2.

Example 4-1

Now press the shortcut key to Flash Fill.

Example 4-2
Example 4-3

Limitations

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

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

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

Things to Remember

Flash Fill works based on the pattern of the data. If there is no pattern, excel will show the below error message.

Flash Fill error

Recommended Articles

This has been a guide to Flash Fill in Excel. Here we discuss how to use Flash Fill in Excel 2013 and 2016 using Data Tab, Shortcut key, and AutoFill option along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>