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

Keeping Callouts Positioned

Using graphics to add callouts to your graphics is a common occurrence in Word. Here's how to stop all those graphics ...

Discover More

Turning Off Sharing

All good things must come to an end at some point. When you are done sharing your workbook with others, this is how you ...

Discover More

Problems with Default Workbook and Worksheet Templates

You can create a default template for both your workbooks and worksheets. These should be placed in the XLSTART folder, ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

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

Get Rid of Web Stuff

When you copy information from a Web page and paste it into a worksheet, you can end up with more than you bargained for. ...

Discover More

Extracting URLs from Hyperlinks

When you add a hyperlink to a worksheet, it consists of a minimum of two parts: display text and URL address. If you have ...

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 9 + 9?

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.