How to change relative reference to absolute reference in Excel?

In Excel, cell references are relative by default, changing when formulas are copied to other cells. To copy a formula without changing its cell references, convert it to an absolute reference either by pressing F4 or adding a dollar sign ($) before the row and column numbers. When needing to copy multiple formulas with unchanged references, a specialized method is required. In this article, we will introduce three methods for changing relative reference to absolute reference in Excel.

Change a formula from relative reference to absolute reference by pressing F4

Change multiple formulas’ cell references from relative to absolute with one click by using a handy tool

Change a formula from relative reference to absolute reference by manually adding dollar sign ($)

Change a formula from relative reference to absolute reference by pressing F4

Supposing the column C is using formula (=A1*B1) to multiply column A by column B. When we copy the column C to other cells, the calculating results will change, because the formula (=A1*B1) is using the relative cell reference. If we want to copy the column C without changing cell references of the formulas, we can change the cell references from relative to absolute before moving it.

If we don't change the cell references from relative to absolute, after moving the column C to Column E, all values will be changed. See screenshot:

In this situation, we can press F4 to toggle relative references to absolute references. Please do as follows:

1. Put the cursor behind A1 in the formula bar, press F4 once, it will become $A$1.See screenshot:

2. Then put the cursor behind B1in the formula bar, press F4 once, it will become $B$1. See screenshot:

After changing the formula’s cell references to absolute address, we can copy the formula and move to other cell without changing cell reference. And get the value unchanged.

Change multiple formulas’ cell references from relative to absolute with one click by using a handy tool

The above method is easy to change one or two formulas’ cell references, but if there are a range of formulas need to be changed, it may be troublesome and time-consuming. Kutools for Excel can help you change a range formula’s cell references quickly and comfortably.

After installing Kutools for Excel, you can change relative reference to absolute reference quickly by following the next steps.

1. Go to select the range that contains formulas you want to change cell references in worksheet.

2. Click Kutools > More> Convert references in the Formula group. In the Convert formula references dialog, choose To absolute option from Convert. And then click OK.

It will convert cell references in formulas to absolute references. Now, we can copy and move the formula to any cells in current worksheet without changing cell references in formulas.

Change a formula from relative reference to absolute reference by manually adding dollar sign ($)

Click the formula cell that you want to change, then add $ dollar sign before row and column. Then tap Enter, and cell references in formulas will be changed from relative reference to absolute reference.

1. Put the cursor before A in A2 in the formula bar, and add $ dollar sign.

2. Put the cursor before 2 in A2 in the formula bar, and add $ dollar sign.

3. Repeat above steps to add dollar sign ($) before row and column to change the formula’s cell references to absolute address.

After changing the formula’s cell references to absolute address, we can copy the formula and move to other cell without changing cell reference. And get the value unchanged.

Related articles