Skip to main content

How to change relative reference to absolute reference in Excel?

Author: Tech Support Last Modified: 2024-11-15

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.

original data arrow right  copy formula without changing cell references

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:

 relative cell references arrow right  values will be changed when copying the formulas

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:

 select cell reference A2 and press F4

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

select cell reference B2 and press F4

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.

cell references are locked arrow right copy the formula without changing cell reference

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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.

choose To absolute option in the dialog box

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.

cell references are converted to absolute references arrow right cell references will not chnaged when copying the formula to other place
Tip: To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.

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.

add dollar sign

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

 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.

 cell references are converted to absolute address arrow right copy the formula to other cell without changing cell reference