How to Use Flash Fill With Microsoft Excel 2013
Microsoft Excel 2013 has reinvented the old formula system of previous versions. It used to be that in order to change the information in one column, one had to write a code or formula to teach Excel what was wanted.
With flash fill, Excel is able to extrapolate from one cell to the entire block of cells in a row or column, offering an automatic change.
Excel has surpassed itself with this latest form of time saving software, taking the work out of processing a column’s worth of data.
Watch the free video here, transcripts for the entire video follow:
Learn how to master Microsoft Excel 2013. Get 19 hours of Microsoft Excel 2013 training – click here.
Video transcripts:
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.
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!
