Removing hyperlinks is no difficult feat and with a few simple clicks, you can restore your hyperlinks to simple text. There are a handful of different ways to do this which we will walk you through later in this tutorial.
Hyperlinks can be created manually but Excel has a brain of its own when it comes to URLs and email addresses it automatically turns these into hyperlinks. Hyperlinks will lead the user to the webpage by clicking on the entered link.
Sounds like a one-click convenience, right? Since you're reading this article on eliminating hyperlinks, we probably don't need to sing their praises but considering hyperlinks are quite helpful, why would you want to remove them?
Problems with Hyperlinks
Hyperlinks might save you time, redirecting you to the link in your browser right when you click it, but this might also be the problem. The most common point of frustration regarding hyperlinks is the redirection when you do not want to be redirected. So when you try to click the cell containing a hyperlink to edit the cell content it opens that specific link in your default browser.
Though it's not impossible to select a cell with a hyperlink – you can always double-click or navigate via the arrow keys on the keyboard.
What is putting you off hyperlinks could be – not being able to single-click the cell, the time wastage of being redirected off your worksheet, or maybe the blue font is an eyesore? It might not just be automatic hyperlinking that you want to get rid of. It could be a created internal hyperlink, following data in the same workbook.
Excel will pick up any URL, email address or file path entered in the sheet and turn it into a hyperlink. While Excel wouldn't be wrong to assume them as links, here's our guide to removing hyperlinks when you don't want Excel to behave in this way.
Removing Hyperlinks
Now that we're all worked up over hyperlinks, here are some super simple ways of removing them from your sheets with super simple steps, leaving plain text behind. The format of the cell from which the hyperlink is removed will be set to the default format of a regular cell; black font color, default font and font size, no borders, color fill, etc.; just plain text.
For our example, we have used color fill and different font weights, in the hyperlinked cells and borders on our table to show you the formatting changes that can occur after the hyperlinks are removed.
Method #1 – Remove Hyperlinks from the Right-Click Context Menu
Below are the steps for removing hyperlinks using the right-click context menu:
- Select the cells, row, column, or entire sheet from where the hyperlinks need to be removed. For our example, we will select the range B2:C7.
- Right-click on any one of the selected cells to open the context menu.
- Click Remove Hyperlinks option from the context menu.
And they're gone! These steps will remove the hyperlinks from the selected data, restoring the text back to the default format. This is what removed hyperlinks look like:
Note: It should be noted that Remove Hyperlinks option is not displayed all the time in the right-click context menu. It only appears on the context menu when the selected cells contain at least one hyperlink.
Method #2 – Remove Hyperlinks from the Ribbon Menu
With the same level of zero difficulty, here are the steps for removing hyperlinks from the ribbon menu:
- Select the cells, row, column, or entire sheet from where the hyperlinks need to be removed. We will select the range B2:C7 since that's where all our hyperlinks are.
- From the Home tab, in the Editing section, there's a cute little eraser with the title Clear. Click the Clear button.
- From the drop-down menu of the Clear button, click Remove Hyperlinks.
And they're gone again! The text has been restored to the default format.
While removing the hyperlinks using the Clear button, you may have noticed a Clear Hyperlinks option just above it. Right ahead, we'll tell you what that's all about.
Method #3 – Remove Hyperlinks Keeping the Formatting Intact
Maybe we were wrong, maybe you're not finding the blue color of hyperlinks an eyesore. You may want your text to have the look of a hyperlink but not the quality of being a hyperlink.
Here we have the steps of clearing hyperlinks i.e. removing hyperlinks without changing the associated formatting (text color, background color, font size, font weight, borders, etc):
- Select the cells, row, column, or entire sheet from where the hyperlinks need to be removed. For our example, we will again select the range B2:C7.
- Under the Home tab, in the Editing section, we are back to the little eraser Clear Click the Clear button.
- From the drop-down menu, select Clear Hyperlinks.
The Clear Hyperlinks option is a softer form of the Remove hyperlinks option. By removing hyperlinks, the text goes back to the default formatting with the hyperlink gone (as demonstrated in the segments above). By clearing hyperlinks, the hyperlink will be gone but the associated formatting (text color, background color, font size, font weight, borders, etc) is retained.
You can see above that with Clear Hyperlinks, the format is maintained. After clearing the hyperlinks, another cute little eraser button appears on the bottom right corner of the cells.
This button gives you quick access to the Clear Hyperlinks Option button and also has the option of clearing the formats from the cells. If you change your mind later, you can access this option and remove the formats too.
Method 4 – Remove Hyperlinks with VBA
Hyperlinks can also be removed using a one-off VBA code. VBA codes can remove hyperlinks from cells, the active worksheet, or the entire workbook. The steps for removing hyperlinks from cells, the sheet, or the workbook are pretty much the same, just the codes to be used are different. Let's have a look:
Remove Hyperlinks by Cell Selection
These are the steps for removing hyperlinks from selected cells:
- Select the range of cells you want to remove the hyperlinks from. We have selected B2:C7.
- Press Alt + F11 to open the VBA editor.
- Navigate to the Insert tab and click the Module option. This will open the module window.
- In the module window that opens, paste the following code:
Sub RemoveHyperlinksInSelection
Selection.Hyperlinks.Delete
End Sub
- Press F5 to execute the code.
You will notice that the hyperlinks in the selection have been removed now.
Remove Hyperlinks from the Active Sheet
Follow the steps below to remove all the hyperlinks from the active worksheet:
- On the worksheet from which you want to remove all the hyperlinks, press Alt + F11 to open the VBA editor.
- Navigate to the Insert tab and click the Module option. This will open the module window.
- In the module window that opens, paste the following code:
Sub RemoveHyperlinksInActiveSheet()
ActiveSheet.UsedRange.Hyperlinks.Delete
End Sub
- Press F5 to run the code.
The steps above will remove all the hyperlinks from the active worksheet.
Remove Hyperlinks from the workbook
Follow these steps to remove all the hyperlinks from the entire workbook:
- Press Alt + F11 to open the VBA editor.
- Navigate to the Insert tab and click the Module option. This will open the module window.
- In the module window that opens, paste the following code:
Sub RemoveHyperlinksInEntireWorkbook()
Dim wrkSht As Worksheet
For Each wrkSht In ActiveWorkbook.Worksheets
wrkSht.UsedRange.Hyperlinks.Delete
Next wrkSht
End Sub
- Press F5 to run the code and this will remove all the hyperlinks from the workbook.
Prevent Excel from Creating Hyperlinks Automatically
Another way to understand why Excel creates hyperlinks of URLs, email addresses, and network paths is that it autoformats internet and network paths. If we can disable this autoformatting, we should be able to avoid automatic hyperlinking. We only have to disable this option from Excel options. Here's how:
- Select the File tab and navigate to options from the left panel.
- This will open up the Excel Options window. From the Excel Options, window select Proofing from the side panel.
- Click the AutoCorrect Options… button.
- On the Autocorrect window, navigate to the AutoFormat As You Type tab. Uncheck the Internet and network paths with hyperlinks option.
- Click OK and you are done.
Following these steps would prevent Excel from creating automatic hyperlinks of URLs, email addresses, and network paths. Being a broad Excel file option, this change applies to all Excel files and is not limited to the current workbook.
There we have it. Wasn't that easy? The hyperlinks went down without a fight. They probably knew they had us hot under the collar. Next time you're faced with the menace, use these simple methods to not get hyper over hyperlinks. We have lots more to polish your Excel skills. So fluff up your collar, keep learning and flaunting!
About Mehwish Javaid