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.

OPTION 1

  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.

OPTION 2

Use the following macro:
Sub NoMoreHyperlinks()
ActiveSheet.Hyperlinks.Delete
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!