In this blog post we will go through 3 different methods to create a Hyperlink to another Worksheet in Excel.
Following is the data which we will be using to explain these methods. Given below is the Cost summary of a Highway Project and the Total cost is the consolidation of amounts from the Worksheets ‘Roads’, ‘Street Lighting’, ‘Storm Water’ and ‘Domestic Sewerage’.
Let’s see how to create Hyperlink in the cells containing amount so that, a single click on the link will take us to the source data.
HYPERLINK function to link Worksheets
The HYPERLINK function in Excel can be used to create a clickable hyperlinks to Workbooks, Files, and, Webpages.
The HYPERLINK function has two arguments.
Link Location can refer to a place in a document, such as a specific cell or named range in an Excel Worksheet or Workbook
Friendly name is the text to display in the cell.
In the formula bar you can see that the amount ‘2.21 Crores’ comes from the cell ‘F4’ of the Worksheet called ‘Roads’.
As we need to create a link to the cell F4 in the Worksheet called Roads, Link location will be Roads!F4.
We want the cell to display the amount itself. Roads!F4 will be the second argument.
So, the formula to create hyperlink to cell ‘F4’ of the Worksheet called ‘Roads’ will be,
The # is added before the sheet name to indicate that the formula is pointing to a Worksheet in the same Workbook.
Similarly, to create a hyperlink to cell ‘G217’ of the Worksheet called ‘Street Lighting’,
=HYPERLINK("#'Street Lighting'!G217",'Street Lighting'!G217)
With a single click on these links we can navigate to the corresponding locations.
Manually insert Hyperlink
To manually insert a Hyperlink into a cell,
right-click on the cell > Link
Insert Hyperlink dialog will be activated.
Click on Place in This Document on the left side bar > select the corresponding Worksheet from the Worksheet list under the label Cell Reference (here I have selected the sheet, Roads) > Type in the address of the cell into the input box under the label, Type the cell reference (F4) > Click OK
Hyperlink to the cell F4 of the Worksheet called Roads is created in the cell C3. Place the cursor over the cell C3 and the link location will pop up as screen tip.
Drag and Drop to create Hyperlink
This one is the easiest method among the different methods explained here.
To create Hyperlink to a cell,
select that cell > right-click on the border of the cell > holding the Alt key, drag it to the Worksheet tab in which you want the Hyperlink > place the cursor over the cell where you want to insert the link > release the mouse button and you will get a menu with different options.
Select Create Hyperlink Here.
If you want the Hyperlink on the same worksheet itself, no need to press the Alt key.