For this lesson, we need to open a practice file: First Quarter Sales and Profit-9. Of course, if you're working through the course sequentially, you'll already have this open from the previous lesson. AutoFill can save you a lot of time when extending or copying text and number ranges, but the story is not over yet. AutoFill's ability to copy and adjust formulas is one of the most powerful tools in Excel's impressive armoury, and we're going to see how AutoFill can help replicate formulas in this lesson. First, I want you to think about the formula in cell B9; you can see it on the Formula Bar at the top of the screen. The formula is: =SUM(B4:B8), because it's adding up the range B4 to B8, to give the total January sales. Now, think about the formula that would be applicable to cell C9 (the sales for February). It would be almost the same: =SUM(C4:C8). And if you think about the right figure for March, in D9, well, that would be almost the same, again: =SUM(D4:D8). So it seems that, as you move to the right, it's logical that the letter needs to be incremented in a formula. Let's just illustrate that on screen. These are the actual formulas you'd want to see, to get the right answers in B9, C9 and D9: =SUM(B4:B8), =SUM(C4:C8), and: =SUM(D4:D8). Now the good news is that AutoFill is capable of doing this for you. If you were to AutoFill cell B9, as you auto fill to the right, AutoFill knows that you probably want it to increment the letter part of the formula, so it creates the new formulas for you, automatically. Okay, we're back to normal again. Let's do it. I'm going to click on cell B9, hover over the AutoFill button on the bottom right-hand corner until I see the Black Cross cursor shape, and then click-and-drag to the right, twice. When I release the mouse button, I get the right answers in cells C9 and D9. It might look wrong to you because, coincidentally, the sales in February were the same as the sales in January. So let's just audit that, and prove that what we thought would happen has happened. I click on cell B9, and look on the Formula Bar at the top of the screen, and I see: =SUM(B4:B8). I click on C9, and it's: =SUM(C4:C8). And then I click on D9, and I now have: =SUM(D4:D8). So AutoFill did a great job for us. Now let's consider the formula in E4: =AVERAGE(B4:D4). Now, if we were to AutoFill this down, we wouldn't want it to change the letter part of the formula; we'd want it to change the number part of the formula, because the correct formulas for Los Angeles, London, Paris and Munich, will be AVERAGE B5:D5, B6:D6, B7:D7 and B8:D8. I wonder whether AutoFill is clever enough to do that for us. Let's click on cell E4, look for that Black Cross cursor shape, and auto fill down to E9. When I release the mouse button, I've got all the right answers, because AutoFill's done exactly what we wanted it to. Let's look at those formulas: =AVERAGE(B4:D4), =AVERAGE(B5:D5), (B6:D6), (B7:D7), (B8:D8), (B9:D9). So when we auto fill downwards, AutoFill changes the number part of the formula, and when we auto fill across, AutoFill changes the letter part of the formula. That ought to work as well on the formula in F4: =MAX(B4:D4), because if the numbers increment on the way down, we'll have B5:D5, B6:D6, etc. Let's try it out. I auto fill down, and I've got the correct Maximums for each of the cities. And if I look here: 35,000, that's correct, for Paris, in January. 15,000 is correct, February for Munich. Let's look at the formulas: =MAX(B4:D4), B5:D5, 6, 7, 8, 9; just as we expected, and just as we wanted. AutoFill does have a lot more amazing powers, and a lot more wrinkles in how we can use it, and we'll be seeing some of those in later lessons. But for now, we're done with AutoFill, so let's click: Save As: Excel Workbook. And, this time, I'm going to save it with the name: First Quarter Sales and Profit-10. Click the Save button, and we're finished.