For this lesson, you need to open the sample file: The World's Fastest Cars-12, from your Sample Files folder. You've seen how useful Excel's AutoFill is when copying formulas. Most of the time things work perfectly, as you normally want to increment row and column references when you auto fill down and across. But sometimes AutoFill can be a little too helpful, when it adjusts cell references that you'd like to be left alone. This lesson will illustrate the type of worksheet that requires absolute cell references, as we convert the car prices from US dollars to Great Britain pounds. We'll begin by inserting a column to the left of column H. To do that, I right-click the column Header and choose Insert from the Shortcut menu. And, in cell: H3, I'll type: Price (GBP), for Great Britain pounds. Now, in order to convert from US dollars to Great Britain pounds, we'll need an exchange rate. So, in cell: G1, I'll type: USD/GBP. And in cell: H1, I'll type today's exchange rate which, when I recorded this video, was 0.62233. But, by the time you watch this video it will have changed, of course. If you want to get the up-to-date exchange rate, go to www.oanda.com and you'll be able to get an up-to-date exchange rate in real time. And, if you're not in England or America, it might be fun to change the exchange rate to match your own currency. The exchange rate 0.62233 means that it's possible to buy one US dollar for 0.62233 pounds. So it's very easy to calculate the exchange rate, in cell: H4. I type: =, as always, to start a formula. And then click once with the mouse on the US dollar price of the Shelby. I then put in a Multiplication operator and click once on the exchange rate. And when I press the Enter key, I can see that a Shelby Aero will cost 407,253 Great Britain pounds. Now, normally, we'd auto fill a formula down the column to the bottom, but AutoFill won't give us the correct result in this case. Let's find out why. First, I'll ask Excel to display formulas on this worksheet instead of values. And to do that, I click the Formulas tab on the Ribbon, and in the Formula Auditing group, I click the Show Formulas button. And you can see the G4*H1 formula in cell: H4. Now, I'll auto fill to the end of the range. So I hover the mouse over the AutoFill button until I see the Black Cross cursor shape, and then click-and-drag down to the end of the range. And let's look at what AutoFill has done. The first reference: G4*H1, works, of course. That's the Shelby price in US dollars, multiplied by the exchange rate in cell: H1. But now, let's look at the Bugatti price. I click on cell: H5, and you can see that it's correctly adjusted G4 (to point at the price of the Bugatti). But it's also adjusted the exchange rate cell: H1. It's now pointing at H2. That contains no value at all, so this formula would result in a value zero, as we multiplied the Bugatti price by zero. And if I look at the next cell down, the Koenigsegg price, you can see that I'm trying to multiply the dollar price by cell: H3, which contains text. So this cell would contain an error. It should be clear that what we really wanted AutoFill to do was to change the G4 part of the formula in cell: H4, but leave the H1 part of the formula alone. In Excel terminology, we'd say that we wanted cell H1 to be an absolute cell reference, and G4 to be a relative cell reference. So how do we tell Excel that H1 is an absolute cell reference? Well, here's how. I'll edit the formula in the Formula bar, because it's easier to do it that way. In front of the 'H' of the absolute cell reference: H1, I'll type a dollar sign ($). Now, it's important to realize that this has got nothing to do with dollars (the currency). It's just the way in which we tell Excel, how to make any cell reference absolute. And, in front of the '1' of the H1 cell reference, another dollar sign. You must put the dollar sign in front of the letter and the number. You'll understand why later in this session, when we cover mixed cell references. I now press the Enter key and, of course, nothing has happened yet on the worksheet, because I need to auto fill cell H4 again in order for the auto filled values to take effect. So, once again, I'll auto fill to the end of the range, and now you can see that everything's going to be fine. In the case of the Shelby, we're multiplying cell: G4 (the Shelby price), by: H1 (the exchange rate). And for the Bugatti, it's: G5 (the price), but still: H1, for the exchange rate. And in cell: H6, we've also got the right formula: the Koenigsegg price multiplied by the exchange rate. So now, let's change the formulas back into values, by once again looking at the Formulas tab on the Ribbon and, in the Formula Auditing group, click the Show Formulas button once more. And I can now see the correct prices for all of the cars in Great Britain pounds. Before ending this lesson, I'd just like to show you a quicker way of applying those absolute cell references. Let's go back to the formula in cell: H4, and I'll remove those dollar signs that I put in earlier. In order to use this quicker way, you need to make sure that the insertion point (that's the flashing, vertical black bar), is touching the formula you want to make absolute. Well, at the moment, it's touching the '1', so that's fine. And if it were in the center, touching both the 'H' and '1', that would be fine, too. And also if it were on the left-hand side, touching just the 'H'. So now that the insertion point is touching the formula I want to make absolute, I press the F4 key on the keyboard. And when I press, you can see that two dollar signs are automatically inserted. Now, if you're only seeing one dollar sign, it's because you accidentally pressed the F4 key twice. If I press again, you'll see one dollar sign disappear. And then you'll see the dollar sign move, then disappear entirely, and then both reappear. You'll understand why this is useful when you complete the mixed cell reference lesson, later in this session. I'll now press the Enter key to finish the formula. And all that remains is to save your work, and I'll save with the new name: The World's Fastest Cars-13. And, as before, I'll save to the folder above my Sample Files folder. I click the Save button and now you've completed Lesson 3-12: Understand absolute and relative cell references.