Excel creates hyperlinks by default for emails and URL. Sometimes they can be unwanted…. There are a few ways for removing hyperlinks. For example, the right click, clear button, and VBA. In this tutorial, we will learn how to remove hyperlinks.
Figure 1. Example of How to Remove Hyperlinks in Excel
How to Remove links using the Right-Click Option
We can change the settings to turn off the default hyperlink creations. But, most of the time we need to manually remove multiple hyperlinks. We can remove hyperlinks using the right click, clear options or VBA.
The following example uses an employee information data set. Column A, B and C has the employee’s names, IDs and emails. The emails are formatted as hyperlinks by Excel.
Figure 2. The Sample Data Set
To remove these hyperlinks using the right-click options, we need to:
- Go to cell C2. Click on it with our mouse.
- Drag the selection from cells C2 to C7.
- Right click on the selection.
- Select Remove Hyperlinks.
Figure 3. Removing Hyperlinks Using the Right-Click Options
Excel will remove all links from the email addresses.
Figure 4. Example of the Final Output of Removing Hyperlinks Using the Right Click Options
Remove Hyperlinks using the Clear Button
We can remove hyperlinks from the ribbon as well. The clear button deletes links from worksheets. To remove the hyperlinks from the previous example using Clear:
- We need to select cells C2 to C8.
- Next, we go to Home>Clear and click on the drop-down button.
- From there we need to select Remove Hyperlinks.
Figure 5. Removing Hyperlinks using the Clear Button
This will also remove hyperlink as well.
Figure 6. The Final Output Using the Clear Button
How to Remove All Hyperlinks Using VBA Code
We can use a simple VBA code to remove multiple hyperlinks. To remove the hyperlinks from the previous example using VBA,
- We need to press Alt + F11 to open up the VBA editor.
Figure 7. The VBA Editor Interface
- Next, we have to click Insert and select Module.
Figure 8. Example of Inserting a Module
- Copy the code below and paste it to the module.
Sub Remove_Hyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub
Figure 9. The Sample Code
- Lastly, we need to press F5 for the code to run.
Figure 10. Example of the Final output from the VBA Code
This will disable hyperlinks.
Remove Hyperlinks from Entire Workbook
We can use VBA to remove all hyperlinks. This comes in handy when we have a large workbook and need to remove hyperlinks from the entire workbook.
In this example, we use the same employee information database. The worksheets contain information for employees of different departments.
Figure 11. The Data Set for the Entire Company
- To remove the hyperlinks in column C, we need to open the macro editor window following steps 1 and 2 from the previous example. Now, let us copy the code below to the editor.
Sub Remove_Hyperlinks_All()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
ActiveSheet.Hyperlinks.Delete
Next Ws
End Sub
Figure 12. The Sample Code
- Finally, click f5 to run the code.
- We need to save this workbook as a Macro-Enabled Workbook for the VBA to run.
Figure 13. Saving as Excel Macro-Enabled Workbook
This will remove links.
Figure 14. The Final Result from the VBA Code
How to Remove Hyperlinks in Google Sheets
We can remove hyperlinks in Google Sheets with the right-click options. To perform the same example in Google Sheets, we need to:
- Go to cell C2. Drag the selection to cells C2 to C8.
- Right click on the selection.
- Click Unlink to remove links in Google Sheets.
Figure 15. Example of Remove Hyperlinks in Google Sheets using Unlink
This will remove all hyperlinks in Google Sheets.
Figure 16. Example of How to Remove Hyperlinks in Google Sheets
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment