For this lesson you need to open the sample file: First Quarter Sales and Profit-9 from your Sample Files folder.
In the previous lesson you learnt how AutoFill can save you a lot of time when extending or copying text and number ranges. But the story's not over yet, because AutoFill also works with formulas.
AutoFill's ability to copy and adjust formulas is one of the most powerful tools in Excel's impressive armoury.
Let's begin by considering the formula in cell B9. So I'll click on cell B9 and then look at the Formula Bar at the top of the screen.
I can see it's: =SUM(B4:B8). So it's correctly adding all of the sales figures in January to come up with a total of 129,000.
But now think: "What would be the correct formula in cell C9?" Well, this time it would be: SUM(C4:C8). And in D9 it would be: SUM(D4:D8). So it seems that the letter needs to be changed as we move to the right.
Let's look at the correct formulas now for cells B9 to D9 to see how this would work. You can see that the correct formula in cell B9 is SUM(B4:B8), in C9 it's C4:C8, and in D9 it's D4:D8. So every time we move a column to the right, the letter part of the formula needs to be incremented. And that's exactly what AutoFill will do: it will change B to C and then to D, as you move across one column, and one more column.
Let's see that in action now by going back to Normal view.
So I'll AutoFill the formula now in cell B9 to cells C9 and D9. I click on cell B9, hover over the AutoFill Handle until I see that black cross cursor shape, and then click and drag across two cells.
Now you might think something's gone wrong here, because the February total is identical to the January total. But that happens to just be a coincidence, because the sales figures for each month do both add up to 129,000.
But we should audit the formulas just to make sure. So I'll click in cell B9 and look at the formula on the Formula Bar: =SUM(B4:B8). That's perfectly correct.
Now I'll move across one cell to the right, and I can see: =SUM(C4:C8). Well, that's correct for February.
Then one more cell to the right, and it's =SUM( D4:D8). So AutoFill has done a fantastic job in incrementing the letter part of the formula when I AutoFill to the right.
Let's now look at another formula: the formula in cell E4. This is the average of cells B4 to D4.
But now consider the correct formula in cell E5. Well that would be: B5 to D5. And in E6 the correct formula would be: B6 to D6, and so on to the bottom.
Let's have a look at the correct formulas for cells E5 to E9 to see what they would be. And you can see that, in cell E4, the correct formula is: =AVERAGE(B4:D4). And then in the next cell down, it's B5:D5, then B6:D6, then B7:D7, then B8:D8, and then B9:D9.
So it would seem that when you travel down a column, the correct thing to do is to increment the numeric part of a formula. As 4 becomes 5, becomes 6, becomes 7, becomes 8 and becomes 9. And AutoFill is clever enough to do just this.
Let's restore things to normal now. And now let's AutoFill the value in cell E4 to the bottom of the range. So I hover over the AutoFill Handle until I see the black cross cursor shape, and then click and drag to the bottom of the range. The numbers look quite good.
Let's audit the formulas. =AVERAGE(B4:D4), that's correct. =AVERAGE(B5:D5), that's good. And then B6:D6, B7:D7, B8:D8, and B9:D9. So AutoFill has applied the rule: "Increment the numeric part of cell references when a formula is AutoFilled down a column." And it's done a great job.
Let's see if it can do the same for cell F4. This time it's =MAX(B4:D4). So I'll AutoFill down to the bottom of the column.
Let's audit these numbers in a different way. F4: 29,000, that is the maximum for New York, for Jan, February, March. I can see that happened in February. For Los Angeles 43,000 is the correct number: the maximum sales in March. 22,000 for London is correct again, once again in March. 35,000 for Paris, yes, that would be January sales for Paris. And 15,000 for Munich is February. So AutoFill has once again done a fantastic job.
In the next two lessons you'll discover some of AutoFill's even more powerful features. And in Session 3 we're really going to take AutoFill to the max.
But for the moment all that remains is to save your work. And I'm going to save with the new name: First Quarter Sales and Profit-10. And as usual I'll save to the folder above my Sample Files folder.
I click the Save button, and you've now completed Lesson 2-15: Use AutoFill to Adjust Formulas.