Hyperlinks are a convenient way to navigate within an Excel document, but there’s a common issue: when you insert rows or columns, the hyperlinks will not adjust correctly, leading to incorrect links (or if you change a sheet name, broken links). Fortunately, there’s a more robust way to create dynamic hyperlinks that automatically adjust as your spreadsheet changes. This method leverages Excel functions to create hyperlinks that “follow” the cells they are linked to, even after structural changes in your spreadsheet.
Table of contents
YouTube: Hyperlinks in Excel (formula)- tying to specific cells
Building a Dynamic Hyperlink
First, start by linking to the target cell. In the example below, in cell B5, we are linking to a cell labeled “Unit Sales.” To do this, type =, click on the target cell, and press Enter to create the basic link. Next, to extract the cell reference from the formula, use the FORMULATEXT function and point it to the cell where you created the link (in this case, B5). This function displays the underlying formula or cell reference rather than the result, which is crucial for building a dynamic hyperlink.
Next, you’ll need to remove the equal sign (=) from the formula before using it in the hyperlink. To do this, apply the MID function. In the example below, we extract the text from cell C5, starting from the second character and continuing for up to 1000 characters, effectively removing the equal sign. Once the reference is cleaned, you can use the HYPERLINK function to create a clickable link.
In the HYPERLINK function, input the path by combining a hash symbol (#) with the cell reference (D5 in the example below) using concatenation (&). For the link’s friendly name, you can either type a custom label like “Click Here” or reference another cell for the label text as we did below. This approach ensures that the hyperlink follows the linked cell, even if the cell is moved to a different sheet.
What makes this method powerful is that the hyperlink will update automatically if the linked cell is moved. For instance, if you decide to move the data that you are linked to, to a new sheet, the hyperlinks will adjust themselves to point to the new location of these cells.
Tips and Troubleshooting
- Ensure that the cell references in your formulas are absolute if you want them to remain unchanged when copied elsewhere.
- If the hyperlinks aren’t updating correctly, double-check the syntax in the HYPERLINK function, especially the use of the hash symbol (#) in front of any cell address.
- If your hyperlinks still don’t follow the cells as expected, revisit the MID function to ensure that it’s correctly extracting the full cell reference without the equal sign