- Appendices
Most formulas in Excel contain references to one or more cells. When you copy such formulas to another location, the cell references automatically adjust to their new location. These references are called relative—they adjust when moved or copied.
Usually that is good news, but sometimes you want certain references to keep referring to the same cell, or at least to the same row or the same column. Such references are called absolute—they remain “locked” when moved or copied.
Unlocked, or relative, references look like this, =A1, whereas locked, or absolute, references look like =$A$1 or =A$1 or =$A1. The $-sign locks one part of the reference. It is not really a dollar-sign but a string-sign. You can type that sign by hand, but it is usually much easier and faster to hit the key F4. The F4-key is a cycle key—it cycles from A1 to $A$1 to A$1 to $A1 and then starts all over again.
I explained this behavior in the figure above (file 9-Appendices.xlsx on sheet “Locking”). Cell F4 has the following formula in it: =$E3*F$2. Notice that column E is locked, so it stays that way when we copy the formula to the right, whereas row 3 is unlocked and will change into 4 in the next cell down. Notice also that row 2 is locked, so it keeps referring to row 2 when copied down, whereas column F will change to G when copied to the right (or to E when copied to the left). As a consequence of these “locks,” the formula can be copied over the entire range F3:I12.
Sometimes it is nice, handy, or even necessary to see all the formulas on your sheet at once—for instance, to make sure your formulas remained correct after you moved or copied them. If you want to get a formula-view of your sheet, just hit (this is called a tilde, which can be found under the Esc key). Ctr ~ is a toggle shortcut, switching back and forth between value-view and formula-view per sheet.
Locking cells can also be very important when you work with conditional formatting. Say we have the following situation: We want to compare an old value with a new value by marking the new value if it went up by more than 5, 10, 15, or 20 units. In this case (shown below), the values I used are systolic blood pressure (BSP) units in mmHg.
The amount of units up is chosen in cell G1 on sheet “ValueUp” shown below. After selecting the cells that we want to mark conditionally, D2:D19, we implement conditional formatting with the following formula: =($D2-$C2)>$G$1.
Make sure that the references to columns D and C are locked, but not to their rows. The reference to cell G1 has to be completely locked, as every cell in column D has to “listen” to that one cell G1. If you do not follow these rules, conditional formatting will not work the way you want it to work.
