Hello again and welcome back to our course on Excel 2013.
It’s very often the case when you’re working with Excel that you have a large amount of
data which needs to be subjected to some fairly routine repetitive processing.
And one of the new features in Excel 2013, which is Flash Fill, can really help with
that.
And I’m going to demonstrate Flash Fill using one or two specific examples.
Now the examples I’m going to use are based on the worksheet that you can see in front
of you.
And in this worksheet, I’ve got some very simple data about some of my customers.
I’ve got their home phone numbers, seven digits, and I’ve got last name and first
name of each of the customers.
And what I want to do is to make a couple of new columns out of this data and I’m
going to get Flash Fill to help me to do it.
Now the home phone numbers that I’m using on the left are obviously not complete numbers.
They’re just the last seven digits of those numbers.
And normally when you see those numbers written out, they will be written out in a format
like this: 395-6492.
And what I’d really like is for each of those phone numbers to have that hyphen put
in the relevant position.
Now if you’ve used Excel before, you’ll know that you could write a formula using
some string functions in Excel to do that for you and to automate this process and that
would be absolutely fine.
It’s the sort of thing we’re going to look at later on.
But Flash Fill offers you an even quicker way of doing it.
Having entered one of those numbers correctly, if I go to the second one and just start typing
the second one, so the second one will be ultimately be 722-9845, watch what happens
if I just type the seven.
Basically Flash Fill or more specifically Excel with the help of the Flash Fill facility
looks at the whole of the Column next to the one I’m working in and it works out what
each of the other should be based on what I’ve done in the first one.
So it uses the first one as a sort of model and it says okay, I can see what you’re
doing there.
Well, if I applied the same principle to the rest of the column this is how all of the
others would look.
Now I can look at all those and I can say actually, that’s absolutely right.
They’ll all be absolutely correct.
And then all I have to do is to press the Enter key and the whole lot is accepted.
Just think how much work that saved me being able to do that.
Now in this case, I’ve only got somewhere between 20 and 30 phone numbers, but if I
had hundreds or thousands that would be a huge time saver.
Now if you experiment with Flash Fill a little, you will find that you can actually achieve
some pretty sophisticated affects with it.
And in the next example, I’m going to actually two columns and combine the content of two
columns.
I have here, for example, last name and first name.
And let’s suppose that I want to put together a complete name, a whole name for each of
these people.
And I’m going to do it in this format.
I’m going to do Hilda Anderson.
Okay, that’s how I want them to look.
So the next one will be Ray and as you can see once again it’s worked out what they
should all be.
So that’s fine, but let’s assume now that I think again and decide that I don’t actually
want that format.
I’d rather have surname, then a comma and a space, and then the first name.
If you change your mind or if you look at the suggestions and you decide that it’s
not setup correctly, the way to effectively cancel what Flash Fill is trying to do is
to press the Escape key.
So if you press Escape, you’ll lose all of its suggestions.
You can then just delete the one you were working on, go back up to the beginning again,
and say okay, what I’m going to do now is I’m really going to start this again with
a different format.
So now I’m going to go for Anderson, Hilda.
Well what about just Anderson, H, just the first initial?
Anderson, H. And then we’ll try and as you can see it’s worked it out.
It’s taking the surname, comma, space, and then the first character of the first name.
So I look through those.
I think actually they all look fine, hit the Enter key, and they’re all accepted.
And that again has saved me a huge amount of typing or putting together a formula to
do the job for me.
So that’s Flash Fill, a really useful new feature in Excel 2013.
I’ll see you in
the next section.