Removing hyperlinks in Excel

BY J. CARLTON COLLINS, CPA

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.

SPONSORED REPORT

Tax reform complicates year-end tax planning

Get your clients ready for tax season with these year-end tax planning strategies, which address how to make the most of recent tax law changes, such as the new deduction for qualified business income and the cap on the deductibility of state and local taxes.

VIDEO

What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.