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.