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 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
How to get or reference cell from another worksheet in Excel?
In some cases, you may need to get or reference a specific cell value from other worksheets, and make these linked cells dynamic with the changes of referenced cell. In this article, we will introduce two methods for you to achieve it.
How to highlight all cells referenced by a formula in Excel?
This article is talking about highlighting all cells which was referenced by a formula in Excel. Please follow the below two methods to get it done.
How to lock all cell references in formulas at once in Excel?
Normally, you need to lock all cell references to make them absolute in order to prevent cell references changed with formulas. This article shows you methods to achieve it.
How to remove all formula references but keep values in cells in Excel?
For removing all formula references but keep values in cells, this article will show you methods to achieve it.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.