Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Tying a Hyperlink to a Specific Cell.
Written by Allen Wyatt (last updated November 21, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Manoj created a hyperlink between two worksheets by using copy and paste hyperlink command (the hyperlink targets a specific cell). Later he inserted some rows on the target worksheet that caused the target cell to move down a bit. Even though the target cell moves down, the hyperlink continues to reference the old cell location. Manoj is wondering if there is a way to make sure that the hyperlink always targets the cell he intended when creating the link.
In Excel, hyperlink addresses are essentially text that references a cell. Formulas in Excel link to cell references which adjust when changes in the worksheet structure are made (inserting and deleting rows and columns, etc.). Hyperlink addresses, being text instead of cell references, will not adjust with such changes.
The solution is to create a named range that refers to the target cell you want used in the hyperlink. (You do this by displaying the Formulas tab of the ribbon and then clicking on Define Name in the Defined Names group.) When you create your hyperlink, you can then reference this named range in the Insert Hyperlink dialog box. (See Figure 1.)
Figure 1. The Insert Hyperlink dialog box.
At the left of the dialog box, click Place In This Document. You'll then see a list of named ranges in your workbook and you can choose which one you want to be associated with this hyperlink. In this way, you allow Excel to take care of translating between the name and the address for that name, which means that the hyperlink will always point to the cell you want it to point to.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6195) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Tying a Hyperlink to a Specific Cell.
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!
Excel allows you to easily add hyperlinks to your worksheets. If those hyperlinks are suddenly being blocked, it can be ...
Discover MoreCopying information from one place to another in a worksheet is easy. Copying hyperlinks may not seem that easy, but you ...
Discover MoreExcel allows you to define hyperlinks in your worksheets, and these can target specific cells on other worksheets. Here ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-03-27 12:32:50
J. Woolley
To make the hyperlink address relative instead of absolute, consider using Excel's HYPERLINK function or the freely available SuperLink function in My Excel Toolbox. Both functions require a text value for Link_Location, but that value can be specified using Excel's CELL("address",Reference) function. For example, to make a hyperlink on Sheet2 that references cell E5 on Sheet1, add either of the following formulas to a cell on Sheet2:
=HYPERLINK(CELL("address",Sheet1!E5))
=SuperLink(CELL("address",Sheet1!E5))
These formulas will automatically adjust when either Sheet1 or Sheet2 is changed. The SuperLink function is recommended because it resolves several issues with the HYPERLINK function.
See https://sites.google.com/view/MyExcelToolbox/
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments