How to Make a Cell Reference Absolute in Excel

Techwalla may earn compensation through affiliate links in this story.
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.

Advertisement

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.

Advertisement

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.

Advertisement

Advertisement

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.

Advertisement

Advertisement

Report an Issue

screenshot of the current page

Screenshot loading...