Skip to main content

How to change/convert absolute reference to relative reference in Excel?

Author: Tech Support Last Modified: 2024-07-30

Normally there are several types of cell references you can use in a formula, and each type of cell references can help you achieve different purposes in the formula. You can have the following types of cell references, such as absolute cell reference, relative cell reference, relative row reference absolute column reference and absolute row reference and relative column reference. But sometimes you may need to change the using of the formula purpose by changing the cell references in the formula. The following tricky methods will you tell how to change absolute reference to relative in Excel.

Change absolute reference to relative reference with F4 key

Change absolute reference to relative reference with VBA code

Quickly change absolute reference to relative reference with Kutools for Excel

Absolute Reference

a screenshot of the original data

arrow

Relative Reference

a screenshot of the result


Change absolute reference to relative reference with F4 key

With the shortcut key F4, we can easily toggle the absolute reference to relative reference, please do as the following steps:

Put the cursor behind $A$1, then press F4 three times, it will become A$1, $A1, A1 successively. See screenshot:

a screenshot of changin absolute reference to relative with F4 key 1 a screenshot of arrow 2 a screenshot of changin absolute reference to relative with F4 key 2 a screenshot of arrow 3 a screenshot of changin absolute reference to relative with F4 key 3 a screenshot of arrow 4 a screenshot of changin absolute reference to relative with F4 key 4

According to this step, put the cursor behind $B$1 to get B1.

And this cell reference will become a relative reference from absolute reference.

If there are multiple formulas’ cell references need to be changed, this way will be tedious and time-consuming.


Change absolute reference to relative reference with VBA code

With VBA code, you can quickly change a range of formulae cell references from absolute references to relative references at a time.

1. Select the range that you want to change.

2. Click Developer > Visual Basic or you can press Alt + F11, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:
VBA: Convert absolute to relative reference.

Sub ConverFormulaReferences()
'Updateby20140603
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
Dim xIndex As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Absolute = 1" & Chr(13) _
& "Row absolute = 2" & Chr(13) _
& "Column absolute = 3" & Chr(13) _
& "Relative = 4", xTitleId, 1, Type:=1)
For Each Rng In WorkRng
    Rng.Formula = Application.ConvertFormula(Rng.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex)
Next
End Sub

3. Then click a screenshot of VBA run button button to run the code, and a prompt box will pop out  for selecting a range to convert, then click OK and another dialog displays to prompt you which type you want to use. Then you can choose the right type you need. For this example, I will insert 4. See screenshots:

a screenshot of changing absolute reference to relative with VBA select range
a screenshot of arrow
a screenshot of changing absolute reference to relative with VBA specify reference type

4. Then click OK. All of the absolute references in selection have been changed to relative references in the formula.

This is a multifunction VBA code, with this code; you can also change relative to absolute reference, change absolute row or change absolute column.


Quickly change absolute reference to relative reference with Kutools for Excel

Kutools for Excel: with more than 100 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

Kutools for Excel let you change absolute to relative reference or vice versa quickly and easily. Please do as follows:

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

Step 2. Click Kutools > More in the Formula group > Convert Refers. It will display Convert Formula References dialog box.

Step 3. Check To relative and click Ok or Apply in the Convert Formula Reference dialog. It will change the absolute reference to relative reference.

a screenshot of changing absolute reference to relative with Kutools for Excel

If you would like to change the cell references to column absolute or row absolute, please check To column absolute option or To row absolute option.

For more detailed information about Convert Reference, please visit Convert Reference feature description.


Relative article

Change relative reference to absolute reference


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!