Join Dennis Taylor for an in-depth discussion in this video Splitting and combining columnar data with Flash Fill, part of Excel 2016 Essential Training.
- If you saw the previous movie on splitting data, you saw how we could deal with data like this and split those names into separate columns using a text to columns feature. But on the sheet called Flash Fill, we're going to be using a relatively new tool available starting in Excel 2013. It's called Flash Fill and many times it's going to be faster and better, although it doesn't ultimately have as much flexibility as Text to Columns. If you slide over the Flash Film button on the Data tab, the description reads, "Automatically fill in values.
Enter a couple of examples you want as output and keep the active cell in the column you want filled in." It doesn't tell us everything, but it sounds kind of interesting. Look at Column A. Two problems; the major one is we cannot sort this list by last name. The minor problem possibly; it's all uppercase. We'd prefer to have just the first letters capitalized. I'm going to insert a new column to the left of Column B; right-click insert. Now, the Flash Fill idea is I'm going to type the data here the way I want it to appear.
So, I'm gonna do two things. I'm gonna be typing the names in reverse order; Santos, Evan and I'm only going to be capitalizing the first letters. Santos, Comma, Space, Evan, Enter. And now I'm gonna start typing the second name with a capital W. And in effect, Excel is saying, "I see what you're doing. I'm gonna repeat this down the column." And you can probably see in gray, the other names are about to appear. I'll simply press enter. We're all set.
We don't have any formulas here or anything. We could get rid of Column A. Now, I'm gonna undo this with ctrl + z and go back and show you another way to do it. Let's imagine, as I press ctrl + y to undo the undo, I've just typed in Evan Santos. The other technique for using Flash Fill is to complete the single entry, then go to that feature, Flash Fill. Click it, and Excel does the job. It even did this on A1 which isn't quite what we want. Now, where Flash Fill isn't so good is that if we had some of the names here with middle initials or maybe some of them were not capitalized, we might run into some oddities there.
By all means, experiment with it. You saw what happened here and you saw how fast it was and we don't have any formulas here or anything like that. It's looking good. And we could've gone in different directions with that too. I wanna try this also over in Column D. There we've got the problem of City, State, Zip in the same column. We did the same sort of thing in the previous movie with that feature Text to Columns. Let's try it now with Flash Fill. Moving to the right here. I'm going to insert 3 or 4 columns to the right of Column D, simply click Column E, right-click, and insert.
Now, eventually, we might want more columns, but let's isolate the cities here. I'm going to type right here Eau Claire and press enter. Now type W, capital W, and right away Excel sees what I'm doing. It sensed not that I typed two words necessarily, but I typed in all the data to the left of the comma. So, the Flash Fill technique recognized what I was doing. When I typed the W, it filled in Waterbury. I simply pressed enter. Now, I could put in additional columns earlier.
Maybe I just wanted to isolate the city. But if I wanna isolate the other two fields, drag across here, right-click, insert, make this a little bit narrow for the moment. I'm going to type WI. This time I'll use Flash Fill. By the way, there's a keystroke shortcut. It's ctrl + e. Let's try that; ctrl + e. It works just perfectly. The heading up above didn't do quite right. We don't worry about that too much. Off to the right, now I want the zipcode; 87137.
I'm pressing ctrl + enter so the active cell doesn't move. I'll simply press ctrl + e, the shortcut for Flash Fill. We've got that too. So, the headings aren't quite right, but the data's in place. Re-adjust this, get our headings. We might have to type these. Well, zipcode's okay right here. It's just State of course. That's fine. And right here, city. And eventually what we would do here is of course we don't need column D. Right-click and delete. And over here to the left, same idea with a choice here.
We could possibly just move this to the right or copy it, either way. And get rid of Column A. We don't need that anymore. So, Flash Fill's a great feature. You do need to experiment with it a little bit with certain kinds of data, but often it's faster and more efficient than Text to Columns. It's just that when you get unusual situations, you might have to revert to the Text to Columns capability. But this is an attention getter. You saw how efficiently it worked in these examples here. Not only for splitting the cities out, but also for the name.
There's another use here too. What I'm gonna do is backtrack a little bit and insert two new columns here. Right-click, insert, and start over going in a different direction. What if the names were separate from the beginning? I'm gonna type the last name here, Santos. Press ctrl + enter; Flash Fill, ctrl + e works just fine. I'll go the right, type Evan. Ctrl + enter keeps the active cell in place as I complete the entry.
Once again ctrl + e, the shortcut for Flash Fill. That's all taken care of too. I don't need Column A anymore. Right-click and delete. But I might want to join these names. So, let's put in a new column again; insert, click here. Now I'm going to type, Evan Santos or if it's important that I need to sort the data, but I wanna take up less space, I'll put in Santos, Evan. Probably the better long range choice because we can sort.
Now, idea there, once again we can activate Flash Fill with ctrl + e. Now, notice here different than the other examples, it's looking to the right instead of to the left. Nevertheless, it's working just fine. We're all set. Adjust this, get rid of these. We're back in shape. So, I think you can sense here, we've got some real power with this feature called Flash Fill. We can make all the column headings bold. Simply click here, Home Tab, Bold or ctrl + b, either way.
And we're in great shape. Adjust all the columns and so on. A vital tool for cleaning up data; Flash Fill.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros