Hi, this is Wayne again with a topic “Excel’s AutoFill Handle in Depth”.
In this tutorial, I’m gon na focus on the autofill handle in Excel, sometimes also just called the fill handle – and I touched on this in my intermediate – Excel skills tutorial, but I wanted to in this tutorial to focus exclusively on the autofill handle and what is the Autofill handle basically, it is just this innocent-looking green square in the lower right corner of a cell that you’ve clicked on. So, if I click on this cell there it is looking innocent and not particularly powerful or useful there. It is again there. It is again so that little green square. What is its purpose generally? It helps you to copy the contents of a cell, for example. If I click here in I 1 and I type a name and tap enter on the keyboard, that name is now in cell. I 1, and I can then click on I 1 and go to the autofill, handle and click and hold the click and then drag down, and what does it do it copies? The contents of I one copy sit down as far as I clicked and dragged now. It works that way, particularly with text. There are some exceptions, but if you want text to be copied, click on the cell, that has the text, go to the autofill, handle click and drag, and it copies it down now with numbers.
Sometimes it’s a little different. So let’s try some numbers here in J 1, I’m going to type in number 1 and then click away and then back to the 1 and then I’ll click on the autofill handle and drag down. And you can see it’s still copied. The number 1 copied it down, so it worked the same way with numbers. However, the autofill handle can also be used to extend a pattern, so I can establish a pattern: 1, 2, 3 and then I can click and drag to show Excel that pattern to identify it by highlighting this 1 2 3 Excel is smart and it’s figuring out. Ok, that’s what he wants. He wants to continue that pattern. So now, as I use the autofill handle, look what it did it extended the pattern now the pattern can be kind of complicated if you want it to be so, I put in let’s say some odd numbers.
Only click and drag to highlight the odd numbers use. The autofill handle and look it continued. The pattern only odd numbers, so you can play with that and see what kind of patterns the autofill handle in Excel can recognize. Now the autofill handle or again it can be called just the fill, handle also works, particularly well with anything related to time, and so here we have a time here at the left. This is a weekly schedule and we have one day Sunday, but I’d like to add all of the days here. Well, do days relate to time.
Yes, they do and so watch what happens. If i click on sunday and use the autofill handle, i can click and drag and as i do, you can see a preview appear down here. So i can see that it is extending the pattern now there wasn’t a pattern but because sunday is related to time. The autofill handle just figures it out and realizes that i’d like to have monday come after sunday now, what? If that’s not the case? Typically, yes, you want monday to follow sunday, but what if i just want the word sunday repeated over and over, if you want to force the autofill handle in Excel to copy like it did with jason smith and not to extend a pattern all you have to Do is click on the cell use the autofill handle, but before you click hold the control key on the keyboard and then drag, and that way it forces it to copy and not to extend the pattern or follow a time sequence. I’M gon na undo that, because i really do want the days of the week and i’ll just clear out jason smith here at least the extra ones. Now, let’s look at times here we have 6 a.m. I’ll, just go to the autofill, handle click and drag and pull that down until let’s say 10:00 p.m. because the time of day is related to time.
The autofill handle just knows what to do it just added an hour to each cell and when it got to p.m. it didn’t hesitate, it just switched to p.m. now. You might be thinking what, if you want it to be, not 6 a.m. to 7 a.m. but maybe 6 a.m.
to 6:30 a.m. well just type in 6:30 a.m. and then show Excel the pattern. So I clicked and drag to highlight 6:00 and 6:30. That’S the pattern. I go to the autofill, handle click and drag and pull down, and now the pattern goes every 30 minutes, not every 60 minutes, so this autofill handle is really pretty smart.
Other things related to time that work well with this include also the months so January, and you can spell it out or you can abbreviate it either way, but it’s understood by the autofill handle to be months which are related to time, and so it just automatically Extends the pattern, but again, if you don’t want it to do that, hold ctrl and click and drag the autofill handle and it will just copy and not extend a pattern now. One other thing to be aware of when you’re working with a small, manageable spreadsheet, like this one that really only has 20 or 30 rows in it, it’s totally reasonable to just click and hold the autofill handle and drag down right. That’S not a burden! That’S not difficult, but imagine a spreadsheet with 500 rows, a thousand records or whatever it is. How tedious would that be to click and drag the autofill handle down? Well, the good news is instead of clicking and dragging it down. You can highlight the cell, or in this case the pattern, the range that you want to copy or extend and then just go to the autofill handle and double click. Now you can see what it did it extended the pattern all the way down. As far as my data goes so because my data only reaches row 19, that’s as far as the autofill double-click went, it didn’t go any further because there’s no data below that, and so that’s why? If I go to a new sheet – and I just type in January – and then I click on the cell and double click on the autofill handle, look, nothing happens. Why? Because January is already as far down as my data goes, there’s no data below it. If I add some data like that, then now I can double click and it extends the pattern or the time sequence as far down as my data exists. So back on sheet 1.
I can just double click here on the April, we’ll fill handle and it fills it in the rest of the way down. So I hope that you’ll look for opportunities to use the autofill handle when you’re using it use the click and drag method. When you’re dealing with a short list like this one here, if the list is much longer, you might want to just double click on the autofill handle and have it automatically populate down the page and then remember, if you don’t want to extend the pattern or the Time sequence just hold the ctrl key when you click and drag, and it will just simply copy and repeat thanks for watching. I hope you found this tutorial to be helpful.
If you did, please click the like button below and consider connecting with me on my social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe, click the bell. Next to the subscribe button that way, you’ll be notified whenever I post another video and watch for a new video from me at least every Monday. If you’d like to support my channel, consider becoming a supporter of mine through my patreon account and you’ll see a link to that in the description below .