Flash Fill in Excel is a smart tool that can be used for any custom formatting job you want Excel to do for you. It simply requires an example of a pattern to imitate what has been done and applies that pattern to the available data in Excel.
Also read: How to highlight a cell in Excel?
Steps to use flash fill in Excel
Let’s get started with this step-by-step guide to using flash fill in Microsoft Excel. We will take different examples to learn about the flash fill tool and figure out how it can be used to achieve different objectives.
1. Create the dataset
Let’s begin by creating a table with a list of full names and with two empty columns titled First name and Last Name.
While this is quite a simple example, remember you can repeat the same as long as you have a pattern to your data.
2. Define the purpose
The goal is to separate the first names from the last names into two different cells. But it is quite difficult to do it when dealing with a long list of data.
3. Provide the Flash Fill tool with an example
To achieve this, you need to use the Flash Fill tool. Here’s how-
- Flash Fill needs an example to learn from, to format the data the way you want.
- You will have to manually separate the first name and last name of the first person in the list and Flash Fill will learn from this example. Here is an illustration-
4. Put Flash Fill in Excel to use
- Once you have given an example, select any cell below the example cell and press CTRL+E on your keyboard.
- Repeat the same for the other column as well.
- Or go to the Home tab.
- Under Editing group, pull down on Fill and select Flash Fill.
- Repeat the same for the Last Name column.
All first names and last names have now been separated from each other via the flash fill tool.
Another example of Flash Fill in use
Let us take a different example of separating numerical and textual data in Excel-
Note that we haven’t put a space in some entries to check if flash fill will still be able to separate textual data from numerical data.
We now apply flash fill by pressing CTRL+E or by clicking Flash Fill under Fill-
This was all about using the Flash Fill tool in Excel for different kinds of tasks. You can have a go with the Flash Fill tool in your way and find out how it can be useful to you. Drop a comment if you have any doubts regarding the flash fill tool!