Bottom Line: Learn how to use the Flash Fill feature of Excel to clean up and extract data.
Skill Level: Beginner
Watch the Tutorial
Download the Excel Files
You can practice or follow along using the BEGIN file that I use in the video. I've also included the FINAL file that has the finished result.
Different Ways to Split Text
This is our fourth post exploring different ways to split columns. The example we're looking at is taking a Full Name column and breaking it into First and Last name columns.
So far, we've learned how to split names with Power Query, with the Text to Columns feature, and using formulas. These prompted some really great questions and suggestions. I'm particularly grateful to Wayne, Paul, and HG for their recommendation to use Flash Fill, which we're going to look at today.
If you've never used Flash Fill before, it's super easy.
Using Flash Fill
Flash Fill is an intuitive feature in Excel that's pretty cool. Essentially, Excel guesses what you are trying to do based on one or more entries that you give it as samples.
Let me show you a few examples of what I mean.
Starting with a column of full names, I simply type (or copy and paste) the first name for the first entry in the column.
Then, with any cell in the column selected, you can use Flash Fill to populate the rest of the cells. Flash Fill is an option on the Data tab. Or instead you can use the keyboard shortcut Ctrl + E.
Excel will fill the rest of the column with values that it thinks you want. In this case, it correctly pulls all of the first names out of the full name column. The same can be done with the last names as well.
Automatic Flash Fill As You Type
One feature I forgot to mention in the video is Flash Fill's ability to automatically fill the column with values. This can happen when you start typing the value to return in the second cell in the column.
After you start typing in the second cell, you might see a preview of the data below. You can simply hit Enter to accept all the changes and fill the column with values.
This automatic fill option for Flash Fill can be turned on/off by going to File > Options > Advanced > Editing Options > Automatically Flash Fill checkbox.
Flash Fill can extract information based on other characters or delimiters as well. For example, If I have a list of email addresses and I simply want to pull out the domain portion of the address so I can see what email providers are most popular among my clients, I can use Flash Fill in the same way. If I type one example of just the domain portion of the email address, Flash Fill pulls everything after the @ symbol in the subsequent cells.
Something More Complex
Flash Fill is most accurate when everything in your column is relatively uniform, as in the examples above. As things get a little more complex, Flash Fill has more trouble figuring out exactly what you're looking for.
Here's an example of a list that has middle initials, hyphenated last names, multiple word last names, middle names, and titles. When I try to Flash Fill the last name, you can see that there are some incorrect results.
If you correct one or two of the entries and perform the Flash Fill operation again, Excel will attempt to “learn” from your changes and will potentially return different results. The above list has too many complexities for Flash Fill to completely get it right, but if you have a list that is a little less varied, a few iterations of Flash Fill might provide you with an accurate list—or at least be good enough that you only need to do some minor clean-up.
So if your list isn't perfect on the first try, give it a few more examples and then run Flash Fill again.
Flash Fill Pros and Cons
The biggest advantage of Flash Fill is obviously its ease of use. Type a few entries (or copy and paste them) and then click a button, and you could potentially have an entire column of accurate data immediately. Excel does all the work for you.
A major disadvantage, aside from Flash Fill not always being accurate for complex lists, is that it doesn't automatically update with changes. With the formula solution, changes to your split columns automatically occur when you adjust the source column. With Power Query, a simple refresh is required when changes are made. For this Flash Fill solution, we would need to repeat the original process when updates are made to the source list.
Flash Fill would also not be able to extract middle names from a list of full names, like Power Query can, unless all of the entries in the source column include a middle name.
Ways to Split Text
Here are the links to the other posts on ways to split text:
- How to Split Text in Cells Using Formulas
- Split Cells with Text to Columns in Excel
- How to Split Cells and Text in Excel with Power Query
- Split by Delimiter into Rows (and Columns) with Power Query
So, while Flash Fill is limited in being able to figure out more complex data sets, it remains a phenomenal resource to use for simpler and more uniform entries. It's super quick and intuitive and it's a great tool to have in your Excel toolbelt.
If you have any questions or suggestions, I'd love to hear them. You can leave them in the comments below.
I’ve used Flash Fill to combine names from First/Middle/Last columns (the opposite of your example) by sorting the names into those WITH a middle name and those WITHOUT and running Flash Fill over each collection. I do recognise Flash Fill’s shortcomings but as a long-time user of EXCEL it still seems like magic to me!
Thanks for sharing! That is a great idea to sort the data on the middle names and run it twice. It would still be a lot faster than doing it manually. And I agree it’s a pretty magical Excel feature.
Thanks again and have a nice day! 🙂
I just cleaned 9000 lines of names and used text to columns, which was great, but I sure with I had known this! Thank you for sharing your knowledge.
Thanks, Debra! Yes, Flash Fill can be faster than Text to Columns. I’m happy to hear you now know both techniques. I think they each have their use cases.
Thanks again and have a nice day! 🙂
Thanks Jon for making these training available for free and the diligent and easy, faultless explanations. For sure i will see you soo on one of your subscribed and paid for courses….for sure!!!!….after all a man got to earn some keep 🙂 lol… eternally grateful you helped me lots on a massive project i have been contracted on recently, first time learning how to use these feature saving me days and weeks…..Lot more to learn definitely….Thank you.
Thanks for highlighting Flash Fill – there are always so many workarounds in Excel using arcane formulas and I’ve been going the long way to extra inner words, dates, or numbers from text strings. I was using “left”, “middle” and “right”. Wow was I wrong. I do not see any shortcomings with the feature, as Anne Brown-Robins, has mentioned. I really appreciate the clear graphics and downloadable files for practice. All of the downloads worked flawlessly and I will incorporate flash fill in my Excel files. Thanks!
Does this work with regular ranges or only with tables?