Hyperlink Doesn't Match Cell Contents

by Allen Wyatt
(last updated November 2, 2019)

1

Ian has a worksheet to keep track of email addresses for a group. Each address is in a cell and is formatted as a hyperlink, so he can click to open a new email. Ian updated one of the addresses so that both the cell and the formula bar showed the new address. However, when he hovered over the cell with the mouse, the link showed the old address and when he clicked on the cell, the email used the old address. Ian doesn't understand why it happened when the data in the cell had been revised.

There is an important factor to keep in mind when it comes to hyperlinks in a worksheet: What you see isn't necessarily what you get. This may sound glib, but it is based on the fact that what is displayed in a cell may be different than the URL associated with the cell.

Here are the situations that can occur that will result in what you see in the cell being exactly the same as the URL for the hyperlink:

  • You enter a web address (website address, e-mail address, etc.) into an empty cell.
  • You select the cell and edit what you see in the Formula bar.
  • You select the cell and edit the cell contents directly in the cell.

In any of these instances, when you press Enter, the URL associated with the cell is updated to match the cell contents. So, for instance, if the cell contains nothing or it contains a pre-existing address (such as bob@xyzcorp.net) and you enter a new address into the cell (such as bob@xyzcorp.com), then the resulting URL will match what you entered.

There is a caveat here: If, immediately after pressing Enter, you press Ctrl+Z, then the change to the URL is undone, but the change to the cell contents is not. You can see this if you enter an address, press Enter, immediately press Ctrl+Z, and then hover the mouse pointer over the cell. You should see that the URL doesn't match what is shown in the cell. To undo both the URL change and the cell contents change, you actually have to press Ctrl+Z twice in a row.

You might think that your cell contents should always match the URL during the course of normal editing of your worksheet data. This may not be the case, however—there are things that can throw a wrench into the works. Any of the following actions could result in a mismatch:

  • Your edit introduces a space at the beginning of the address or someplace within the address. For instance, you change "bob@xyzcorp.com" to " mary@xyzcorp.com", with the leading space, or to "mary @xyzcorp.com", with the space before the @ sign. It is not a problem to add spaces at the very end of the address.
  • You edit the contents of the cell to be a non-web address. For instance, you change the cell contents from "bob@xyzcorp.com" to "Bob Smith".
  • You right-click on the cell, choose Edit Hyperlink, and in the resulting dialog box, you change only the Text to Display field.

The bottom line is that if you are not very careful, it is easy to get what you see in the cell out of sync with the actual URL for the cell. The absolute best way to make sure they do stay in sync is to only make changes to the cell using the Edit Hyperlink dialog box—right-click the cell and choose Edit Hyperlink from the resulting Context menu. (See Figure 1.)

Figure 1. Editing a hyperlink.

There are three things that can be independently changed through this dialog box, and you'll want to make sure they are set properly to ensure things are in sync.

  • The Text to Display box. This box, at the top of the dialog box, controls what is "active" in the cell; in other words, what is clickable to initiate the hyperlink.
  • The Link Address box. This can have different names; in the screen shot above it is "E-mail address." The wording can vary based on what you have selected as a link type at the left of the dialog box. This needs to be set to, essentially, the "guts" of an HTML hyperlink. That's why, for an e-mail address, you see it in the screen shot preceded by the code "mailto: ".
  • The ScreenTip. If you click this button at the upper-right corner of the screen, you can set what you see as a ScreenTip when you hover the mouse pointer over the link. If you have the ScreenTip set to nothing, then a default ScreenTip is displayed that incorporates what you set in the Link Address box.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13695) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating Two-Line Custom Formats

Creating custom formats is a very powerful way to display information exactly as you want it to appear. Most custom ...

Discover More

Leading Zeros in Page Numbers

Page numbers in Excel printouts are typically simple counters, without much chance for embellishment. If you want to add ...

Discover More

Improper Index References

When indexing a document, you may get some funny results once in a while. If you get single index entries when you were ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Pulling Apart a URL

Excel provides a good number of worksheet functions that can help you pick apart text strings in various ways. In this ...

Discover More

Links to Hyperlinks

Hyperlinks in a worksheet can be helpful or essential, depending on the nature of your data. If you create a link to a ...

Discover More

Sending Single Worksheets via E-mail

Got a single worksheet that you want to e-mail to someone, but don't want them to see the rest of the worksheets in the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three minus 1?

2019-11-03 18:40:21

Jacques Raubenheimer

Another big culprit for getting what is displayed out of sync with what is stored as the url is find & replace.
I would venture to guess that this is probably the most common culprit in daily usage.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.