How to Use Flash Fill in Excel to Split Data (4 Quick Ways)

In this article, we will learn how to use Flash Fill in Excel to Split Data. There are several ways to Split data into several columns in Excel like using the Formula & Power Query feature but using the Flash Fill feature is really an easy & fast method. MS Excel has introduced this magical feature in its 2013 version. Flash Fill analyzes patterns of data & does the split job.

Let us consider the following dataset of five persons. The Full Name of all five persons is in column B. Now we want to split the full names into First Name, Middle Name and last Name in columns C, D & column E respectively. Now I will show you how to split those names using the Flash Fill feature.

How to Use Flash Fill in Excel to Split Data: 4 Methods

Method 1. Split Uniform Data in Excel Using Flash Fill Feature

In this method, we will learn how to work with Flash Fill to Split Data using the Home tab. Follow the steps below.

Step 1:

• Initially type the first name or word or value only in cell C5 from the Full Name column or column B.

Step 2:

• Now select any of the cells from the First Name Column. Here I have selected cell C6.
• Then follow to the Data tab >> Data Tools >> Flash Fill.

• Click on Flash Fill, It will speculate what you want in the First Name column & automatically fill it up with First names from the Full Name Column.

Step 3: 

• Now Fill up cell D5 with a middle name, word, or value & follow Step 2 again for the Middle Name Column.

• Flash Fill will again Fill up the Middle Name Column with middle names from the Full Name column.

• If we repeat Step 2 again for the Last Name Column, we will get the same result & in the end, we will get the desired dataset like the photo below with very little effort.

Method 2. Split Complex Data in Excel Using Flash Fill Feature

Flash Fill is more accurate when working with a uniform dataset. It may not show accurate results when the dataset is complex or irregular. In that case, we have to input a little data manually and from that Flash Fill will learn the pattern of change & correct the course of action.

Suppose we have a dataset where the Full Name column consists of Three or Four Words that are not uniform. We want to split the dataset into 3 columns but some data have Four words & Flash Fill finds it difficult to deal with those datasets. Now following some steps we will learn to operate with such a complex dataset.

Step 1:

• If we follow method 1 for this dataset Flash Fill won’t understand where to put second names ‘Clair’ & ‘Jamy’ of cells B7 & B9 respectively and miss them out in the split data columns.

Step 2:

• Suppose we want ‘Clair’ & ‘Jamy’ as First Name in column C. Then we will have to manually type any of that in the First Name column & Then press Enter.
• Flash Fill will now understand what we want & change the rest of the data accordingly.

For a more complex dataset, more manual input & iteration will fix the dataset.

Read More: [Solved!] Flash Fill Not Working in Excel

Method 3. Keyboard Shortcuts to Use Flash Fill

Here we will learn a keyboard shortcut to use the Flash Fill feature to make our task easier.

Step 1: 

• At first type the First Name only in cell C5 from the Full Name, Age column like previous methods. In the First Name column I want to Split my dataset from The Full Name, Age column & AutoFill full columns.

Step 2:

• Now, select any cell from the First Name column. Here I have selected cell C6. 
• Then type Flash Fill keyboard shortcut CTRL + E & Flash Fill feature will Automatically Split data in the First Name column & Fill it up.

Step 3: 

• Now click the Context Menu which just appeared beside First Name column & then select Accept suggestions.

• Applying the same Steps for the Last Name & Age columns using the Flash Fill shortcut we will get our data table completed & it will look like the photo below.

Method 4. From Context Menu Using Flash Fill

In this method, we will see how the Context Menu lets us use the Flash Fill feature.

Step 1: 

• Type the Name first from the Name, Age, Weight column in the corresponding row of the Name Column. Here we have typed ‘Charles Wood’.

Step 2: 

• Now when we try to type the second name ‘Roger Waters’ in column the Name Column, Just after typing ‘R’, Excel automatically predicts what we want to do & all other names appear shadily in the Name column.
• Upon pressing Enter all the names will be clearly visible.

Step 3: 

• Now click the Context Menu which just appeared beside the Name column & then select Accept suggestions.

• If we apply the same steps for the Age & Weight columns using Flash Fill shortcut, we will get our dataset completed & it will look like the photo below.

Read More: Flash Fill Not Recognizing Pattern in Excel

Points to Remember While Practicing

Flash Fill Does Not Update Data Automatically

• Let us have a dataset like the photo below where columns the First Name, Middle Name & Last Name columns are filled up using Flash Fill.

• Now if we change the Full Name from ‘Charles P. Wood’ to ‘Anthony D. Gomez’, Flash Fill won’t auto update this information. Information in First Name, Middle Name & Last Name columns will remain just as before. You will have to do it manually.
• So Flash Fill is not recommended where the dataset often changes.

Capitalization

• Flash Fill even recognizes the Letter Case pattern.
•  If we type ‘wood’ with LowerCase letter in Last Name column unlike UpperCase ‘W’ of the Full Name column, Flash Fill understands the difference & Fills up column E with all having LowerCase letter.

Turn on Flash Fill Feature

• By default, the automatic Flash Fill option remains turned off.
• To turn it on Click File tab.

• Now Select Options.

• Then click Advanced option.
• Finally check Automatically Flash Fill.

• Now Flash Fill feature will be activated.

Download Practice Workbook

Conclusion

Reading the adobe article we have already learned how to use Flash Fill in Excel to Split data. So, using the above methods of the Flash Fill feature we can easily Split data into several columns. Though Flash Fill isn’t recommended when working with complex dataset, it is a real quick tool to Split simple dataset. If you have any confusion regarding Flash Fill, please leave a comment. See you next time!

Related Articles

• How to Turn Off Flash Fill in Excel