by Allen Wyatt
(last updated November 2, 2019)
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:
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 firstname.lastname@example.org) and you enter a new address into the cell (such as email@example.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:
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.
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel provides a good number of worksheet functions that can help you pick apart text strings in various ways. In this ...Discover More
Creating a drop-down list with Excel's data validation feature can be a nice touch for a worksheet. What if you want the ...Discover More
Excel allows you to easily add hyperlinks to a worksheet. Click on it, and the target of the link is opened in a browser ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.