How to Use the Flash Fill Feature in Microsoft Excel 2016
During this Microsoft Excel 2016 training tutorial video, we will show you how flash fill works.
Video Transcript
You’ve already seen how to use Fill and Series Fill. In the last version of Excel a feature was introduced called Flash Fill. And this adds a lot of intelligence to Fill and I’m going to demonstrate it first of all with a straightforward example.
Want More? Get Started With a Free Excel 2016 Course! Click Here
I’ve got a list here of what IMDB says at the time of recording this are the top 50 movies of all time according to the votes of their subscribers. And in column A I’ve got the name of the movie. In column B I’ve got the year of release. Now let’s suppose that I wanted to make a column C where I put the name of the movie with the year of release in brackets afterwards. So if for instance I go in here to C2 and type in and then I put a bracket and I put the year of release afterwards. And then I go down to the next cell and start typing the same thing again.
Watch what happens as soon as I start typing. I’m just going to type the T of The Godfather. Now as soon as I type something which is different from the The Shawshank Redemption what Excel does is to look at the columns there, A and B, to the left and it tries to work out if it could make what was typed in C2 out of what’s in A2 and B2 and it says yes I can. I can put the contents of A2, a space, a left bracket, the contents of B2, and a right bracket. And if I applied the same principle to row 3 it would say “The Godfather (1972)”. Once its done that it then basically forecasts or predicts what all of the others are going to look like and puts them in there in effectively in gray, in a sort of provisional well this is what I think you want. And if you look at that and you say, “Yep that’s exactly what I want in fact” all you need to do is to press the Enter key and all of those cells are filled in for you.
Now if at this point it had done the wrong thing and I realized I had set this up perhaps in the wrong way and it wasn’t going to work I could press the Escape key and then it wouldn’t save all of those forward. But let’s press the Enter key and there you are. Everything is done. Now that can be a real time saver, particularly if you have long lists of some things and you want to combine them in that way.
There are limitations on what you can do using Flash Fill but it’s pretty good at joining things together. Now let me just quickly demonstrate to you that it’s pretty good at splitting things apart as well.
Want More? Get Started With a Free Excel 2016 Course! Click Here
So let’s see what Flash Fill can do with extracting the family names from this class register. Family name is column B, the starting name, that’s the family name, comma, first name or given name is column A. So let’s start with John Beale. His family name is Beale. Now let’s try Milly Clayton. And as you can see it’s coped with that very well. It’s got all those family names sorted out okay so I’ll go with that. And what about first name? And once again it’s fine with that as well. So that’s separated out the family names and the first names in my class register.
That’s it. That’s Flash Fill. I’ll see you in the next section.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
