Transcription
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 an 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. 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 gonna 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 so if we go over to step two and we take a look at this second task I have one and two.
Top 10 List
Now here's something much more practical I might need to create a top 10 list so what I'm gonna do is I'm gonna select one and I'm also gonna select two then I'm gonna look for the black plus sign which is the autofill handle and I will click and drag. this will create a top ten 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 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 ten list.
Date Patterns
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 to 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 gonna do the exercises for tasks one and two and show you how using autofill can be very useful when you're working with dates. for task 1 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 as it did with the number one so Microsoft took the feedback and if I just select one date and use the autofill handle it will automatically increment. I don't need to add 1 / 2 / 2018 underneath the first date. let's say I work in the payroll department I need to figure out Friday's for every day in a 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 four 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 gives show me the months for the other course. I'm gonna 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 gonna 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. so 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 gonna go to Quarter five because it knows that we only have four quarters so it starts again at quarter one. that completes the section on autofill let's.
Flash Fill
Now take a look at flash fill now I'm gonna pretend that my boss at 4:55 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 and we have about a hundred of them. I know it's late but we just got this important assignment from our bit from a 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. 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 so if I just press Enter I get the result right there.
Flash Fill Names
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 Williams' 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 Ctrl Z to undo if I hover my mouse over flash fill I will notice that the keyboard shortcut for flash fill is ctrl e so in the same position that I'm currently at all I have to do is press ctrl e to complete the pattern. that's how you can efficiently and very quickly complete patterns in your data using autofill and flash fill.