How to use Flash Fill with Microsoft Excel 2013

If you have a large amount of data which needs to be entered, the new Flash Fill feature in Excel 2013 will help you. Learn how.

Let’s demonstrate using Flash Fill with some examples.

1. You have a long list containing huge amounts of data. For example, let’s says it’s the names of your customers and their seven digit phone numbers shown in the Home Number column below. If you need to make a couple of new columns out of this data, you can use Flash Fill. If you need to change the format of each phone number to 395-6492 instead of 3956492, inserting a hyphen in every phone number by typing is a lot of work. In previous versions of Excel, you could use a formula for that. However, Excel 2013 offers an even quicker way of doing it by using Flash Fill.

Type the right format of the first phone number in a new column (e.g. 395-6492). Go to the next cell of the column and start typing the next phone number. After typing the first three digits (722), you will see this happen:

Excel will fill in the format you need for the rest of the phone numbers. All you need to do is just press enter, and you will get all phone numbers with a hyphen in that column.

That’s how Flash Fill works:

2. Let’s say you have columns named Last name and First name. You need to combine them together in one column like this: instead of Anderson in column C, and Hilda in column D, you need to write the whole name Hilda Anderson in column E. You also need to do this for all of the customers on your list. Manual typing will give you huge amounts of unnecessary work.

Flash Fill, on the other hand, will help you do it in just a couple of seconds.

Now, go to column E and type the name you need – Hilda Anderson. Then, move to the next cell. Start typing the next name – Ray Brennan. After typing the letter R, the full name Ray Brennan will come up, along with a list of similar name possibilities that offers suggestions which you might be looking for:

If you decide, however, not to use Flash Fill, just press Escape. The list of offered surnames will disappear, and the editing will be backstage after typing the first letter R of the second name (Ray Brennan). You can delete it, and just the first name, Hilda Anderson, will be shown in the first cell of the column.

You can change the name format any way you like, and use the Flash Fill to input those names.

So, as you can see, Flash fill is a useful new feature of Excel 2013 that allows you to fill huge volumes of data in rows and columns without having to type.

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!