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 across and down. 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 US dollar prices to GBP. We need to open a sample file, as usual, which is going to be: The World's Fastest Cars-12. If you're working through the course sequentially, as always, you'll already have this open. Let's first insert a column to the left of column H. To do that, you right-click on the column header of column H, and Insert from the shortcut menu. We're now going to type: Price (GBP), (Great Britain Pounds), in cell H3. And in cell G1, I'm going to put in my conversion rate, by typing: USD/GBP. I now press the Tab key, to move across one to the right, and let's put in the conversion rate. And the conversion rate's been flying all over the place recently, but when I recorded this, it was: 0.51195. In other words, you could buy a US dollar for 51 pence. If you want more realism, you can get the current exchange rate from www.oanda.com. If you're not in England or America, it might be fun to change the exchange rates to match your own currency, and see how much all of these cars would cost in your country. Let's first place a formula in cell H4, that will calculate the price of a Shelby in GBP. So I click in cell H4 and, as always, to start a formula, I press the Equals key on the keyboard, once. Then I'm going to click on cell G4, which contains the Shelby price. And, to work out the price in GBP, I'm going to multiply that. So that's: multiplied by the exchange rate, in cell H1. I how have the formula: =G4*H1. Let's see how much a Shelby costs in the United Kingdom: £335,000. Let's now consider what would happen if I was to auto fill this formula down to the bottom of the list, in column H. A good way to demonstrate this will be to change the worksheet so that it displays formulas, rather than values. To do that, click the Formulas tab on the Ribbon and, in the Formula Auditing group, choose Show Formulas. You can now see the formula: =G4*H1 (the price in US dollars, multiplied by the conversion rate). I'm now going to auto fill H4 down to the bottom of the list. And, as you know, when we auto fill formulas, the numeric part of the formula is incremented. So, in this cell, we've got: =G5*H2. But look what's happened: it's changed the reference to G5, which is what I want; G5 is the Bugatti Veyron price. But instead of applying the exchange rate in H1, it's now looking at H2 for the exchange rate (a blank cell). And if you look at the next cell down, we can see that we've got: =G6. That's right: the price of the Koenigsegg. But we're multiplying by a text string in H3 now, which will give an error when we try to multiply the numeric value of G6 by the text in H3. So you can see that, what we need to do, is tell Excel: adjust the G4, to G5, G6, G7; that's absolutely fine, but leave H1 alone, because H1's always in the same place. In other words, we need to make H1 into an absolute reference and leave G4 as a relative reference. So now that we understand absolute and relative references, how do we tell Excel that H1 should be absolute? Here's how it's done: Let's look at the formula in the Formula Bar, at the top of the screen; it's easier to edit up there. And I'm going to put a little $ sign in front of 'H', and a $ sign in front of the '1'. Now this has got nothing at all to do with the currency being in dollars, it just so happens that, to make a reference absolute, we put the $ sign in front of both the letter and the number of the cell reference. I now press the Enter key, on the keyboard once, and nothing's happened, of course, in the other cells, because I haven't yet auto filled the formula in H4. I now click in cell H4, hover over that dot in the bottom right-hand corner, and drag down with my mouse. Now look what's happened: it's exactly what we want to happen. In every case, we're referencing the exchange rate, and the price of the relevant car, so I can change back now from Show Formulas, to Show Values. And I can see the GBP price of each of the cars in the list. I'd like to now show you a faster way of adding those Dollar signs onto the formula. Let's click in H4 again, to demonstrate this. And I'll just remove those $ signs so that we are back where we started. So in order to apply those $ signs more quickly, first of all make sure that the insertion point (that's that flashing black bar) is touching the formula 'H1' in some way. You can see it's touching the 'H' now. If it was here, it's also touching the 'H' and the '1'. And if it's here, it's touching the '1'. Any of those three are just fine. When you've done that, press the F4 key on the keyboard, and the Dollars are put in for you. Now if you see only one $, it's because you accidentally pressed F4 twice. If that's the case, press it another couple of times and you'll get the $ in front of the letter and the number again. You'll see how we can use that technique to create a mixed reference in a later lesson. I press the Enter key now, and we can see the GBP price of the Shelby again. Only one more thing to do; that's to save the worksheet, so: Save As. And, this time, we'll call it: The World's Fastest Cars-13. And, as usual, we'll put that in the Practice folder. I click the Save button, and we're done.