URLs (website addresses) typed in spreadsheets are automatically converted to hyperlinks by Excel. Then, by clicking the links in the cells, you can open the websites in a browser. However, having links in spreadsheets isn’t always a good idea because clicking their cells can open your browser and websites even if you don’t need to.
This is how you may delete all hyperlinks from Excel spreadsheets if you need to insert a list of plain text URLs on a sheet.
Using the Context Menu to Remove the Hyperlink Option
If you’re using a more modern version of Excel, you can use the context menu to remove all hyperlinks from a sheet.
- 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 the Remove Hyperlink option on the context menu. That will convert the Hyperlink to a plain text URL.
- Hold on the Ctrl key while selecting the cells in an Excel spreadsheet to eliminate numerous hyperlinks.
- Then select all of the cells that have links and choose Remove Hyperlink from the drop-down menu.
- Alternatively, you can use the Ctrl + A shortcut to select all of the spreadsheet cells, then right-click and choose Remove Hyperlink to convert all hyperlinks to plain text.
Removing Links From Sheets Without the Context Menu Option
However, the Remove Hyperlink context menu option is not available in all Excel versions. As a result, you won’t be able to choose that option in Excel 2007. Users of Excel 2007 can still use the Paste Special method to remove links from spreadsheets.
- For example, enter ‘www.bing.com’ in cell B3 and ‘1’ in cell C3 of the same spreadsheet. To copy cell C3 to the Clipboard, select it and execute the Ctrl + C shortcut.
- After that, select the cell with the Hyperlink, otherwise B3. To open the window directly below, right-click that cell and pick Paste Special > Paste Special from the context menu.
- To remove the Hyperlink, choose Multiply in that window and press the OK button. Then, in the spreadsheet, deselect cell B3.
Paste URLs Into Spreadsheets as Plain Text
If you need to paste several URLs into a spreadsheet, choose the Keep Text Only option to remove the hyperlink formatting.
- Copy the URL www.google.com by selecting the anchor text of the Hyperlink and pressing Ctrl + C.
- Then, in your Excel spreadsheet, right-click cell D3 to bring up the context menu seen below.
- A clipboard icon can be found under Paste Options. That’s the Keep Text Only option, which allows you to copy the URL into the cell without including any links.
- Alternatively, pick the Keep Text Only option by clicking the little arrow on the Excel toolbar’s Paste button.
Set up a Macro That Removes Hyperlinks
A macro is a pre-recorded sequence of possibilities. This Tech Junkie article (and accompanying video) explains how to record macros in Windows. You can record macros with the entire Excel application’s macro-recording function, but you can also manually enter the Visual Basic code to set up macros. For example, why not create a macro that deletes all hyperlinks from an Excel spreadsheet?
- To access the VB editor in Excel, press the Alt + F11 hotkey.
- Then, on the VBAProject panel, double-click ThisWorkbook.
- Using the Ctrl + C and Ctrl + V hotkeys, copy and paste the following code into the VB code window: ‘In Excel, there is a code that may be used to remove hyperlinks.
- Sub RemoveAllHyperlinks()
- End Sub
- To run the macro, choose the spreadsheet from which you want to remove hyperlinks, open a Macro window with the Alt + F8 hotkey, and select ThisWorkbook. Then press the Run button after removing AllHyperlinks from the Macro window.
Switch off Automatic Hyperlinks
Excel converts URLs to links automatically. You can, however, set the software to keep all URLs entered as plain text. To do so, go to the File tab and click Options to bring up the Options window.
On the left side of the window, select Proofing and then the AutoCorrect Options button. This will bring up the window depicted in the image below. On that window, click the AutoFormat As You Type tab. On that tab, deselect the Internet and network pathways with hyperlinks option. Finally, close the Excel Options window by pressing the OK button. URLs inserted in spreadsheet cells will now only be displayed as text.
Keep in mind that turning off automatic hyperlinks might save you a lot of time and effort.
Managing Hyperlinks in Excel
There are a couple of options for removing all hyperlinks from an Excel worksheet. Note that you may also remove hyperlinks by going to the cell context menu, selecting Edit Hyperlink, and pressing the Remove Link button.
Do you find yourself needing to use hyperlinks in plain text regularly? Is there a better way to get rid of hyperlinks? Let us know what you think in the comments area below.
Visit: Editorial Times