Let’s open up the first sheet in the example workbook to see how flash fill can help you combine data in different cells.
As you can see, this sheet contains a column full of first names, a column of last names, and a column of company names. For our example, we’ll say that each of these people have the same style of email address: email@example.com.
We’ll use flash fill to get Excel to create those email addresses automatically.
First, we’ll manually enter an address. We’ll start with the first employee, Winfred Loakes of Fivespan.
Click into cell D2 and type “firstname.lastname@example.org”:
Now we’ll put Excel’s power to use.
Click the Flash Fill button in the Data tab of the Ribbon.
Once you’ve done this, Excel will do its best to carry on the pattern.
Let’s see how it did in our case:
Very impressive! Excel combined the first and last names with the company names to create email addresses.
You might notice, however, that the email addresses in rows 4 and 10 have spaces in them. The company names both consist of two words, and the spaces between those words were included in the email addresses. Let’s fix that.
Clear column D, and click into cell D4.
We’ve removed the space between the two words, but will Excel be smart enough to do the same for other two-word company names?
And will it still work with single-word names? Let’s find out.
Let’s find out…
Click the Flash Fill button.
Of course it is!
Excel learned from our removal of that space, and applied it to the two-word company name in row 10. And it also managed not to mess up any of the single-word company names.