Removing hyperlinks in Excel

Q: I frequently copy and paste large amounts of data from the web into Excel 2010. My problem is the pasted data often contains hundreds of hyperlinks, which makes it difficult to then select those cells without triggering the hyperlinks (to avoid triggering a hyperlink, I click on a nearby cell and then arrow over to the cell containing the hyperlink). I know I can avoid this problem by pasting the data into Excel using the Paste Values command, but I don’t want to lose the webpage formatting, so this approach doesn’t work for me. I have tried copying the hyperlinked cells and pasting them as values [to the same location], but the hyperlinks still remain, so this doesn’t work either. My current approach is to right-click on each cell and select Remove Hyperlink one at a time. Is there an easier way to remove these hyperlinks?

A: Excel 2010 provides a new option called Remove Hyperlinks (plural—with an “s”) that enables you to remove multiple hyperlinks (prior editions of Excel provide only the Remove Hyperlink option, which removes only one hyperlink at a time). To remove more than one hyperlink, select a range containing multiple hyperlinks (such as a range of cells, columns, rows, or an entire worksheet), then right-click on any cell, and select Remove Hyperlinks from the pop-up menu. (The Remove Hyperlinks tool does not work across multiple worksheets.)

Note: Remove Hyperlinks appears when your selection range contains two or more hyperlinks, otherwise Remove Hyperlink appears, which is probably why you previously overlooked this option.

For those using Excel 2007 or 2003, here is a trick that might help. You can remove multiple hyperlinks but retain the formatting by copying a range of data containing multiple hyperlinks and pasting them to a new location using the Paste Values command (from column A to column C in the example pictured below).

Next, delete the original data you copied. This action will delete the data and the hyperlinks, but not the formatting. Finally, Copy the data and Paste Values that data again to its original location (from column C back to column A in the example pictured below). (Remember, when using Paste Values, the pasted data adopts the formatting of the paste destination.)

The data will be returned to the original location, with formatting, but no hyperlinks, as shown at left. (Notice in the picture below that when the cursor hovers over the data in cell A3, which previously contained a hyperlink, no hyperlink pops up.)

One final tip: You can select a cell containing a hyperlink using your mouse by clicking on the cell and holding the mouse button down a few seconds until the pointer becomes a cross shape, and then releasing the mouse button.