An Excel document with too many Hyperlinks in it can be messy, especially when you don't need them. Hyperlinks are a good way to point someone to a website where they can get more information on a subject. But when there are too many of them, you may want to remove some of them. In this case, it may become necessary to remove some unnecessary hyperlinks just to make it easier to read the document.
But sometimes the process of just removing hyperlinks in Excel can be very difficult, especially when you don't know how to do it. In this article, we are going to be looking at how to remove hyperlinks in Excel in several different ways.
1. Remove Hyperlinks in Excel Using a Macro
One of the ways you can remove all hyperlinks on any Excel document is to write a simple macro. Here's how to do that;
Step 1: With the Excel file in question opened, hit "ALT + F11" on your keyboard to open the Visual Basic Editor.
Step 2: On the left of the Visual Basic Editor, click on "ThisWorkbook".
Step 3: Copy and paste the VBA code generated into the code window and then close the VBA window.
Step 4: Now select the Excel file you want to remove hyperlinks from and then hit "ALT + F8" to open the Macro dialog box.
Step 5: Click on "ThisWorkbook.RemoveAlllHyperlinks" and then tap on "Run".
Please note that this method will remove all hyperlinks from the document permanently and you will not be able to use the undo command to put them back on the document.
2. Remove Hyperlinks in Excel with VBA
You can also use VBA to instantly remove all hyperlinks from the Excel document. The following is a simple step by step guide to show you how;
Step 1: Begin by copying the following VBA code;
'Code by Sumit Bansal @ trumpexcel.com
Step 2: Open Excel and then click on the "Developer's" tab.
Step 3: Click on "Visual Basic" to open the VBA editor and then right-click on the any workbook objects.
Step 4: Click on "Insert" and then choose "Module" from the menu that appears.
Step 5: Paste the VBA code into the module and then close the VB editor.
The steps above ensure that the VBA code will remove all hyperlinks from the Excel sheet when you open it. To remove hyperlinks in a single click, you can add this Macro to the Quick Access Toolbar (QAT). Here's how to do it;
Step 1: Click on the "Customize Quick access Toolbar" option. It is usually the down-facing arrow at the end of the toolbar.
Step 2: Click on "More Commands".
Step 3: In the dialogue box marked "Choose Command From" dialogue box, click on "Macros".
Step 4: Click on the Macro you want to add which in this case is the "RemoveAllHyperlinks" macro we created above.
Step 5: Click on "Add" and then click "OK".
With this Macro added on your QAT, you can remove all hyperlinks on any worksheet by simply clicking on it.
3. Find and Remove Hyperlinks with Specific Text
You can also remove hyperlinks in Excel that are associated with certain text. For example if you want to remove hyperlinks of the text "Product 1", here's how to do it;
Step 1: Open the Excel document with the hyperlinks you want to remove and then hit "CTRL + F" to open the "Find and Replace" dialogue box".
Step 2: In the text box, type in the word that represents the hyperlink (Product 1). Click on dropdown arrow next to "Format" and select "Choose Format from Cell".
Step 3: Select the cell with "Product 1" and you should see "Preview" next to the left of the format button. Click on "Find All".
Step 4: This will display all hyperlinks with the specific text "Product 1". Hold the "Ctrl" or "Shift" button to select all the results. Right click on them and choose "Remove Hyperlinks".
4. How to Prevent Excel from Creating Hyperlinks Automatically
If you really don't want hyperlinks on your Excel documents, you can easily prevent Excel from automatically converting URLs and emails into hyperlinks. There is a setting in Excel that is designed to automatically convert Internet network paths into hyperlinks. Disabling it will prevent this from happening. If this seems like something you would want to do, follow these simple steps;
Step 1: Open Excel and then click on "File > Options".
Step 2: In the options dialogue box that opens, click on "Proofing".
Step 3: Click on "Autocorrect Options".
Step 4: In the dialogue box that opens, select "Auto Format As You Type" tab and uncheck "Internet and network paths with hyperlinks".
Step 5: Click "OK" to close the dialogue box. The next time you use Excel, you will notice that URLs and emails will not automatically be converted into hyperlinks.
Now you have more than one ways to remove hyperlink in Excel sheet and there is no need to suffer a disorganized or messy worksheet. Let us know which method works best for you in the comments section below.