Advanced Excel - Flash Fill Tutorial

In this tutorial, I'm going to show you a great feature that's in the latest versions of Excel, and it's called Flash Fill, and in a lot of cases, this will be a great time saver for you. In order to show you how Flash Fill works, I've created a sample spreadsheet, and it's basically a list of a hundred names. And here, to the right of the names, I have a column for First Name, Last Name, First Initial, and Last Initial. So let's say that I've been given the task of taking this spreadsheet full of people's names, and separating out the first name and the last name, and then also putting in the initials. This could be a huge nightmare, a huge headache, especially if, instead of a hundred names, I had a thousand names to do, or ten thousand, or more. Now, in a previous video, I showed you how to split names, and you should still watch that video if you're interested in learning how to do that. But today, I want to show you how to accomplish this task that I have to do, using the Flash Fill option that's in the more recent versions of Excel. The first thing I need to do is show Excel what kind of information I want. So I'm going to click here, where it says First Name, and there in cell H2, in this case, I'm going to click and type in Stephanie. And then I'll hit Return or Enter on the keyboard. And next, all I have to do is go to the Data tab and look for the Data Tools group. You'll find Flash Fill. So I click on Flash Fill, and look what it did. It figured out, based on the data that I had already typed into column H, it figured out that I wanted just the first names. And it's not based on the column title that I typed here. No, it's actually based on the fact that I typed in Stephanie, the first name, here. Excel figured that out and applied it all the way down the entire column. Now, notice, whenever you do Flash Fill, you'll get a few comments here in the lower left corner. So pay attention to this lower left corner when you use Flash Fill. It says Flash Fill changed cells, and it says 149. So 149 cells were affected by this Flash Fill that I just did. Okay, let's try it now with last names. So here in the last name column, I'm just going to type in Gramling. And this time, notice if I go up to Data, I can't do Flash Fill. The reason why is because this cell that I'm typing in is still active. And so technically, there's really nothing in that cell at all. Until I tap Return or Enter, now there's something there in the cell. There's data in the column. So now I can go up to Data and choose Flash Fill. Now, I don't want you to get the impression that you always have to have selected the cell below the information that you want to mimic. No, you don't. I could be down here. I could be back on that data. But you do have to make sure that the data that you're going to be mimicking has actually been entered into the cell, and it's not just in progress of being entered into the cell. So to prove that, I'm just going to go up here to Gramling. I'll click on it to select it. I'll click Flash Fill, and look, it worked perfectly. All the way down, I can check, let's say, number 89, Delmer Quail. All right, it worked perfectly. I'm going to go back up now. Let's try it just with first initial, so S. And this time, I'm going to just click here a few cells down, just to prove to you that you can select any cell in the column that you want to Flash Fill to. Also, this time, instead of clicking on Flash Fill, I want to use the keyboard shortcut. It's Control-E. So you just hold Control and tap E, and it does the same thing as if you had clicked the Flash Fill button. So take a look at that. It worked perfectly. I have the first initial of the first name. Now let's try last initial, and I'm going to base it off of a different person. Instead of basing it off of Stephanie Gramling, I'd like to base it off of Alberta Kenny. So I'm going to click here. I'll put in her last initial. I'll make sure that that data is entered just by hitting Enter or Return, or you could even just click away from it like I did. But I am in the column that I want to Flash Fill into. And then I'll hold Control, tap E, and let's check to see if it worked. It did. So even though there are other ways to do some of this, for example, using text to columns, I really love Flash Fill, and I tend to use it quite a bit. But one word of caution. When you use Flash Fill, you should use it pretty much only when the data that you're going to be basing it off of is pretty stable. It's going to stay pretty much the same. And the reason I say that is this. Watch. If I change Lucille Sugarman's name, let's say she changes her last name to Thompson. I tap Return. Notice that it did not update the information that was Flash Filled. And even if I select a part of the spreadsheet and let's say I clear it out, clear contents, let's see what happens if I try Flash Fill again. I got some interesting results there. You see that? And so it really works best, Flash Fill does. If you're basing the Flash Fill on data that is very stable, it's just going to stay the same and not change. Okay. I want to show one last example. Let's say instead of initials or first name, last name, what if I just want to change the names so that they start with the last name and then go comma and then first name? That should work as well. So I'm going to try that out. I'm just going to go to this cell here, type in Grambling, comma, Stephanie. I'll tap Enter or Return and then I'll hold Control, tap E, and it followed the pattern. Now I want you to know that even though my examples all have to do with names, Flash Fill works with lots of other things as well. All it's doing is it's looking for a pattern. In this case, it saw that there were two words and then it saw that I only typed the first word here. So then when I Flash Filled, it just saw that pattern and it copied it. Now if it doesn't follow the pattern perfectly, you may need to type two examples into the column and then try Flash Fill, and then it will be much more likely to see the pattern. But anyway, my point is that this works with much more than just names. If there's any pattern at all, it will try to see that pattern, understand it, and then mimic it. So for example, let's say you have a list of phone numbers and you want to put in the dashes for the phone numbers. You can do that. Just go ahead and type it the way you want it to look and then tap Enter or Return. Now before I actually do the Flash Fill, I need to have other phone numbers listed. So give me a minute to add a few phone numbers and then I'll show you the results. Okay, great. So now if I do Flash Fill, look what it does. It put in the dashes in this case, and now that I've done that, I don't really need this column N anymore. So I could right-click on it, Delete, and there's my data formatted the way I want it to be. So that should get you started using Flash Fill in your Excel spreadsheets. I hope that you found this video to be helpful. If you have, please click the Like button below and please consider connecting with me on my social media websites like Facebook, Pinterest, and Twitter. And definitely do subscribe to my YouTube channel for more videos about technology for teachers and students. And watch for another video from me at least every Monday.