Flash Fill - Split Cells in Excel – Step by Step

Hello friends,

Today I will discuss about Flash Fill in Excel, a wonderful tool introduced in Excel 2013 and 2016 to separate the data in a cell.

In my last article, I had discussed about Text to Columns, generally we use to split cells and spread the data over the next columns. In this method, I hope you have noticed that when applied text to a certain column, the column also split and the original column disappeared. But sometimes we need to keep the original column.

How Flash Fill works?

Flash Fill analyzes the data you are entering and it tries to figure out what result you are trying to achieve. I told you, Microsoft Excel is getting wiser!!!

How to Use Flash Fill in Excel?

Let us explain this tool with an example, suppose as shown in the below image we have few names with their birthdays and we need to separate it by first name, last name and birthdays. You can split these by months or days or by years but for simplicity we will break it by first name, last name and birthdays.

We need the table as under from the second table.

Table - 1

How to Use Flash Fill in Excel

Table - 2

Use Flash Fill to Split Excel Cell

In the first row, I have “Jacob Elordi - June 26, 1997”, (a movie actor,) I desire to get the text to be split by ‘Jacob’ then in the next column ‘Elordi’ then ‘June 26, 1997’.

Let’s use Flash Fill Step by Step :

     1. First of type the header of the table as First Name, Last Name & Date of Birth.

     2. Type the first name in the column named First Name e.g., Jacob

     3. Press Enter, then select the above cell again where you typed Jacob

     4. Go to Data then in the Data Tools group click Flash Fill.

Flash Fill Option in Excel

     5. Now you will see all the column is filled with the first names

     6. Again, type the last name in the next column labeled as Last Name and repeat the steps.

     7. Apply the same procedure to the Date of Birth column.

The final table structure will be as under. Isn’t it amazing tool provided by Microsoft Excel!!!

After Using Flash Fill in Excel

The Shortcut for this tool is Ctrl+E.

Note : Flash Fill is turned off by default, you need to activate it from File, Options then Click on the Advanced and select Automatically Flash Fill.

Flash Fill Option in Excel

I hope you have enjoyed this article regarding Flash Fill.

Thank you for reading…