Back
Excel

Hyperlinks in Excel: Hot or not? (videos)

HOT, of course! Well, actually, it depends.

Have you ever:

  • Inadvertently activated a hyperlink when you only meant to select the cell?
  • Imported a bunch of URLs that aren’t hot – but need to be?
  • Imported a bunch of URLs that are hot – but don’t need to be?
  • Pasted in links with “friendly” display text, but wanted to show URLs instead?
  • Wanted to stitch together URLs from bits of text in various columns?

I don’t know about you, but I answered “Only all the time,” “Uh huh,” “Oh yeah,” “Affirmative,” and “Yep” to those questions.

Hyperlinks frequently trip people up, so we asked Excel expert Bill Jelen to create videos to answer some of the more common customer questions. Check ‘em out. Do they help?

Question

Answer

See for yourself

How can I select a cell that contains a hyperlink without suddenly jumping to the link destination?

To select a cell with a hyperlink without actually activating that link, click outside the cell, and then use one of the arrow keys to navigate to the cell. You can also press CTRL and then click the cell. Or, click on the hyperlink and hold for about two seconds. When you see the hand icon change to a white plus icon, let go of the mouse.

 

 

Video: Selecting hyperlinked cells

 

 

How do I remove one or more hyperlinks? How do I turn off the automatic creation of hyperlinks?

Excel 2010 has two new menu items that can help you remove multiple hyperlinks at once: Remove Hyperlinks (note the plural!) and Clear Hyperlinks. These commands allow you to instantly clean up a whole list of links. If you’re using an ancient version of Excel, Bill shows you how to use a macro instead. Bonus: Learn how to stop Excel from automatically creating hyperlinks as you type.

Video: Get rid of hyperlinks

 

What do I do when I paste a column of URLs into Excel but none of the links are hot?  

In this scenario, you must convert the text into actual hyperlinks. If you just have a few links to convert, press F2 to edit the cell, and then press ENTER. If you need to fix a huge list of links, you can use the HYPERLINK function.

Video: Hyperlinks aren’t hot

 

I pasted a large number of links from a web page into Excel, but I only see the display name. How can I show URLs instead of friendly names?

You can use a simple macro to automatically extract URLs (e.g., http://www.microsoft.com/en-us/default.aspx) from friendly names (e.g., Microsoft web site).  

 

Video: Show URLs instead of friendly names in pasted text

When should I use the HYPERLINK function instead of the Insert Hyperlink command? What’s the diff, anyway?

If you just have a few links to create, you can use the Insert Hyperlink command. If you need to deal with a whole bunch of hyperlinks, again, consider the HYPERLINK function. Bonus: Watch Bill take columns that contain bits of a URL and join them into a single URL for use with the HYPERLINK function.

Video: Insert Hyperlink vs. =HYPERLINK

 

 

How can I create email links from the text in several different columns?

Say that you have first names in column A and last names in column B. You can combine the ampersand symbol (&) and the HYPERLINK function to build email addresses. When you click the link, your e-mail program automatically starts and creates an e-mail message with the correct address in the To box.

Video: Create a mailto hyperlink

 

 

 

For more tips, visit Bill’s site, MrExcel.com.

–Anneliese Wirth