Microsoft Excel is one of the best products of Microsoft. It has many great capabilities and this is what that makes it so popular these days. Few days back while working on Excel, I realized that there is no inbuilt system in Excel to remove multiple hyperlinks at once, although there is a way to remove single hyperlink at a time but the feature does not support bulk processing. So, in this post I will enlist few ways by which you can remove multiple hyperlinks in Excel.
How to Remove Multiple Hyperlinks in Excel 2007 & 2010 :
Actually the ways which I am going to show you here work for both Microsoft Excel 2007 and Microsoft Excel 2010. If you are using Excel 2003 or previous versions then you can remove the hyperlinks by using macros as explained in the fourth method.
Removing multiple hyperlinks by Clearing the Cell Format:
In Excel we can remove all the formatting that are applied to a cell and with this we can also remove the annoying hyperlinks.
1. In the spreadsheet select the cells from which you want to remove hyperlinks. You can select multiple sells in Excel by ctrl and left click or by using the mouse select functionality.
2. After selecting the cells click the small dropdown arrow in front of “clear” button, this will open a menu on this menu select the option that says “Clear Formats”. After selecting the option all your hyperlinks will be removed.
Removing multiple hyperlinks by Format Painter:
In this method we have to remove the hyperlink from just a single link and then apply its format to all the other cells where the hyperlinks are to be removed.
1. Select any one of the cells from which you want to remove the hyperlinks, after this right click the link and select the option that says “Remove Hyperlink”.
2. Now after you have removed hyperlink for the first link, select the link and double click the “Format Painter” icon (as shown in image below).
3. After clicking the “format painter” button select the cells for which you want to remove the hyperlinks and all hyperlinks will be removed.
Removing hyperlinks by Paste Special method in Excel:
In this method we will fool Excel to think that the link we have is a number, so that it will remove the hyperlink.
- On your Excel sheet, type in the number “1” (without quotes and it can be deleted later).
- Right click the cell where you typed the number 1 and then click on Copy.
- Select the cells for which you want to delete the hyperlink and copy them.
- Now right click on the selected area and select the Paste Special option.
- Under Operation, click on Multiply, and then click OK.
- On the Home tab, in the Styles group, click on Cell Styles. From the drop down menu, click on Normal.
- Now the hyperlinks are deleted and you can delete the cell in which you had typed“1”.
Removing hyperlinks by using a Macro:
This is the most powerful method and it works for all the versions of Excel right from Excel 2001 to Excel 2010. In this method we are going to write a 3 line code to delete all the selected hyperlinks.
- In the Excel , press “Alt+F8”, this will open a macro window.
- Enter the name of the macro and click the “create” button.
- Now it will open a window where you can edit your macro code, insert this code
Selection.Hyperlinks.Delete
- Just above the “End Sub”, For instance see my macro in the image.
- After this save the code and close it.
- Now you will be back on the spreadsheet, here select the cells for which you want to remove the hyperlinks.
- Again press the Alt+F8 keys and now select the macro that you have just created and click on the run button.
- Finally, all your hyperlinks will be removed now.
So, this was the method how to remove multiple hyperlinks in Excel.
?ed the ABOVE POST, THEN GO A STEP FURTHER AND SHARE IT WITH YOUR FRIENDS!!
Article by Ankit
Ankit is the founder of Geek Revealed. He is tech Geek who loves to sit in front of his square headed girlfriend all day long. :D You can find Ankit on FaceBook or Google Plus
Comments on this entry are closed.