Hyperlink Doesn't Match Cell Contents

Written by Allen Wyatt (last updated August 21, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


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 Microsoft 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

Browsing by Heading

The Object Browser can be a great (albeit underused) way of navigating through your document. One handy way to move about ...

Discover More

Expanding Width of All Tables

If you have a lot of tables in your documents, you might want to change the width of all of them. You can take forever ...

Discover More

Pop-Up Comments for Graphics

Excel allows you to add comments to individual cells in a worksheet, but what if you want to add comments to graphics? ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Generating a Web Page

Want your worksheets to be available to others on the Internet? Excel provides a way you can save your data in HTML ...

Discover More

Hyperlinks No Longer Work in a Workbook

Hyperlinks can be a great timesaver and very convenient. Unless, of course, if they don't work as you expect. This tip ...

Discover More

Opening Sites in a Browser

You can store all sorts of information in a worksheet, including Web addresses. If you want to open those addresses in a ...

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}] (all 7 characters, in the sequence shown) 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 less than 3?

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.