Hyperlinks are a great tool as they allow you to speed up and simplify navigation within a file. Sometimes hyperlinks can be frustrating. See how to remove some of those frustrations below.

Selecting a cell with a hyperlink

To select a cell that contains a hyperlink you can use the keyboard arrow keys and you can also use the mouse. When clicking on the hyperlink cell with the mouse, just click and hold and you will select the cell, rather than following the hyperlink.

Hyperlinks when you want them

If you import data into Excel and the hyperlinks are not working then how you activate them depends on how many there are.

When there are only a few, then for each cell press F2 and press Enter. In a list you just keep pressing F2 and Enter in sequence to work your way down the list.

To handle many entries you can use the macro below. It works for most emails, web addresses and folder locations. Select the range involved and run the macro.

Sub Create_Hyperlinks()
' Create hyperlinks from cell contents in a selected range
' Works for email, drive letters eg c:\temp and
' www. addresses and websites without www
 
Dim c
Dim strAddress As String
 
    For Each c In Selection
 
        If c.Value <> "" Then
 
            If InStr(c, "@") > 0 Then
 
                strAddress = "mailto:" & c.Value
 
            ElseIf InStr(c, ":\") > 0 Then
 
                strAddress = c.Value
 
            Else
 
                strAddress = "http://" & c.Value
 
            End If
 
            c.Hyperlinks.Add c, strAddress, c.Value
 
        End If
    Next c
 
End Sub

If you are unsure of how to use macros, click here to see a post on using macros.

Hyperlinks when you don’t want them

This is common if you are typing entries that contain the @ symbol. Excel assumes they are email addresses. To reverse the hyperlink once it has been entered you can click Undo or press Ctrl + z. This removes the hyperlink and leaves the cell contents unchanged.

When typing the entry you can precede the entry with the apostrophe ‘ this will stop it being considered a hyperlink.

There is also a setting you can change to stop them being created in the first place – see section below.

If you have numerous cells that are already hyperlinks and you want the hyperlinks removed, select the range, right click the range and choose Remove Hyperlinks at the bottom of the menu. This works in Excel 2010 and later versions.

For previous versions you can use one the macros listed below. The first works on a selected range and the second removes all hyperlinks from a sheet.

Sub Remove_Hyperlinks()
'removes all hyperlinks from the selected range
 
    Selection.Hyperlinks.Delete
 
End Sub

Sub Remove_ALL_Hyperlinks()
'removes all hyperlinks from a sheet
 
    Cells.Hyperlinks.Delete
 
End Sub

Turning off Automatic Hyperlinks

There is a setting that controls the hyperlink creation, but it is hidden away in the Excel Options windows. You can turn it off when you don’t want Excel to create hyperlinks. In the File tab, in Excel Options, Proofing option on left, AutoCorrect Options button and the AutoFormat As You Type tab has the setting – see image below.

Click here to see how you can run a Macro off a Quick Access Toolbar icon.