What Is FLASHFILL In Excel?
FLASHFILL is an automatic filler of data in the cells of an Excel sheet which senses the data pattern and automatically fills in the cells. It is an easy-to-use and time-saving method. FLASHFILL was first introduced in Excel 2013.
For example, a user types some characters in the adjacent cell. In that case, the FLASHFILL function automatically senses the last cell’s pattern and flashes the suggested data in the cell. It is available in the “Data Tools” section in the “Data” tab, shown in the following image. The excel shortcut key to activate excel FLASHFILL is “CTRL+E.”
Table of contents
Key Takeaways
- In Excel, Flash Fill automatically fills in data in the cells when it senses a pattern.
- Flash Fill can be activated in the “Data Tools” group in the “Data” tab. The shortcut key to activate excel FLASHFILL is “CTRL+E.”
- In case your source data changes, Excel will not automatically update your results.
How To Use FLASHFILL In Excel?
Generally, the FLASHFILL option is active in Excel, but in case it is not, you can use the following steps to apply FLASHFILL in Excel:
- Step 1: To start with, go to the File tab -> in More… -> select Options as shown in the below image.
- Step 2: Then, select the Advanced option -> click on the the Automatically FLASHFILL check box.
To understand more about the function, let us look at some examples of FLASHFILL in Excel:
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Example #1 – Join First And Last Name To Create a Full Name
The following image shows persons’ first and last names in two columns, A and B. Now, we want the full name in the third column, C.
Here, the FLASHFILL function will help fill the names automatically by the following pattern of the above cell C2.
In the table, the data is reflected as below:
Column A contains the First Name
Column B contains the Last Name
Column C is for filling with the Full Name using the FLASHFILL Excel function
- Step 1: To fetch the desired result, we must insert at least one name in cell C2 so that the FLASHFILL can understand the pattern.
Excel will display the error popup window if the first cell is empty, as shown in the following image.
Step 2: Enter the first name in cell C2. The FLASHFILL Excel function will understand the pattern. As soon as “M” is written, you may see that the full name appears automatically in cells C3, C4, C5, and C6, as shown in the following image.
Here, the FLASHFILL function is applied:
- To extract the first name from column A
- To extract the last name from column B
- To combine them to form a full name
The FLASHFILL function works automatically in Excel. The user may click on the icon of FLASHFILL from the “Data Tools” section from the “Data” tab in the Excel ribbon or press the Excel FLASHFILL shortcut key CTRL+E.
Step 3: Press the “Enter” key. As a result, all the data will be extracted automatically with the FLASHFILL function.
Example #2 – Extract Numbers From Any Text String
The following image displays the data that contains alphabets and numbers in a certain format. We need to pull numbers in the second column.
Again, the FLASHFILL Excel function will help extract the numbers automatically by following the pattern of the above cell B2.
In the table, the data is reflected as below:
Column A contains Data
Column B contains Extracted Data
Step 1: Enter the first number in cell B2. The FLASHFILL will understand the pattern, and as “3” is typed, you may see the whole number has appeared automatically in cells B3, B4, B5, and B6, as shown in the following image.
Again, the FLASHFILL is applied:
- To extract only the number from column A
- To give the desired result
The FLASHFILL function operates by default in Excel. As a user, you need to click on the icon of FLASHFILL from the “Data Tools” section from the “Data” tab or press the Excel FLASHFILL shortcut key CTRL+E.
Step 2: Now, press the “Enter” key. Consequently, the FLASHFILL function will extract all the data automatically.
Example #3 – Create An Email Address From The List of First And Last Name
The following image shows the people’s last and first names in two columns. The user wants the company’s email id with their full name in the third column.
The FLASHFILL function will automatically aid the email ID by the following pattern of the above cell C2.
In the table, the data is reflected as below:
Column A contains the Last Name
Column B contains the First Name
Column C is for filling the Email ID with the full name utilizing the FLASHFILL Excel function
Step 1: Enter the first email ID in cell C2. The FLASHFILL will understand the pattern. And as “s” is typed, the real email id will be displayed automatically in cells C3, C4, C5, C6, C7, and C8, as shown in the below image.
Here, again, the FLASHFILL Excel function is applied:
- To extract the first letter of the first name from column B and transform it to lower case.
- To extract the first letter of the last name from column A and transform it to lower case.
- To combine these with periods, company name, “@” symbols, and “.com” extensions to create the email address.
The function automatically works in Excel. The user clicks on the icon of FLASHFILL from the “Data Tools” section from the “Data” tab or presses the Excel FLASHFILL shortcut key CTRL+E.
Step 2: Press the “Enter” key. Then, with the help of the FLASHFILL function, all the data will be extracted automatically.
FLASHFILL Shortcut
One can use the FLASHFILL Excel function for text, numbers, mixed data, dates, etc. FLASHFILL is used in MS Excel 365, and it automatically fills the data by sensing the pattern. The shortcut key for FLASHFILL in Excel is “CTRL+E.” The user needs to insert data, select the cell, and press the “CTRL+E” key to extract the data similarly.
FLASHFILL Limitations
- FLASHFILL results are not dynamic. Whenever the values are updated, the user needs to perform FLASHFILL again.
- The function may identify the incorrect pattern and give the wrong result.
- FLASHFILL needs vertical data. It never gives results horizontally.
- FLASHFILL does not understand the complex pattern. As a result, it may either not give the right result or does not show the result.
- FLASHFILL does not run automatically. We need to click the FLASHFILL option or shortcut key CTRL+E.
- If there is any spelling mistake or missing number or data, it may create a wrong result in FLASHFILL.
Frequently Asked Questions (FAQs)
FLASHFILL in Excel is an automatic filler of data. It senses the pattern and automatically fills in the cell’s data. The FLASHFILL Excel function is easy to use and very powerful. It can understand the pattern when the data is changed. It also helps avoid complex formulas, which can unnecessarily manipulate the data.
FLASHFILL in Excel is the smart feature that identifies the pattern cantered on the data and in all cells wherever needed. It makes the work easier and saves time and effort.
Steps to activate the FLASHFILL in Excel:
1. Click on the File tab, select the More option from the navigation pane and select Options.
2. As a result, a new window will open. Select the Advanced option from the navigation pane.
3. Checkbox the Automatically FLASHFILL option and click Ok.
In short: Files > More > Options > Advanced > Automatically FLASHFILL > Ok
For example, the following image shows the first name and last names of the people in two different columns. The user wants to convert their names to their initials in the third column.
The FLASHFILL function will automatically fill the initials of their names by following the pattern of the above cell C2.
In the table, the data is reflected as below:
Column A contains the First Name
Column B contains the Last Name
Column C is for converting names to initials using the FLASHFILL Excel function
Enter the first initial in cell C2. The FLASHFILL will understand the pattern, and as “G” is written, the whole initial will appear automatically in cells C3, C4, and C5, as shown in the following image.
Here, the FLASHFILL is applied:
To extract only the initials from column A and column B
To give the result in column C
The function operates automatically in Excel. The user is just required to click on the icon of FLASHFILL from the Data Tools section from the Data tab or press the Excel FLASHFILL shortcut key CTRL+E.
Press the “Enter” key. Subsequently, the FLASHFILL function will extract all the data automatically.
The FLASHFILL Excel function is available in the “Data Tools” section in the “Data tab,” shown in the following image. The shortcut key of FLASHFILL is “CTRL+E.”
Download Template
This article must be helpful to understand the FLASHFILL in Excel with its examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to FLASHFILL in Excel. Here we learn how to use it with examples, its shortcut, limitations, and a downloadable excel template. You can learn more from the following articles –
Leave a Reply