How to Make a Cell Reference Absolute in Excel

Using relative references can cause incorrect equations when copying a cell.

Image Credit: Image courtesy of Microsoft

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.

Step 1

Edit the formula in the original cell.

Image Credit: Image courtesy of Microsoft

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

Step 2

Press "F4" to make a reference absolute.

Image Credit: Image courtesy of Microsoft

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.

Step 3

Copy the formula.

Image Credit: Image courtesy of Microsoft

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.

Video of the Day