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.
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.
Video of the Day
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.