How Can I Replace Range Names with Cell References in Excel?

When you’re working with Excel, using range names can make your formulas easier to read. But sometimes, you might want to switch back to using cell references instead of those range names. If you’re looking for a quick and easy way to replace range names with cell references in Excel. In this tutorial, I will be sharing the VBA Codes to Replace Range Names with Absolute & Relative cell references.

What is an Absolute Reference:

Absolute Reference is a type of reference in Excel that stays locked in one place. It doesn’t change, even if you try to copy or move the formula to another cell. In simple words, it’s a fixed location in your worksheet.

Example reference value:

=$A$1 // Single Absolute Reference Cell.

$A$1:$B$10 // An absolute reference of a range of cells.

What is a Relative Reference:

Relative Reference in Excel is a cell reference that automatically adjusts or modify when you copy the formula to another cell.

Example reference value:

A1 // Relative reference of the single cell.

A1:B10 // Relative reference of a range of cells.

Let me consider an example worksheet, where the column A contains the Price of the product and column B contains the Discount value. In the column C the final discounted price will be displayed.

• I’m going to name the range of cell values from A2 to A8 as “Price” and the cell B2 as the “Discount“.

• Now in the column C, I’m going to use the formula as =Price*Discount to calculate the final price.

Using the VBA Codes to Replace the Range Names with Cell References:

To do this, you need to do the following steps,

• First, you need to choose the range of cells that you want to replace their names with cell references.
• Use the keyboard shortcut ALT + F11 to launch the VBA editor.
• Then from the menu bar, you need to choose Insert -> Module.
• Copy and paste the below vba code.

Using the VBA Code to Replace Range Names with Absolute Reference:

Sub AbsoluteNamesWithRelativeRefs()
'Replace range names with Absolute reference
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "By Excel24x7.com"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
        End If
    Next
Next
End Sub

• Use the shortcut F5, to execute the command. Now the range name will be replaced with cell reference as shown as below.

Using the VBA Code to Replace Range Names with Relative Reference:

• Copy the below vba code of using relative reference.

Sub ReplaceNamesWithRelativeRefs()
'VBA Code to Replace Range Names with Relative Reference
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "By Excel24x7.com"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Final Price", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(xName.RefersTo, "=", ""))
        End If
    Next
Next
End Sub

• Use the shortcut F5, to execute the command. Now the range name will be replaced with cell reference as shown as below.

This is how all of the selected formula range names have been replaced.

That’s it.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

Recently Published Tutorials:

• 2 Methods: Adding Leading Zeros (0) in Excel using Functions!
• Excel: Highlight Max/Min Values in Rows & Columns!
• How Can You Assign a Value/Category based on Number Range in Excel?
• How Do I Find Cell Contains Maximum or Minimum Values in Excel?
• How Can I Delete All AutoShapes, Objects in Excel?