For this lesson you need open the sample file: First Quarter Sales and Profit-11 from your Sample Files folder.
In this lesson we're going to learn some advanced AutoFill techniques that will massively speed up your efficient use of the AutoFill feature.
I'll begin this lesson by deleting the contents of cells G18 to J24. So I select the cells and then press the Delete key on the keyboard.
In the last lesson you learned how to AutoFill a date. I'm going to type a date: 01-01-09, into cell G18. And now I'll AutoFill it to the end of the range. So I hover over the AutoFill Handle and click and drag to the bottom of the range. And you can see that that was fast, simple and convenient for this worksheet, because the range only contains seven rows.
But imagine you're working with a range with hundreds, or even thousands of rows. It would take a long time to drag to the end of a list that was that long. So let's look at how we would solve that problem.
I'll delete the contents of G19 to G24, and this time I'll automatically AutoFill to the end of the range. To do that, I hover over the AutoFill Handle, (as we did before), until I see the black cross cursor shape, but this time, instead of clicking and dragging, I double click. And when you double click, Excel AutoFills to the end of the range, no matter how long that range is.
You can see that, by default, AutoFill will AutoFill consecutive dates. But in the last lesson, we considered the situation where you wanted to copy the 01-01-09 into cells G19 to G24, instead of AutoFilling consecutive dates.
In the last lesson we looked at two different ways of doing this. The first was to click on cell G18, then drag to the bottom of the range, then to click on the Smart Tag, and then to click Copy Cells.
But that took three mouse clicks. So we looked at a faster technique, and that was to AutoFill in exactly the same way, but this time to use the right mouse button. This time, when the right mouse button is released, a pop-up menu automatically appears. So we can click: Copy and this time it only took two clicks of the mouse.
But there's an even faster method that will only take one click of the mouse. And that is to select the cell that you want to copy, hold down the Control key on the keyboard, then drag the AutoFill Handle down to the end of the range, and when you release, the contents of cell G18 is copied into cells G19 to G24, this time with only one click of the mouse.
In an earlier lesson you saw how it was possible to take a day of the week and AutoFill to get the other days of the week. Suppose you had four employees: Tom, Dick, Harry, and Fred. And you thought it would be wonderful if you could AutoFill in a similar way to days of the week. In other words, you could type: Tom, into a cell and then AutoFill and get Dick, Harry and Fred in the next cells in the range. Well that is possible but, of course, Excel doesn't yet know that you have employees called: Tom, Dick, Harry and Fred.
We'll need to let Excel know about this by creating a Custom List.
To create a Custom List, click the File tab in the top left hand corner. Next you need to click: Options, on the left hand Menu Bar. Then you need to click: Advanced, in the left hand Menu Bar. You then need to scroll down to the General section and, in the General section, notice that there's a button that says: Edit Custom Lists.
I'm going to click that button now, and the Custom Lists dialog appears. I'm going to add a custom list showing the four names: Tom, Dick, Harry and Fred. So I'll click in the List entries box and type: Tom, Enter key, Dick and the Enter key, Harry Enter key, and Fred, and then I'll click the Add button.
In the left hand pane you can see how Excel's been working that magic with days of the weeks and months of the year. They're simply predefined custom lists. And we now have a new custom list containing the words: Tom, Dick, Harry and Fred.
Let's test our new custom list. So I'll click OK to close the dialog, OK again to close the Excel Options dialog, and I'll type: Tom into a cell, and then AutoFill that cell down the column. And you can see that the custom list is working just fine.
All that remains now is to save your work. And I'm going to save on my Computer. I'm going to then browse and select the new name: First Quarter Sales and Profit-12. And as usual I'll save into the folder above my Sample Files folder.
I click the Save button and you've now completed Lesson 2-17: Speed Up Your AutoFills and Create a Custom Fill Series.