Removing multiple hyperlinks in Excel

Removing a bunch of hyperlinks can be tedious. Imagine the time you have to spend right-clicking on each of those links and selecting ‘remove hyperlink’ on the context menu. Of course it would just take two clicks, but what if you are to remove a hundred of them or perhaps 1000 hyperlinks? I’m sure that would be enough to scrape out the paint of your right-mouse button. :)

Below are some simple techniques you can use in order to remove single to multiple hyperlinks on a page:

Removing just one hyperlink
1. Right-Click on the cell and select Remove Hyperlink from the context menu.

Removing hyperlinks using the cell format
1. Select the cells having hyperlinks. You can use CTRL+LEFT CLICK to randomly select a cell.
2. On the menu click on Edit. Hover your mouse to the option Clear and select Fomats. Please note that the cell is still clickable to remove the hyperlink completely you have to select Clear->All.

Remove Excel hyperlinks on selected cells the better way
1. Type in any text or number in a blank cell
2. Right-click and select Copy on the context menu.
3. While pressing CTRL, select each cell with the hyperlink you wish to be removed
4. On the Edit menu, select Paste Special.
5. Under Operation, click Multiply and then click OK.
6. If you are using Office 2007, you may need to click or change the cell format to Normal found at the Styles Group Toolbar.

Removing Excel hyperlinks using a macro
Assuming you know excel programming you could create a macro to automatically remove the hyperlinks.
1. Start Visual Basic Editor. Alternatively you can press ALT-F11 to start the editor.
2. Double click the workbook you are using on the Project Explorer.
3. Type the following text:


Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

4. Save your work.
5. Run your macro by pressing ALT-F8 or using the menu by Tools->Macro->Macros
6. Select the macro you have made, it should have the name ‘RemoveHyperlinks’.


Removing the Default Hyperlink Option

The most efficient way to remove them is by actually avoiding them in the first place. Now you don’t need to follow those hard and confusing steps above anymore. You can prevent Excel from formatting URLs and network links by removing its AutoFormat function at the AutoCorrect dialog box. I am using Office 2007, see the image below:

autocorrect1.jpg

 

autocorrect2.jpg

Unfortunately, if you are using Office 2003 or lower, i can’t give you the exact steps but the AutoCorrect is at Tools on the main menu bar. I just don’t remember where to go from there. If you are using Office 2000, there is no way we could prevent URLs from being formatted.

29 Comments
  1. Thanks!! worked great and simple to do.

  2. Thanks!! it worked. This would be great help for the people downloading spreadsheets form internet for various projects purpose.

  3. life saver.

  4. Thankyou, well explained & easy to understand.

  5. Excellent thanks was wondering how to do this for an entire worksheet.

  6. Thanks for this, the macro was so simple.

  7. So easy when you know how! Thank you very much for this.

  8. I appreciate the tip on deleting multiple hyperlinks, it worked great for what I wanted to do with my spreadsheet. Thanks!

  9. Are you kidding me? There are big problems with each method you describe. Excel is trying to be too smart. Why are hyperlinks the default and why can’t we turn them off??? I have a page full of hyperlinks and I want them gone. If I clear the format, I loose my formatting. If I click on each individually, I’ll spend all day at it. I don’t have time to make macros. And if I did, I will need to run it on each computer I use. Where is the option to disable hyperlinking. It should be a one-click solution. This is BS.

  10. Microsoft is retarded!!! Why is it so difficult to remove hyperlinks. Jerks.

  11. Thanks! The Paste Special/Multiply trick works to clean links well. Except for that it leaves zeros in blank cells. That’s minor compared to manually removing the links.

  12. good one man thanks you soo much love u

  13. Wonderful! Thanks so much for this, it saved me so much time :o)

  14. Doesn’t work if your cells contain numbers. Worked on text hyperlinks but not hyperlinks in the form of a number. It would multiply but leave the hyperlink.

  15. Thanks, it really worked ! It was such an annoyance having to deal with formatting. There should be a simpler way to remove all hyperlinks without loosing the format of the cell.

  16. Or you just copy the data into notepad and then paste it back into excel…

  17. Thank you very much for the valuable tips, I learnt much & saved much time.
    Yass,

  18. I appreciate the tip on deleting multiple hyperlinks, it worked great for what I wanted to do with my spreadsheet. Thanks!

  19. Awesome! It worked perfectly. Thank you sooooo much. You saved me a lot of time and frustration!

  20. Here’s a easy way to remove the hyperlink without removing the text (Excel 2003 at least, not sure on other versions)
    Hold down Crtl and select each cell that needs to have the hyperlink remove. Go up to your Menu Bar and Select Format, then Style…
    Once you open the style dialog box, look at the drop down box and you will see it says Hyperlink. Well, we don’t want that now, do we? So lets change that. Click on the dropdown and scroll down a few times until you see Normal as an option. Select that and you will see the Styles includes section change. Now, uncheck all the boxes except the first one. It should say Number then like General, or text, or decimal beside it. Now, simply click Ok and take a look at your excel sheet.
    The cells you had selected are now normal text and not a hyperlink and your formatting should still be intact.
    Please feel free to reply or ask questions if needed.

  21. the instructions weren’t clear enough to me; I must be an idiot.
    1. Type in any text or number in a blank cell
    2. Right-click and select Copy on the context menu.
    3. While pressing CTRL, select each hyperlink you wish to be removed
    Number 3 tells me nothing. I may has well go to apps class. Theres obviously something missing from your instructions, and other people were too nice to say anything perhaps because they could afford the extra time to figure it out instead of you posting exact instructions. At least you saved yourself a few moments when you posted this article??? Nice.

  22. Hello Michael,
    Sorry if i got you confused. I could have said it this way:
    3. While pressing CTRL, select each cell with the hyperlink you wish to be removed.
    if you clicked the hyperlink, of course it will open the browser with the link. so you need to click on the area on the cell without touching the blue link.

  23. Copy an entire row or column, or a range of rows or columns, then select an equivalent range of rows or columns, right-click and select Paste Special. Choose the Values option in the Paste Special dialog popup. Click OK.

  24. Also you can TURN OFF “Auto Hyperlink” option from the Menu.

    Click the Microsoft Office ButtonButton image, and then click Excel Options.
    In the Excel Options dialog box, click Proofing, and then click AutoCorrect Options.

    Now Under Auto Correct you will find AutoFormat as you type. Click on that tab. Under the tab uncheck “Internet and Network Paths with hyperlink”

    Now if you will type an email address or a hyper link / website address, excel will not create Hyperlink to it automatically :)

    Cheers.

  25. Or, you can just paste into notepad to automatically strip all of the formatting. Then copy and paste into your desired application….done.

  26. Just Copy – Paste Special – Values.. :)…. [1] Select columns/rows with hyperlinks to be removed.. [2] Copy (ctrl V)… [3] Click on a cell in either a new worksheet or go to a blank part of the same worksheet…. [4] Right Click…. [5]Select Paste Special… [6] Select Values… TADA!

  27. Just found a MUCH easier way to do this.
    1-Remove hyperlink in one cell just by right clicking and removing.
    2-Click on that cell, then click format painter
    3-Drag the mouse over the cells with hyperlinks
    4-Boom, roasted.

  28. Worked in 2010! Awesome – thanks for the guide

  29. Thanks a lot. Its simple and effective.
    Really did a great job.

Leave a Reply