Formula | Change all cell references in one go | Shortcut key

Hi Sandeeep,

There is no shortcut key to select all similar references in Excel. However, there are a few workarounds that you can use.

Another workaround is to use the Go To dialog box. To do this, follow these steps:

1. Press Ctrl+G to open the Go To dialog box.
2. In the Reference box, enter the reference that you want to change.
3. Click the Special button.
4. In the Go To Special dialog box, select the Cell references option.
5. Click the OK button.

This will select all of the cells that contain the specified reference. You can then change all of the references at once by typing the new reference in the formula bar and pressing Enter.

Finally, you can also use a VBA macro to change all of the references in a worksheet. To do this, you can use the following code:

Sub ChangeReferences()

    'Declare variables
    Dim ws As Worksheet
    Dim cell As Range
    Dim oldReference As String
    Dim newReference As String

    'Set the worksheet
    Set ws = ActiveSheet

    'Get the old reference
    oldReference = "$AH$1"

    'Get the new reference
    newReference = "Table1[[#Headers],[Name=]]"

    'Loop through all of the cells in the worksheet
    For Each cell In ws.UsedRange

        'If the cell contains the old reference, replace it with the new reference
        If cell.Value Like oldReference Then
            cell.Value = Replace(cell.Value, oldReference, newReference)
        End If

    Next cell

End Sub

To use this macro, simply copy and paste it into a new module in the Visual Basic Editor (VBE). To open the VBE, press Alt+F11.

Once you have pasted the macro into the VBE, you can run it by pressing F5.

You can also check these links as a reference:

• Microsoft Support article on changing cell references: https://www.youtube.com/watch?v=L8dsyjTYI9Q
• Excel training article on changing cell references: https://www.youtube.com/watch?v=BnYTwvofM3M
• MrExcel forum post on changing cell references: https://techcommunity.microsoft.com/t5/excel/make-all-cells-references-in-a-long-formula-absolute/td-p/1022811
• Excel tips and tricks blog post on changing cell references: https://www.youtube.com/watch?v=L8dsyjTYI9Q
  
  

  
  Please click Mark as Best Response & Like if my post helped you to solve your issue.
  This will help others to find the correct solution easily. It also closes the item.

  
  

  If the post was useful in other ways, please consider giving it Like.

  
  

  Kindest regards,

  
  

  Leon Pavesic