For this lesson you need to open the sample file: The World's Fastest Cars 12 from your Sample Files Folder. And, if it's not already selected, select the World Cars worksheet.
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 AutoFill 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 US dollar prices to Great Britain Pounds.
I'll begin by inserting a column to the left of Column H. You learned how to do this in Lesson 3-1. I select Column H, then right click in the selected cells, and select Insert from the Shortcut Menu.
I'll now add a column header to this column, which will be Price, open brackets, GPB and I'll close the bracket.
In order to convert prices I need an exchange rate, and I'll place the exchange rate in cell H1. So I'll put a label in G1 that will be: USD/GBP. And the exchange rate today, as I record this video, is 0.59423. But of course, by the time you watch this video that exchange rate will have changed.
If you'd like to use an up-to-date exchange rate, go to www.oanda.com, where you'll quickly be able to get today's USD/GBP exchange rate. And if you're in a country other than the USA or Great Britain you might find it fun to use the exchange rates from your own country.
I now need to place a formula in cell H4 that will calculate the price in Great Britain Pounds. The exchange rate means that you can buy one US Dollar for 0.59423 Great Britain Pounds. So it's very easy to convert the price of the Shelby into Great Britain Pounds.
I'll type = on the keyboard to start the formula, then click on the Shelby price, then the Multiplication Operator, and then the value in cell H1. And when I press the Enter key, I can see that a Shelby Aero costs 388,864 Great Britain Pounds.
Now you might think that you can simply AutoFill the formula in cell H4 to the end of the range. But, in the case of this worksheet, that will give incorrect results. Let's examine why.
First of all I'll change the worksheet so that, instead of displaying values in cells, it displays formulas. To do that I'll click Formulas on the Ribbon and, in the Formula Auditing group, I'll click Show Formulas. And formulas are now displayed instead of values.
And now I'm going to AutoFill the formula in cell H4. So I click cell H4 and then drag the AutoFill Handle to the end of the range.
Let's look at the first formula: the formula in cell H4. It's the price of the Shelby multiplied by the exchange rate; and that's exactly what I want.
Now let's look at cell H5. I can see that AutoFill's done a wonderful job of adjusting the G4 part of the formula so that it now references the price of the Bugatti. But look what it's done to the exchange rate reference. Instead of referencing the exchange rate in H1, it's referencing the blank cell in cell H2. So the result of this calculation would be a price of zero for the Bugatti Veyron.
And let's look at the Koenigsegg. This time things are even worse. I'm multiplying (correctly) the price of the Koenigsegg by the text value in cell H3. And because you can't multiply by a text value, this would result in an error in cell H6.
It should now be clear that, what I really wanted AutoFill to do, was to adjust the G4 part of the formula but leave the H1 part of the formula alone. In Excel terminology we'd say that G4 is a Relative Reference and H1 is an Absolute Reference.
So how do I tell Excel that H1 should be treated as an Absolute Reference? I need to edit the formula, and I'll use the Formula Bar for this because that's more convenient than editing in the cell.
I'll click just to the left of the H of H1 and type a dollar sign on the keyboard.
Now it's important to realize that this has nothing at all to do with dollar (the currency). It's simply the symbol that we use to denote an Absolute Reference wherever it's used.
Then I'll move to the right of H and add another dollar sign before the 1. It's important that you put two dollar signs in your Absolute Reference. The reasons for this will become apparent in the next lesson, when you learn about Mixed Cell References.
I'll now press the Enter key to save the formula into the cell.
Now you can see that nothing's happened in the other cells in the range (cells H5 to H15). That's because I'll need to AutoFill again to adjust the other formulas. So I'll click in cell H4 and then AutoFill to the end of the range.
Let's now examine the formulas in Column H. The formula in cell H4 is going to work correctly again: the price of the Shelby multiplied by the exchange rate. In cell H5 I'm multiplying the Bugatti price by the exchange rate; that will work fine. And in H6 I'm multiplying the Koenigsegg price by the exchange rate. So everything should now work fine.
I'll now switch the worksheet back to display values instead of formulas. So Formulas tab on the Ribbon and, in the Formula Auditing group, I'll click Show Formulas once again. And I can now see all of the correct prices for each car in Great Britain Pounds.
Before winding up this lesson I'd just like to show you a faster way of adding those dollar signs automatically. I'll click back on cell H4 and I'll remove those dollar signs from the existing formula. I'll now click to the right of H1.
Now in order to automatically apply those dollar signs, the flashing cursor must be touching the cell reference that you want to convert to Absolute. What do I mean by that? Well the flashing cursor is currently to the right of the 1 of H1, so it's touching the formula and things would work fine. If I click in between the H and the 1, that would be fine too, because the flashing cursor is still touching the cell reference. And if I click to the left of cell H1, that's going to work as well; the flashing cursor is still touching the cell reference.
When you're sure that the flashing cursor is touching the cell reference, press the F4 key on your keyboard, and those dollar signs magically appear.
Now if you only see one dollar sign it's because you accidentally pressed the F4 key twice. I'll just press the F4 key again and you can see that one of the dollar signs has disappeared. If this happens to you, just keep pressing F4 and eventually both dollar signs will reappear. I'll press F4 again, and I now have a dollar sign in front of H, press again, and they've both disappeared. Press again, and they're both back.That's what I want.
The reason this happens will be apparent to you when you understand Mixed Cell References after completing the next lesson in this course.
I'll now press the Enter key to save that value into the cell.
All that remains now is to save your work. And I'm going to save with the new name: The World's Fastest Cars 13. And, as usual, I'll save into the folder above my Sample Files Folder.
I click the Save button, and you've now completed: Lesson 3-12 Understand Absolute and Relative Cell References.