MARISSA JONES ONLY!!!!

Understand Relative and Absolute Cell References

Learning Outcomes

· Identify cell referencing

· Identify when to use absolute or relative cell references

As you work in Excel, you may want to reuse formulas in different parts of a worksheet to reduce the amount of data you have to retype. For example, you might want to include a what-if analysis in one part of a worksheet showing a set of sales projections if sales increase by 10%. To include another analysis in another part of the worksheet showing projections if sales increase by 50%, you can copy the formulas from one section to another and simply change the “1” to a “5”. But when you copy formulas, it is important to make sure that they refer to the correct cells. To do this, you need to understand the difference between relative and absolute cell references.  You plan to reuse formulas in different parts of your worksheets, so you want to understand relative and absolute cell references.

Details

Consider the following when using relative and absolute cell references:

· Use relative references when you want to preserve the relationship to the formula location

When you create a formula that references another cell, Excel normally does not “record” the exact cell address for the cell being referenced in the formula. Instead, it looks at the relationship that cell has to the cell containing the formula. For example, in  Figure 2-12 , cell F5 contains the formula: =SUM(B5:E5). When Excel retrieves values to calculate the formula in cell F5, it actually looks for “the four cells to the left of the formula,” which in this case is cells B5:E5. This way, if you copy the cell to a new location, such as cell F6, the results will reflect the new formula location and will automatically retrieve the values in cells B6, C6, D6, and E6. These are  relative cell references , because Excel is recording the input cells in relation to or relative to the formula cell.

Figure 2-12Formulas Containing Relative References

In most cases, you want to use relative cell references when copying or moving, so this is the Excel default. In  Figure 2-12 , the formulas in cells F5:F12 and cells B13:F13 contain relative cell references. They total the “four cells to the left of” or the “eight cells above” the formulas.

· Use absolute cell references when you want to preserve the exact cell address in a formula

There are times when you want Excel to retrieve formula information from a specific cell, and you don’t want the cell address in the formula to change when you copy it to a new location. For example, you might have a price in a specific cell that you want to use in all formulas, regardless of their location. If you use relative cell referencing, the formula results would be incorrect, because the formula would reference a different cell every time you copy it. Therefore, you need to use an  absolute cell reference , which is a reference that does not change when you copy the formula.

You create an absolute cell reference by placing a $ (dollar sign) in front of both the column letter and the row number of the cell address. You can either type the dollar sign when typing the cell address in a formula (for example, “=C12*$B$16”) or you can select a cell address on the formula bar and then press [F4], and the dollar signs are added automatically.  Figure 2-13  shows formulas containing both absolute and relative references. The formulas in cells B19 to E26 use absolute cell references to refer to a potential sales increase of 50%, shown in cell B16.

Figure 2-13Formulas Containing Absolute and Relative References

Using a Mixed Reference

Sometimes when you copy a formula, you want to change the row reference, but keep the column reference the same. This type of cell referencing combines elements of both absolute and relative referencing and is called a mixed reference. For example, when copied, a formula containing the mixed reference C$14 would change the column letter relative to its new location, but not the row number. In the mixed reference $C14, the column letter would not change, but the row number would be updated relative to its location. Like an absolute reference, a mixed reference can be created by pressing the [F4] function key with the cell reference selected. With each press of the [F4] key, you cycle through all the possible combinations of relative, absolute, and mixed references (C14, $C$14, C$14, and $C14).