How to replace relative references of multiple cells in Excel with absolute references

Demand scenarios

In the Excel table, the latest releases are updated every week and inserted into a fixed position. Therefore, you need to ensure that after inserting a new row, the formulas in the table use absolute references.

Now, there are multiple cells in the table that use relative references, and they all need to be replaced with absolute references.

Solution

1. Modify one by one

Select the cell to be adjusted, move the cursor to the reference position, and press F4 on the keyboard to adjust the reference method.

2. Overall replacement of VBA macros

• Press Alt + F11 to open the VBA editor.
• In the VBA editor, insert a new module (right-click on any item and select “Insert” -> “Module”).

• Paste the following VBA code in the new module:

Relative reference → absolute reference (always reference the data of the cell)

Sub ConvertToAbsoluteReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

• Close the VBA editor.
• Select multiple cells or ranges of cells that contain formulas.
• Run the macro you just created: You can press Alt + F8, select “ConvertToAbsoluteReferences”, and then click “Run”.

This VBA macro will traverse the selected cells, convert the cells containing formulas, and change the relative index in the formula to an absolute index.

Supplement: VBA code in other situations

Relative reference replacement

Relative reference → row absolute reference (always reference the data of one row)

Sub ConvertToRowLockedReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRowAbsolute)
        End If
    Next cell
End Sub

Relative reference → Column absolute reference (always reference the data of one column)

Sub ConvertToColumnLockedReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlColumnAbsolute)
        End If
    Next cell
End Sub

Relative reference → absolute reference (always reference the data of the cell)

Sub ConvertToAbsoluteReferences()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

Row absolute reference replacement

Row absolute reference → relative reference

Sub ConvertRowAbsoluteToRelative()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRelative)
        End If
    Next cell
End Sub

row absolute reference → column absolute reference

Sub ConvertRowAbsoluteToColumnAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsoluteRowRelativeColumn)
        End If
    Next cell
End Sub

row absolute reference → absolute reference

Sub ConvertRowAbsoluteToAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

Column absolute reference replacement

Column absolute reference → relative reference

Sub ConvertColumnAbsoluteToRelative()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRelativeColumnAbsoluteRow)
        End If
    Next cell
End Sub

column absolute reference → row absolute reference

Sub ConvertColumnAbsoluteToRowAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRowAbsoluteColumnRelative)
        End If
    Next cell
End Sub

Column absolute reference → absolute reference

Sub ConvertColumnAbsoluteToAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlAbsolute)
        End If
    Next cell
End Sub

Absolute reference replacement

absolute reference → relative reference

Sub ConvertAbsoluteToRelative()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRelativeRowRelativeColumn)
        End If
    Next cell
End Sub

absolute reference → line absolute reference

Sub ConvertAbsoluteToRowAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlRowAbsolute)
        End If
    Next cell
End Sub

absolute reference →column absolute reference

Sub ConvertAbsoluteToColumnAbsolute()
    Dim cell As Range
    Dim formula As String
    
    For Each cell In Selection
        If cell.HasFormula Then
            formula = cell.Formula
            cell.Formula = Application.ConvertFormula(formula, xlA1, xlA1, xlColumnAbsolute)
        End If
    Next cell
End Sub