For this lesson you need to open the sample file: First Quarter Sales and Profit-10 from your Sample Files folder.
Sometimes AutoFill begins to misbehave, and actually gets in the way of efficient work by wrongly anticipating what you need.
Let me demonstrate this. In cell F18 I'm going to type the date: 31-Mar-09.
Now let's imagine that you're entering transactions, and you have several transactions for the 31st of March, and you want to AutoFill cell F18 so that you get the same value in cells F19 to F24. We want the 31st of March in each cell. But when I release the mouse button I get sequential dates, and that isn't what I wanted.
Well, notice there's a Smart Tag at the bottom right of the autofilled range. And if I click on that Smart Tag, you can see that Copy Cells is an option at the top of the list. So if I click Copy Cells, I get exactly the result I wanted: the 31st of March in each of the cells.
But what were those other AutoFill options all about? Well to demonstrate them I'll select cell F18 and I'll AutoFill four cells to the right: one, two, three, four. I'll then click that Smart Tag, and once again I'll select Copy Cells to put 31st of March in all four cells.
Now you can see that the cells aren't wide enough to display their contents, so I'll select columns G, H, I and J, hover over the intersection of two of the column headers, and then drag slightly to the right, to make the columns a little wider.
Let's begin with the value in cell G18. I'll AutoFill to the end of the range and then click the Smart Tag.
Options 3 and 4: Fill Formatting Only and Fill Without Formatting, will be covered later in Session 4: Making Your Worksheets Look Professional. At this stage in the course it won't be clear what they do just yet. And the last option: Flash Fill, will be introduced later in this Session, in Lesson 2-18.
So the first option we'll explore now is: Fill Days. I'll click the Fill Days option button and you can see that the result is quite disappointing. It's exactly the same in this case as Fill Series, when the date is incremented one day at a time.
Let's now look at the Fill Weekdays option. I'll click on cell H18. I'll AutoFill to the end of the list. I'll click the Smart Tag and then select: Fill Weekdays. This time you can see that we've skipped from the 3rd of April to the 6th of April. That's because the 4th and 5th of April 2009 were a Saturday and Sunday, and this sequence is skipping the weekend days.
Now let's look at Fill Months. I'll click on cell I18. I will AutoFill to the end of the range. I'll click the Smart Tag and this time select Fill Months. You can see this time the month has been incremented as I've AutoFilled down the range. But the 31st can't be used as the day, in the case of April, June and September, because those months only have 30 days. So AutoFill has used the last day in the month.
The last option was Fill Years. I'm going to do this in a slightly different way. I'll click on cell J18 and this time I'm going to use the Right mouse button to AutoFill. So I hover over the AutoFill Handle, until I see the black cross cursor shape, then hold down the Right mouse button and drag to the end of the range. This time you can see that I've saved a click, because the AutoFill Options Menu has appeared instantly, saving me having to click the Smart Tag. I'll click Fill Years and you can see that this option simply increments the date by one year, as each cell is filled down the range.
All that remains for this lesson is to save your work, and I'm going to save with the new name: First Quarter Sales and Profit-11. And as usual I'll save to the folder above my Sample Files folders.
I click the Save button, and you've now completed Lesson 2-16: Use AutoFill Options.