Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples]

Flash fill in excel

What is Flash fill in excel?

Flash Fill is a data tool in Microsoft Excel that allows us to combine, extract or transform data based on a few examples. We only need to provide a couple of examples of the results we want, excel will guess the pattern and fill in the rest of the data for us.

What is Flash fill in excel?
What is Flash fill in excel?

In the above gif file, we can see that we only provided one row of input, and after using Flash fill it automatically separates values.

How to Enable Flash Fill in Excel?

To enable flash fill in excel go to File then Options. After that select Advanced and check on Enable AutoComplete for cell values. This will enable flash fill

How to Enable Flash Fill in Excel
How to Enable Flash Fill in Excel

Where is flash fill in excel

We can check flash fill in excel by going to Data tab in ribbon and then can see Flash Fill in Data Tool column.

flash fill option in excel
flash fill option in excel

We can also check flash fill alternatively by going Home tab then Fill. In the drop down we can see Flash Fill option.

There is also a keyboard shortcut available for Flash Fill. The process for using the keyboard shortcut is similar to using the command in the ribbon.

  1. Type out the example data you want returned.
  2. Select both the cells you want to fill and the cells with the examples.
  3. Press Ctrl + E on your keyboard.

5 Examples of How to do flash fill in Excel

Example-1: Extract the First or Last Name from a Full Name

If we have a list of full names then we can use Flash Fill to extract part of the name. In this example, we used Flash Fill to get the First name from a list of names that are all in the First Last format.

Extract the First or Last Name from a Full Name
Extract the First or Last Name from a Full Name

Example-2: Concatenate First and Last Names to Create a Full Name

If we are starting out with a list of first and last names, then we can use Flash Fill to combine the data into a single cell to make it a full name.

This example uses Flash Fill to combine the first and last names into a First Last format.

Concatenate First and Last Names to Create a Full Name
Concatenate First and Last Names to Create a Full Name

Example-3: Convert Names to Initials

If we want to use initials of names either it is in separate columns or in a column. In the below example we used First & Last names in two columns.

Convert Names to Initials
Convert Names to Initials

Example-4: Create email ids from first name and last name

We can create email ids in bulk by using first and last name. Flash fill helps in creating such email ids we need to just use first and last name along with email domain name. After that just use CTRL + E .

creating email id with flash fill in excel
creating email id with flash fill in excel

Example-5: Separating Number and Text from an alphanumeric

We usually encounter problems wherein we need to separate text and numbers from an alphanumeric value. Tough doing it by formula or manually is a tedious task. Though using flash fill makes this task very easy. See the example illustrated in the below example.

separating number and text by using flash fill
separating number and text by using flash fill

In the above example, we got data as alphanumeric and we need to separate numbers and text. we will first write the number in the first cell even there is an alphabet in between. We will ignore the alphabet and write all numbers at a place then will use flash fill. It will separate all numeric in the column. We will treat text value in the same way as we did for numbers.

There are multiple uses of flash fill in regular excel uses that save our time and make handling easier. I will encourage you to explore more on this.

That is all for now for Flash fill in excel. Will meet again with some new topic.

You can watch the video also on this topic for more clarity.

You can read more about flash fill from the below articles.

https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7

https://www.excel-easy.com/examples/flash-fill.html

ahttps://www.journalofaccountancy.com/newsletters/extra-credit/microsoft-excel-flash-fill.html

Leave a Comment

Your email address will not be published. Required fields are marked *