Excel's default formula notation uses relative cell references. Relative references automatically change when you copy cells with the clipboard or fill handle, making it easier to reuse formulas. In cases where you need Excel to keep the exact cell referenced in a formula, switch the formula to use absolute references by pressing "F4" to add a dollar sign to the equation.
Select the original cell that contains the correct reference and click in the formula bar or press "F2" to edit the formula.
Video of the Day
Select the section of the formula that contains the reference you want to change. For example, in the formula "=A1&B1," select either "A1" or "B1." Press "F4" to change the reference type. Each time you press "F4," Excel advances through a cycle of four options: B1, $B$1, B$1 and $B1. To make the reference fully absolute, set the equation to $B$1.
Drag the fill handle or copy and paste the cell to extend the formula into other cells. If you filled cells before making the necessary references absolute, leading to incorrect results, paste or fill over those cells to fix them.
When moving rather than copying a cell, the contents never change, even if the cell includes relative references.
In addition to fully absolute and fully relative references, Excel can mix references. $B1, for example, keeps the column B absolute, but allows the row to change. B$1 does the opposite, allowing the column to adapt while retaining the row.
To make an exact copy of a cell without switching the cell to use absolute references, select the formula in the formula bar, copy it with "Ctrl-C," press "Esc" and paste it in another cell.
Excel adjusts relative references according to the distance between the original cell to the copied cell. For example, if D1 contains a relative reference and you copy the cell to E3, the referenced cell shifts one column right and two rows down.
Pressing "F4" serves as a shortcut to enter dollar signs in an existing equation, but you can also type "$" by hand when writing a formula to make it absolute.