- Using Relative References in Formulas
- Using Absolute Cell References
- But Wait! There's More!
- Type a Few $s, Save Time
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
But Wait! There's More!
In addition to relative and absolute cell references, there are mixed cell references. In a mixed cell reference, one part of the reference is absolute and the other is relative.
Huh?
Look at the worksheet in Figure 7. Suppose you wanted to write a formula in cell C9 that could be copied to down and across through cell E12 without giving any incorrect results or error messages.
)
Figure
7 The challenge: Write a formula in cell C9 that can be copied through cell
E12.
Impossible, you say? Not at all. The formula would be =$B9*C$6.
To understand what this formula means to Excel, substitute the word always for the dollar sign. $B9 says always column B but the row reference is relative. C$6 says the column reference is relative but the row is always row 6. Get it?
Give it a try. When you copy the formula to the cells, the references and results are all correct, as shown in Figure 8. Take a moment to select the cells, one at a time, and look at the formula Excel wrote—you can see it in the formula bar.
)
Figure
8 A properly written formula can be copied with flexibility.
There's a shortcut for writing these fancy cell references. If you select a cell reference in a formula and press F4 (Windows) or Command-T (Mac OS), Excel cycles through the relative, absolute, and mixed cell reference options for the selected reference. For example, select cell C6. Each time you press F4 or Command-T, Excel cycles through $C$6, C$6, $C6, and back to C6. Stop pressing F4 or Command-T when you see the one you want.