In this blog, we would learn about a new and amazing functionality of Excel. The functionality about which I am talking about is Flash Fill functionality in Excel. The Flash Fill is only available in Excel 2013, 2016, 2019, and Office 365 versions of Excel and not in any of the previous versions. Many of the professional Excel experts call it ‘Machine Learning’.
Let us learn this new functionality step by step.
- Some Basics About Flash Fill
- Where Will You Find this Tool?
- Simple Example to Understand Flash Fill Tool in Excel
- Using Flash Fill Option on Ribbon
- Combine the Data in Different Cells
- Clean or Trim the Data Using Flash Fill
- Other Amazing Things that You Can Do With Flash Fill
- Enabling and Disabling This Tool
Firstly, we would start with what is this new feature all about.
Some Basics About Flash Fill
As mentioned in the introduction paragraph, this is the ‘Machine Learning’ technology of Excel. The Flash Fill function performs the analysis of the data pattern and then automatically enters all the data in the cells based on this pattern analysis.
The Flash Fill is only available in Excel 2013, 2016, 2019 and Office 365 versions of Excel and not in any of the previous versions.
This new tool makes our tasks so easy and quick that would generally take a long formula and VBA code writing.
Let us now navigate to the location where you can find the Flash Fill tool.
Where Will You Find this Tool?
In the Excel Ribbon, click on the tab ‘Data’. There you can find the ‘Flash Fill’ button under the group ‘Data Tools’.
Refer to the screenshot below:
Or you can also run it by using the Keyboard Shortcut: Ctrl + E
Now, when you know where we can get this tool from, we are good to start learning the functionality of this amazing tool provided by Excel.
Simple Example to Understand Flash Fill Tool in Excel
Before getting into what all complex things does this functionality perform, let us take one simple example to understand its basic usage.
In the below screenshot, you can see that I have full names in column A in the worksheet.
Now, suppose you want to get the last name in some other column in Excel (let us say column B).
There are multiple ways to do so. We can create a formula or we can also use the ‘Text to Column functionality‘.
But the easiest way is to use the Flash Fill functionality.
To start with, select the cell B2 and enter the last name ‘Bhatt’.
Now, go to the next record. Select cell B3 and start typing the last name ‘Kohli’.
As soon as you enter the letter ‘K’, you would notice that Excel automatically guesses the last name for each of the records.
Press the ‘Enter’ key on your keyboard and you would get the last names for all the records.
Using Flash Fill Option on Ribbon
As we learned earlier in this blog, the flash fill option is available on the ribbon under the tab “Data”.
We can also use this option on the ribbon to run this functionality.
Select the cell B2 and enter the last name ‘Bhatt’.
Now, go to the next record. Click on cell B3.
Navigate to the flash fill tool in the ‘Data’ tab and click on the ‘Flash Fill’ button.
As a result, you would notice that the excel automatically analyses the pattern and enters the last name in the cells in column B (besides the full name).
Combine the Data in Different Cells
You can even use the Flash Fill tool to combine the data in different cells in Excel. This works the same way as the =CONCATENATE() formula works.
Refer to the below example data.
Now, we wish to combine the first name and last name in column C.
Just the way it was explained in the above section(s), we can give excel with the pattern and then it would automatically detect the others.
Try to perform the same using the Flash Fill button and it would work as well.
Clean or Trim the Data Using Flash Fill
You can even use the Flash Fill function to trim or clean the data in Excel. As you can see in the below screenshot, there is some text in column A. However, as you can see there are unwanted additional spaces at the beginning of the text.
We can use the flash fill functionality of Excel to clean this data and remove the additional unwanted spaces at the beginning.
Other Amazing Things that You Can Do With Flash Fill
Above are some of the basic examples of this functionality. You can do many such more things that can make complex excel tasks very easy and fast that was earlier was done using a formula.
Below are some of the examples of complex things that we can perform using the Flash Fill functionality of Excel.
Changing the Format of the Cell
Changing Only some part of the Text
There are many more things that you can do with this amazing tool to make your daily excel tasks easy and quick.
Enabling and Disabling This Tool
You can turn the flash fill on or off using the ‘Excel Options’ in Excel.
In the ribbon bar, click on the ‘File’ tab. There, select the ‘Options’ button.
The ‘Excel Options’ dialog box would appear on your screen.
Click on the option ‘Advanced’ and drag the window to ‘Editing Options’. Then, uncheck the ‘Automatically Flash Fill’ checkbox and click on OK. This can be re-enabled from here itself.
This brings us to the end of this blog.