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

Get FREE Advanced Excel Exercises with Solutions!

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_Split_data_using_Flash_Fill_Dataset.png


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.

Split Uniform Data in Excel Using Flash Fill Feature

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.

Split Uniform Data in Excel Using Flash Fill Feature

  • 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.

Split Uniform Data in Excel Using Flash Fill Feature

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

Split Uniform Data in Excel Using Flash Fill Feature

  • 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.

Split Complex Data in Excel Using Flash Fill Feature

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.

Split Complex Data in Excel Using Flash Fill Feature

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.

Split Complex Data in Excel Using Flash Fill Feature

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.

Keyboard Shortcuts to Use Flash Fill

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.

Keyboard Shortcuts to Use Flash Fill

Step 3: 

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

Keyboard Shortcuts to Use Flash Fill

  • 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.

From Context Menu Using Flash Fill

Step 3: 

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

From Context Menu Using Flash Fill

  • 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.

From Context Menu Using Flash Fill

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.

How to Use Flash Fill in Excel to Split Data

  • 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.

How to Use Flash Fill in Excel to Split Data


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.

How to Use Flash Fill in Excel to Split Data


Turn on Flash Fill Feature

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

How to Use Flash Fill in Excel to Split Data

  • Now Select Options.

How to Use Flash Fill in Excel to Split Data

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

How to Use Flash Fill in Excel to Split Data

  • 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asif Khan Pranto
Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

2 Comments
  1. thanks. very informative.

  2. You are welcome.Stay with us to see more!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo