How to Quickly Remove Hyperlinks from a Worksheet in Excel

Watch Video – How to Remove Hyperlinks in Excel

I often find it quite frustrating when Excel automatically creates a hyperlink as soon as I insert an email id or a URL in a cell and hit enter.

Now, when I try to select a cell (or double-click on it to get into the edit mode), it automatically opens the link in a new browser window.

Arghhh!

In such cases, there is a need to remove these hyperlinks from the worksheet.

In this tutorial, I will show you 2 methods to remove existing hyperlinks in a worksheet, and an awesome technique to prevent Excel from creating hyperlinks on its own.

Remove Hyperlinks with 2 Clicks

Suppose you have a dataset where Excel has automatically created hyperlinks in URLs or email ids.

Here are the steps to remove hyperlinks from the selected cells:

  1. Select the data from which you want to remove hyperlinks.
  2. Right-click on any of the selected cell.
  3. Click on the ‘Remove Hyperlink’ option.Select Remove Hyperlinks option from the right-click menu

The above steps would instantly remove hyperlinks from the selected cells.

In case you want to remove hyperlinks from the entire worksheet, select all the cells and then follow the above steps.

Remove Hyperlinks with VBA

While the above method of removing hyperlinks with 2 clicks is quite easy, you can also use VBA to bring this down to a single click.

But before it becomes a one-click functionality, there is some pre-work that you need to do (as described below).

The below VBA macro code instantly removes all the hyperlinks from the active worksheet.

Sub RemoveAllHyperlinks()
'Code by Sumit Bansal @ TrumpExcel.com
ActiveSheet.Hyperlinks.Delete
End Sub

Here are the steps to put the VBA code in the backend and enable one-click hyperlink removal:

  1. Copy the above VBA code
  2. Go to the Developer tab.Developer tab in the ribbon
  3. Click on Visual Basic.Remove Hyperlinks in Excel - click on visual basic
  4. In the VB Editor, right-click on any of the workbook objects.
  5. Go to Insert and click on Module.Remove Hyperlinks in Excel - insert module
  6. In the module, paste the above VBA code.Remove All Hyperlinks - Paste VBA code in module
  7. Close the VB Editor.

The above steps ensure that the VBA code is in the backend and would remove hyperlinks from the entire sheet when it’s run.

Now to further simplify this process, you can add the macro to the Quick Access Toolbar (QAT). This will allow you to remove hyperlinks from the entire worksheet with a single click.

Remove Hyperlinks in Excel - macro in QAT

Here are the steps to add the VBA macro to the QAT:

  1. Click on the Customize Quick Access Toolbar icon.Remove Hyperlinks from a Worksheet in Excel - Add to QAT
  2. Select ‘More Commands’.More Commands to add macro to QAT
  3. In the Excel Options dialogue box, in the ‘Choose command from’ dialog box, select ‘Macros’.Remove Hyperlinks from a Worksheet in Excel - Macros in Excel Options
  4. Click on the Macro that you want to add to the QAT.Select the macro that you want to add to QAT
  5. Click on the Add button.
  6. Click OK.

The above steps would add the macro to the QAT.

Now to remove all the hyperlinks from the worksheet, all you need to do is click on the macro in the QAT.

If you don’t want to remove all the hyperlinks in a worksheet, but only the ones in the selected range, use the below VBA code:

Sub RemoveHyperlinksfromSelection()
 'Code by Sumit Bansal @ TrumpExcel.com
 Selection.Hyperlinks.Delete
 End Sub

If you often have the need to remove hyperlinks from a worksheet (or a selected range), it’s a good idea to save this macro in your personal macro workbook.

Here is a detailed guide on how to save a macro in the personal macro workbook.  Once saved in the personal macro workbook, it will be available in all the workbooks on your system.

Prevent Excel from Creating Hyperlinks Automatically

So far we have been treating the symptoms.

Now let’s see how to target the root cause of the issue – URLs/Emails automatically getting converted into hyperlinks.

The reason this happens as there is a setting in Excel that automatically converts ‘Internet and network paths’ into hyperlinks.

Here are the steps to disable this setting in Excel:

  1. Go to File.
  2. Click on Options.Remove Hyperlinks in Excel - Click on Options
  3. In the Excel Options dialog box, click on ‘Proofing’ in the left pane.Proofing option in the left pane
  4. Click on the AutoCorrect Options button.Remove All Hyperlinks - Autocorrect Option
  5. In the AutoCorrect dialog box, select the ‘AutoFormat As You Type’ tab.Select Autoformat as you type tab
  6. Uncheck the option – ‘Internet and network paths with hyperlinks’Uncheck Internet and Network Paths with hyperlinks
  7. Click OK.
  8. Close the Excel Options dialog box.

If you’ve completed the following steps, Excel would not automatically turn URLs, email address, and network paths into hyperlinks.

Note that this change is applied to the entire Excel application, and would be applied to all the workbooks that you work with.

You May Also Like the Following Excel Tutorials:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)