Hyperlinks In Excel– Add Remove Links Formula – Get VBA Code

How To Add Hyperlinks in Excel Using Formula?

Higher version of MS Excel like 2007 or above have built in function to add links.

1. Select a cell to insert a hyperlink.
2. Enter “=HyperLink” & enter value for below parameters.
3. Type website URL address or destination file path as link.
4. Type any text to display as friendly name in Excel.
5. Example: =HYPERLINK (“https://officetricks.com”, “Refer Officetricks for Excel Tips”)
6. Press Enter.

If not a website url, You can also enter complete path for a file name in the first parameter as hyperlink. Once this is done, when any use click this cell, the corresponding file or website will be opened in your default browser. Note: Hyperlinks added using this formula cannot be read using the below VBA codes.

How To Add Hyperlinks in Excel using VBA?

The below code has sample VBA to add website Url & also file path as hyperlink. Open a new workbook, press Alt+F11 & copy paste this below code. Once you press F5, it will add hyperlinks in active sheet.

Sub Excel_VBA_Insert_Hyperlink()

    'VBA Code to Add Website URL as Hyperlink in Excel
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://officetricks.com/", TextToDisplay:="Refer Officetricks.com for Excel Tips"
        
    'VBA Code to Add Website URL as Hyperlink in Excel
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "D:\FolderPath\Filename.jpeg", TextToDisplay:="Filepath To Open"
        
End Sub

Above code will add hyperlinks twice in the same cell. To avoid this, enter a different location in second command. For example: instead of 'Activesheet.Hyperlinks' use 'Thisworkbook.Sheets(1).Cells(1,2).Hyperlinks'

How To Read All Hyperlinks in Excel using VBA Macro?

Use this VBA code to read all the Hyperlinks in Your Excel sheet.

Sub Excel_Read_All_Hyperlinks_VBA()
    Dim idx As Integer
    
    'Read All Hyperlinks in Worksheet
    For idx = 1 To ActiveSheet.Hyperlinks.Count
        MsgBox ActiveSheet.Hyperlinks(idx).Address
    Next
End Sub

How to Add hyperlink in Excel Manually?

To insert a hyperlink in Excel follow these steps.

1. Right click cell.
2. Choose "Hyperlink" in menu.
3. Type value for "Text to display" field.
4. Enter "Address" or choose a path from Existing File or Web page.
5. Click 'Ok'.

This will add a hyperlink in the chosen cell. This cell will display the words you typed in the field "Text to field" with a underline and blue color. Once a user clicks on this cell or link, Excel will open the hyperlink in the default browser or in the desktop. If the file or field entered is wrong, you will get an error message.

How To Delete Hyperlinks in Excel with VBA?

To manually remove a hyperlink from Excel, right click on the corresponding cell in the worksheet. Then choose 'Remove hyperlink' option. To remove it using the VBA code, use 'Delete' instead of '.Add' or '.Address' in the above commands. Once you press F5, the VBA code will remove the hyperlink. Also, if you want to edit value of a hyperlink, then choose 'Edit Hyperlink' option & change the values.