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 the turn by following the below steps.
- Step 1: Go to File > Options.
- Step 2: Then Go to Advanced > CheckBox Automatically Flash Fill.
How to Use Flash Fill in Excel? (with Examples)
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.
- Step 1: Firstly we need to tell the excel what we are doing so type the first FY in the cell B2.
- Step 2: Now go to Data > Flash Fill.
Shortcut key to apply Flash Fills is:
- 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 excel and select the Flash Fill.
Now it will insert all the FY numbers from the invoice numbers columns.
Secondly, after typing the first FY into the cell, press CTRL + E it will extract FY the invoice numbers columns.
Example #2 – Extract First Name & Last Name
This is the common task we all have done in the past. Flash Fill can help us here.
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.
Now I will go to B2 cell and press CTRL + E.
I will go to C2 cell and press CTRL + E.
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.
Firstly, I will type the format in the first cell.
By this time excel already knows the pattern of the data series Now I will type CTRL + E.
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 first name & last name in two different columns, I want the full name in the next column.
Type the required patter name in the cell C2.
Now press the shortcut key to Flash Fill.
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 look at the below example.
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 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.
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 downloadable excel template. You may learn more about excel from the following articles –