Hyperlink Doesn't Match Cell Contents

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.