How do you select a cell containing a hyperlink without activating the link

June 12, 2019/ Chris Newman

By default Excel will convert text to a hyperlink if it determines your data is in a URL or email address format. This is typically not a wanted behavior inside spreadsheets and in this article you will learn a couple ways to prevent this type of auto-correction from occurring within Excel.

Below is an example of what you would see if you entered in text for search engine websites and email addresses.

The problem with these hyperlinks (and most likely why you are reading this article) is if you just want the data, it can be a pain to work around the links while making manipulations. In order to select a cell with a hyperlink, you need to use your right mouse button instead of your left.

So lets dive in and see how we can prevent this from occurring in the first place!

Excel-Level Change

First, let’s look at a setting we can tweak to change this auto-correction task across the entire Excel application. This means this change will impact every workbook on your machine.

In Excel >> File Tab >> Options Menu >>

1. Select Proofing from the side-pane

2. Click the AutoCorrect Options… button

3. Navigate to the AutoFormat As You Type tab

4. Uncheck Internet and network paths with hyperlinks under Replace as you type

5. Click OK to confirm the change

Worksheet-Level Change

Now let’s take a look at how we can use VBA to prevent auto-hyperlinking at the worksheet level. The following code will use Excel Events to trigger the VBA. We will utilize the Change event to run this bit of code every time a cell’s value changes on the target worksheet. If you would like to learn more about triggering VBA code when something on the worksheet changes, you can read my more detailed blog post: How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change.

Preventing New Hyperlinks With VBA

The following snippet will need to be pasted inside your desired Sheet Object using the Visual Basic Editor. It will look at only the range of cells that were last changed and remove any hyperlinks located within that range.

Private Sub Worksheet_Change(ByVal Target As Range)
'PURPOSE: Prevent any new hyperlinks from auto-generating
'NOTE: Manually adding hyperlinks can still be done

If Target.Hyperlinks.Count <> 0 Then Target.Range.ClearHyperlinks

End Sub

Preventing Any Hyperlinks With VBA

If you absolutely don’t want to have any hyperlinks on your sheet, we can use a more encompassing piece of code. The following VBA subroutine will need to be pasted inside your desired Sheet Object using the Visual Basic Editor. This code will remove any hyperlinks on the spreadsheet when a change is made.

Private Sub Worksheet_Change(ByVal Target As Range)
'PURPOSE: Prevent any hyperlinks ever occuring within this spreadsheet

'Remove All Hyperlinks From Cells In This Sheet
  Me.Cells.ClearHyperlinks

End Sub

Any Other Solutions?

Have your discovered any other methods we can take inside Excel to prevent hyperlinks? Let me know in the comments section below.

About The Author

Hey there! I’m Chris and I run TheSpreadsheetGuru website in my spare time. By day, I’m actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the “real world” and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company.

Through my years in the corporate world, I’ve been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins, from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If you’d like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I’ve learned over the years, you can sign up for my free newsletters. I hope I was able to provide you with some value today and I hope to see you back here soon!

- Chris
Founder, TheSpreadsheetGuru.com