Using Absolute References in Excel
This lesson explains why some formulas cannot be copied correctly with relative references alone and shows how to keep a row, column, or cell fixed with dollar signs and the F4 key.
9. Move to cell A21 and type % of Total Income and then press (Right) twice.
You will now create a formula that computes the percentage January contributed to the total profit for the six-month period. Eventually you will compute the percentages for each of the six months.
10. In cell C21 enter the following formula: =C20/C23.
This formula divides January’s profit by the total profit for all six months.
11. Press (Enter) to complete this formula.
Excel should display a value close to 0.179. This value is actually 17.9%. You’ll add the percentage formatting in a future lesson.
12. Use the Fill handle and copy this formula into cells D21 through H21.
Remember the Fill handle is the “skinny black plus”.
Using Absolute Reference
The result of copying the formula into cells D21 through H21 is an error message. You have received this error message because this formula is not a formula that can be correctly copied. You may recall that when you copy a formula, you copy what the formula does in relation to the cell where you copy it to.
In other words, the formula in C21 is C20/C23. Excel actually sees this as: divide the cell immediately above this cell (C20) by the cell two below this cell (C23). If you examine the formula in cell D21 you will see that it became D20/D23 when you copied it. Excel replicated the exact relationship the formula represented and adjusted the cells to the new cell. This is what formulas are supposed to do when you copy them, but this is not the result you are after.
Examining the formulas in E21 through H21 will reveal that each formula refers to two cells in the same column in which the formula is located. The actual error message is telling you that you are trying to divide by zero, a mathematical “no no”. There is no data in cells D23 through H23.
In this situation, you don’t want every part of the formula to change. Instead you’ll need every formula to always refer to the one cell that contains the Total Monthly Profit, cell C23. There are two solutions to this problem. One is to manually change each formula to refer to cell C23. The second, and preferred solution, is to change the way Excel treats C23 when it copies the formula.
Since you want all the other formulas to refer to C23 when you copy the formula to the other cells, you need to tell Excel to keep it constant, or absolute, when the formula is copied. You will not change the way Excel treats C21 because you do want this to change to D21 and so on.
The feature to do this is called absolute reference. In a formula, absolute reference is indicated with ($). For example C21/$C$23 would keep C23 as a constant when you copy that formula.
You can change a cell to an absolutely referenced cell either by typing the dollar signs in manually or by using (F4). In this portion of the lesson you will use absolute reference to make the formula in cell C21 one that can be copied.
1. Move back into cell C21 and click at the far right end of its formula on the formula bar.
2. With the insertion point blinking at the end of the formula, press (F4).
The F4 key adds dollar signs ($) before the row and the column. This key is a shortcut key for making a cell absolute. You can also simply add the dollar signs yourself by editing the formula. The dollar sign before the row and column tell Excel not to change this portion of the formula at all when you copy it.
There are variations of the absolute reference. For example, $C23 would tell Excel to keep column C constant but change the row when you copy to a new location. Likewise, C$23 would keep the row constant. For this formula, you want all the other places you copy it to refer to the total of all the monthly incomes in cell C23. To do this requires a dollar sign ($) before the column and the row.
Making cell C23 absolute in the formulas in cells D21 through H21 will cause Excel to display the correct percentage. You can now correctly copy the revised formula.
3. Use the fill handle to copy the corrected formula from C21 to D21 through H21.
This time you are able to copy the formula with correct results because you used absolute reference. You will add percentage formatting, (%), in an upcoming lesson.
4. Examine the formulas in cell D21 through H21.
Notice that each of these formulas now refers to cell C23. C23 did not change this time when you copied the formula because it was absolutely referenced.