Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10
Chapter 3
Using Cell References
In this chapter you learn to pull the numbers out of the cells in your worksheet and use them in your formulas. You also see how you can turn one formula into dozens with formula copying.
Exercise #1: Calculate sales tax
Here's an exercise that shows you how to put a cell reference in a realistic formula.
Begin by opening the Buy a new phone.xlsx spreadsheet.
Click the link above to download the workbook file to your computer, then open it. Excel will start in protected view, because it doesn't know whether it should trust your file. To accept the file and switch out of protected view, click Enable Editing in the yellow bar at the top of the Excel window.
Here you start with the list price of a new phone, but it's up to you to write the formula that calculates the tax, assuming a tax rate of 4.375%. First, find the cell where you need put the tax formula.
That's cell B8.
Now figure out what the formula needs to do. First it starts with a cell reference. But to which cell?
Cell B6, which has the list price. So the formula starts =B6
-
Now make your calculation.
To get the amount of tax, multiple the list price and the tax percentage:
=B6*4.375%
Notice you can use the percentage sign in your formula, which is handy for keeping things clear. But this isn't the only way to write the formula. If you don't add the percentage sign, you just divide the tax amount by 100, like this:
=B6*0.04375
-
The next step is to write a formula that gets the total cost, list price and tax included. To do that, you'll need two cell references. See if you can identify the cells you need.
The cells you need to reference are B6 and B8.
-
Now write the formula that calculates the total price, with tax. It goes in cell B9.
There's more than one way to write this formula, but you want to do as little work as possible and not calculate the tax all over again. (Why? If you calculate the tax in more than one formula, you make your life more difficult if you need to change the tax rate later).
=B6+B8
-
Now change the list price and watch what happens. Excel will recalculate your formula and update the tax amount to match.
-
Here's a bonus improvement that will make this worksheet even better. Instead of keeping the tax rate hidden in your formula, a better organization is to put the tax rate somewhere on your spreadsheet. You can then pull the value from that cell in your formula. To make this change, start by typing the tax rate into cell B7.
-
There's one minor hiccup. When you type 4.375% into the cell, Excel changes it into 4.38%. Do you know what causes this problem? If so, you can correct it by making a formatting tweak in the Home►Number section of the ribbon.
Technically, Excel hasn't done anything to your number. (To verify this, look in the formula bar where, confusingly enough, it still says 4.375%.) The problem is how Excel has decided to format the cell on the worksheet. Excel assumes you want to see just two decimal places, and so it rounds the displayed version of the number to fit.
To avoid this confusion, go to the Home►Number section of the ribbon. Click the Increase Decimal button, which looks like some tiny zeroes with a left-pointing arrow. You only need one extra decimal place, so you only need to click the button once.
-
Finally, change the formula in B8 so it uses the tax percentage in B7.
Stuck? Check the Solution worksheet to see the first version of the tax formula, and check the Bonus worksheet to see the revised tax formula.
Exercise #2: Copy a calorie formula
This exercise shows you how to quickly make new formulas by copying and pasting.
Open the Calorie counter.xlsx workbook.
You start with one formula (in cell E7). It calculates how many calories of chocolate pudding you've eaten. You can use formula copying to reuse this formula for every other type of food. First, move to E7.
Press Ctrl+C to copy the cell.
Move to cell E8 and press Ctrl+V to paste the formula.
Check the formula bar. You'll see a formula that's switched from calculating chocolate pudding calories to oreo ice cream cake calories. How do you think this happened?
Excel adjusted the formula copy to match its new place in your worksheet. You copied the formula from cell E7 to cell E8 (shifting it one row down). That means Excel shifts all the cell references one row down, so D7 becomes D8, B7 becomes B8, and C7 becomes C8.
There's no reason to paste just one copy of a formula at a time. In fact, you can fill a whole column with formula copies. Begin by pressing Ctrl+C to copy the formula. (You can copy the formula in cell E7 or E8. There's really no difference.)
Now select all the blank cells that are left in the "Calories Eaten" column (from cell E9 down to E16).
If you've forgotten how to select a bunch of cells, there are basically two ways to do it. You can use the mouse (click E9 and drag down to E16). Or, you can use the keyboard (move to E9, and hold down the Shift key while you press the down key, until you reach cell E16). Your selection should look like this:
Press Ctrl+V to put formula copies in all the selected cells, all at once. As always, you can double-check your work in the Solution worksheet.
Exercise #3: Copy a calorie formula (again)
This exercise shows you what happens when formula copying goes bad--and how you can solve the problem with fixed references.
Open the Calorie counter 2.xlsx workbook. You start with one formula (in cell D8). It looks at how many calories you ate in a meal (cell C8), and compares it against your calorie goal (cell C5).
Copy the formula from cell D8 and paste it in cell D9. Take a look at the formula copy in the formula bar. What's wrong with it?
The formula copy looks like this:
=C9-C6
The C9 cell reference is correct. It points to the amount of calories eaten at lunch.
The C6 cell reference is wrong. It points to an empty cell. It should point to the calorie goal in cell C5.
Even though the formula seems to work, it's calculating the wrong result, indicating that you are 580 calories over your goal when you are really just 80 calories over. You could fix the problem by editing the formula copy. But you'll need to do that every time you copy the formula. A better approach is to change the original formula in cell D8 before you make your copies.
Right now, the formula in D8 uses relative cell references. To fix your problem, you need to change one of relative cell references to a fixed reference. Which one is it?
You need to change the relative reference that points to the calorie goal, because it's the one that is being adjusted incorrectly. Change it from C5 to $C$5.
Move to cell D8 and change the formula to use the fixed cell reference.
It should look like this:
=C8-$C$5
Now repeat step 2 to copy your formula in cell D8 and paste it in cell D9. Take a look at the newly copied version in the formula bar. Is it correct?
The new formula grabs the amount of calories eaten, and correctly subtracts the calorie goal from cell C5.
=C9-$C$5
Now that you've fixed the original formula, you can paste it in a whole bunch of cells in a single step. Copy the formula (Ctrl+C), select the remaining cells in the "Amount Over" column, and then paste in all of them at once (Ctrl+V).