How To Remove All Hyperlinks in an Excel Sheet
Excel automatically converts URLs (website addresses) entered in spreadsheets to hyperlinks. Then you can open the websites in a browser by clicking their links in the cells. However, it isn’t always ideal to have links in spreadsheets as selecting their cells can open your browser and the websites even when you don’t need to open the pages. If you need to enter a list of plain text URLs in a sheet, this is how you can remove all hyperlinks from Excel spreadsheets.
Select the Remove Hyperlink Option
If you’re using a more recent version of Excel, you can remove all the hyperlinks from a sheet with a context menu option. As an example, open a blank Excel spreadsheet and enter ‘www.google.com’ in cell B2. Then you can right-click that cell and select a Remove Hyperlink option on the context menu. That will convert the hyperlink to a plain text URL.
To remove multiple hyperlinks from an Excel spreadsheet, hold the Ctrl key and select the cells. Then you can select all the cells that include the links and click the Remove Hyperlink option. Alternatively, press the Ctrl + A hotkey to select all the spreadsheet cells. Then you can right-click and select Remove Hyperlink to convert all the links to plain text.
Removing Links From Sheets Without the Context Menu Option
However, not all Excel versions include the Remove Hyperlink context menu option. As such, you can’t select that option in Excel 2007. Nevertheless, 2007 users can still remove links from spreadsheets with a Paste Special trick.
For an example, enter ‘www.bing.com’ in cell B3. Then input ‘1’ in cell C3 of the same spreadsheet. Select cell C3 and press the Ctrl + C hotkey to copy it to the Clipboard.
Next, select the cell that includes the hyperlink, otherwise B3. You can right-click that cell and select Paste Special > Paste Special from the context menu to open the window shown directly below. Select Multiply on that window, and press the OK button to remove the hyperlink. Then deselect cell B3 on the spreadsheet.
Paste URLs Into Spreadsheets as Plain Text
If you need to paste lots of URLs in a spreadsheet, you can remove their hyperlink formatting by selecting a Keep Text Only option. As an example, copy the URL www.google.com by selecting the hyperlink’s anchor text and pressing Ctrl + C. Then right-click cell D3 in your Excel spreadsheet to open the context menu in the shot directly below.
Under Paste Options there’s a clipboard icon. That’s the Keep Text Only button which you can select to copy the URL in the cell without any link. Alternatively, click the small arrow on the Excel toolbar’s Paste button to select the Keep Text Only option.
Set up a Macro That Removes Hyperlinks
Macros are a recorded sequence of selected options. This Tech Junkie post (and its video) tells you how to record macros in Windows. The full Excel application includes a macro-recording tool to record macros with, but you can also set up macros by manually entering the Visual Basic code. So why not set up a macro that removes all hyperlinks from an Excel sheet?
Press the Alt + 11 hotkey to open the VB editor in Excel. Then you can double-click ThisWorkbook on the VBAProject panel. Copy and paste the code below into the VB code window with the Ctrl + C and Ctrl + V hotkeys.
‘ Code to Remove Hyperlinks in Excel
To run the macro, select the spreadsheet you need to remove hyperlinks from. Press the Alt + F8 hotkey to open a Macro window. Select ThisWorkbook.RemoveAllHyperlinks from the Macro window and press the Run button.
Switch off Automatic Hyperlinks
Excel automatically converts URLs to links. However, you can configure the software so that all URLs entered remain as plain text. To do that, select the File tab and click Options to open the window directly below.
Select Proofing on the left of that window and press the AutoCorrect Options button. That will open the window shown in the snapshot below. Select the AutoFormat As You Type tab on that window. Deselect the Internet and network paths with hyperlinks option on that tab. Press the OK button and close the Excel Options window. Now URLs entered in spreadsheet cells will remain text only.
So there are a few ways you can remove all the hyperlinks in an Excel spreadsheet. Note that you can also remove hyperlinks by selecting Edit Hyperlink from the cell context menu and pressing the Remove Link button.