20 March, 2014

Remove all hyperlinks within Excel

Ever copy a webpage of data into Excel, and it brings along a bunch of hyperlinks? Of course it isn't practical to remove each hyperlink manually. Fortunately there are a couple of options to perform a bulk removal of hyperlinks.
Note that I'm using Excel 2007 in my examples.


  1. Select the cells that contain hyperlinks.
  2. On the Home tab, in the Editing section, click the Clear drop-down arrow.
  3. Click Clear Hyperlinks.
I have found in some cases that the Clear Hyperlinks option isn't available. In those cases, use option 2 below.


Use the following macro:
Sub NoMoreHyperlinks()
End Sub
For those of us that need a reminder on how to create macros, here's a more detailed process.
  1. Press Alt-F8 to display the Macro dialog box
  2. Enter a new macro name (such as "NoMoreHyperlinks") in the text box just below "Macro name:"
    - This will change the Create button from an inactive to active state
    - If you already have one or more macro, the name of one may already be in the "Macro name:" text box. That's ok, just type over it and Excel will realize you're wanting to create a new macro.
  3. Click Create -- this opens the Visual Basic Editor
  4. Copy the lines above and paste into the edit window
    - Note if you didn't use "NoMoreHyperlinks" as a name, you will need to make sure you update the macro code accordingly
  5. Close the editor window -- you should be back on your worksheet
  6. Press Alt-F8 again
  7. Select your new macro and click Run -- all your objects should now be deleted from your worksheet
 That's it!