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.
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 wroteyou 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.