How to Use Flash Fill in Excel [With Steps]

By /
USING FLASH FILL IN EXCEL 1

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.

List of Full Names
List of Full Names

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-
Example Provided for Flash Fill
Example Provided for Flash Fill

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.

First Names and Last Names Separated from Full Names
First Names and Last Names Separated from Full Names

Another example of Flash Fill in use

Let us take a different example of separating numerical and textual data in Excel-

Textual & Numerical Data Example
Textual & Numerical Data Example

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-

Separated Textual & Numerical Data
Separated Textual & Numerical Data

Conclusion

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!