Excel's Autofill and Flash Fill for Quick Data Patterns

Using autofill. In Excel, you'll use autofill and also flash fill to complete patterns quickly. Pattern data can be dragged or copied into other cells using the autofill handle.

What is the autofill handle? The autofill handle is the black cross that appears in the lower right-hand corner of a selected cell or range of cells. I'll start with this first exercise where I have a column that only includes a single number in the first cell underneath the header. I need to duplicate that value across the cells.

This would not be the efficient way to do this. If I press ENTER and then press the number one and then press ENTER and press number one and press ENTER and press number one, that would take quite a bit of time for me to fill that information in. So what I'll do instead is I'll move my mouse over to the bottom right-hand corner and look for a little green square.

As soon as I see that black plus sign, I will then click and drag and that will duplicate the values down the column. This will save me time. I will not have to worry about my typing speed when it comes to filling that information in.

Now I'm going to head over to the right because this particular exercise might not be as practical. There won't be too many situations where you'll have to repeat the number one in a column several times. If we go over to step two and we take a look at this second task, I have one and two.

Now here's something much more practical. I might need to create a top 10 list. So what I'm going to do is I'm going to select one and I'm also going to select two.

Then I'm going to look for the black plus sign, which is the autofill handle, and I will click and drag. This will create a top 10 list. The way I was able to do this is by selecting two cells.

At this moment, Excel is examining the difference between the first cell and the second cell, and the difference is one number are incremented above the first cell. When I go to the black plus sign, Excel is figuring out that the pattern that's going to be replicated here is that the next number should be one number greater than the last one, and it replicates that pattern, and that's how I get my top 10 list. I could also do this with a different type of pattern.

Here the numbers are incrementing by five, so I'll select the first two values, go to the black plus sign, and then click and drag. This will similarly increment the appropriate number of values for that column. Now, usually in class, I would do this exercise and I would give the students an opportunity to do this themselves.

I'm going to do the exercises for task one and two and show you how using autofill can be very useful when you're working with dates. For task one, I have an individual date and Microsoft must have received feedback from their customers letting them know that, you know, in situations where I work with dates, I don't necessarily want the number to duplicate like it did with the number one. So Microsoft took the feedback and if I just select one date and use autofill handle, it will automatically increment.

I don't need to add one slash two slash 2018 underneath the first date. Let's say I work in the payroll department. I need to figure out the Fridays for every day in the year.

Well, if I enter the first two Friday dates, I can then select them, look for the black plus sign, and I'll have all the Fridays throughout the year. I just have to bring that pattern down. Now I need to create a calendar, so I need to get all the months in this column.

What I usually say about this exercise is if you know how to spell January but you're a little iffy on February, you can let Excel do the work for you. Excel has got your back. You'll never make a spelling mistake.

I'm looking to create a series of quarterly months. I'll start with the quarter month for one and two. After I select them both, I'll go to the autofill handle.

Excel will pick up where it left off with the first two quarters and give show me the months for the other quarters. I'm going to scroll down a little bit and let's take a look at task two. Also, I'm working with a calendar so I would like the days of the week.

I'm simply going to select Sunday, click on the plus sign at the bottom right-hand corner, drag it across, and now I have all the days of the week. You can drag horizontally as well as vertically. Now I want to be able to come up with all the quarters, so I don't have to type quarter two, three, and four.

Just by selecting quarter one and moving over to the right, Excel gives me the other quarters I need for my table of information. Now if I wanted to add even more quarters for another year, if I go over to the black plus sign and move it over one more cell, well guess what? Excel is not going to go to quarter five because it knows that we only have four quarters, so it starts again at quarter one. So that completes the section on autofill.

Let's now take a look at flash fill. Now I'm going to pretend that my boss at 455 says to me, I'm sorry to do this to you Garfield, but we just got a bunch of codes that we need to rewrite and what we need to do is type the last three numbers for all of the codes. We have about a hundred of them.

I know it's late, but we just got this important assignment from our big client and I'd like you to work on this. So I figured let me get started because if I start complaining I'll just be here even longer. So I'll start by typing in the last three numbers of the first code.

One, two, three. I'll press ENTER and then I'll start typing the last three numbers of the second code. Two, and then all of a sudden I notice that something is happening within my range.

Excel is automatically making predictions about what should happen next. It notices that I typed the last three numbers for the first code and if I start to type in the last three numbers for the second code it can sort of figure out what's happening here. Now what I need to do is press ENTER.

When I see that phantom text I should not keep typing or else I'm telling Excel that I don't need its help and I will not have that pattern complete. If I just press ENTER I get the result right there. So this is very useful if you want to be able to let's say combine first names and last names.

You will not have to type the entire list. At the most you'll type the first name then start typing the first letter of the second name and press ENTER and you should have your entire list. I'll do this here.

I'll type in Roger Williams and then all I'll do is start to type Sam and Excel has figured out the pattern. Now I'll let you know that this is not something that just occurs from typing. This is also a command that's available on the ribbon.

We'll go to this next exercise and we're going to increase the level of difficulty. What you're going to be asked to do is enter the last name first and the first name last and oh by the way you're going to put a comma between the last name and the first name. So I'll do that here.

Roger Williams will simply be W-I-L-L-I-A-M-S comma and then Roger. Now even though there's not a comma in any of the two columns Excel can pick up on this pattern. Now I'm not going to type.

I'm instead going to go to the data tab and over in the data tab over in the data tools group I'll click flash fill. With one click of flash fill the pattern gets completed. Now what you'll also happen to notice I'll press control Z to undo.

If I hover my mouse over flash fill I will notice that the keyboard shortcut for flash fill is control E. So in the same position that I'm currently at all I have to do is press control E to complete the pattern. So that's how you can efficiently and very quickly complete patterns in your data using autofill and flash fill.