Welcome back to our course on Excel 2016. In this section I’m going to take a look
at Flash Fill. 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. 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.
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.