Rafiul Haq
We’re going to show you 5 methods in Excel to create a Hyperlink to a cell in the same sheet. To demonstrate our methods, we’ve taken a dataset with 2 columns: “Products”, and “Unit Sold”.
Download Practice Workbook
5 Ways to Hyperlink to Cell in Same Sheet in Excel
1. Using Insert Link in Excel to Hyperlink to Cell in Same Sheet
We’re going to use the “Insert Link” feature in Excel to Hyperlink to cell in the same Sheet.
Steps:
- Firstly, select cell B12.
- Secondly, from the Insert tab >>> Link >>> select Insert Link…
Then, the “Insert Hyperlink” dialog box will appear.
- Thirdly, select “Place in This Document”.
- Then, do these-
- Select ‘insert link’ from “Or select a place in this document:”
- Type “Mouse” in “Text to display:”
- Type B6 in “Type the cell reference:”.
- Finally, press OK.
We’ll see the text “Mouse” in cell B12 with link formatting.
We can click on it, and it will select cell B6. Thus, we’ve created an Excel Hyperlink in the same Sheet.
Read More: How to Add Hyperlink to Another Sheet in Excel (2 Easy Ways)
2. Hyperlink to Cell in Same Sheet by Implementing HYPERLINK Function
In this section, we’ll use the HYPERLINK function in Excel to create a Hyperlink to a cell in the same Sheet.
Steps:
- Firstly, type the following formula in cell B12.
=HYPERLINK("#C5","Keyboard Sold")The HYPERLINK function returns a clickable value. Here, we’ll get the link to cell C5. Moreover, we’ve put a hash (“#”) to indicate within this Sheet. Then, we’ll display the second part in cell B12 which is “Keyboard Sold”.
- Secondly, press ENTER.
After that, we’ll see the text “Keyboard Sold” appear in cell B12.
Again, we can click on this to go to cell C5. Therefore, we’ve shown you yet another method of linking to a cell in the same Sheet.
Read More: How to Use CELL Function in Excel (5 Easy Examples)
3. Using Combined Functions to Create Dynamic Hyperlink to Cell in Same Sheet
For the third method, we’ll use the ADDRESS, ROW, and HYPERLINK functions to create a dynamic link to a cell in the same Sheet. This means, that if we delete or insert rows or columns in the dataset, our formula will not break. Moreover, notice there is a blank row in our dataset.
Steps:
- Firstly, type the following formula in cell B13.
=HYPERLINK("#"&ADDRESS(ROW($C8),3),"Monitor Sold")Formula Breakdown
- ADDRESS(ROW($C8),3)
- Output: {“$C$8”}.
- The ROW function returns the row number of a cell. Cell C8 will return 8. Moreover, the ADDRESS function returns the location of a cell reference. Here, we’re denoting the C column by giving the value 3 in the formula. Thus we’ve got the output.
- Now, our formula reduces to, HYPERLINK(“#”&{“$C$8″},”Monitor Sold”)
- Output: “Monitor Sold”.
- The HYPERLINK function returns a clickable value. Here, we’ll get the link to cell C8. Moreover, we’ve put a hash (“#”) to indicate within this Sheet. Then, we’ll display the second part in cell B13 which is “Monitor Sold”.
- Secondly, press ENTER.
Then, we’ll see the output as described in the formula breakdown part.
We can click on that value to go to cell C8. However, notice there is an empty row, we can delete blank rows to verify our dynamic formula still works.
- Finally, remove Row 5.
If we click on the text “Monitor Sold”, we will select cell C7 now. Therefore, our formula works flawlessly.
Read More: How to Create Dynamic Hyperlink in Excel (3 Methods)
Similar Readings
- [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
- How to Create a Drop Down List Hyperlink to Another Sheet in Excel
- How to Link Picture to Cell Value in Excel (4 Quick Methods)
- Why Do My Excel Links Keep Breaking? (3 Reasons with Solutions)
- [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel
4. Hyperlink to First Matched Cell in Same Sheet in Excel
In this section, we’ll use the CELL, INDEX, and MATCH functions to create a Hyperlink to the first matched values in the same Sheet.
Steps:
- Firstly, type the following formula in cell B12.
=HYPERLINK("#"&CELL("address",INDEX($B$5:$B$10,MATCH("Mouse",$B$5:$B$10,0))),"Mouse")Formula Breakdown
- MATCH(“Mouse”,$B$5:$B$10,0)
- Output: 2.
- The MATCH function is used to find the position of a value. Here, we’re looking for the value “Mouse” in the cell range B5:B10. Moreover, we’ve put the 0 for exact matching.
- Our INDEX portion becomes -> INDEX($B$5:$B$10,2)
- Output: “Mouse”.
- The INDEX function returns a value from a range. Here, we’re getting the value from the second row from the cell range B5:B10 which is cell B6.
- Then our formula in the CELL portion reduces to, CELL(“address”,”Mouse”)
- Output: “$B$6”.
- The CELL function returns information about a cell. Here, we’ve set out info_type as “address”. This will return the first occurrence of the text “Mouse” in our range which is cell B6.
- Finally, our formula reduces to, HYPERLINK(“#”&”$B$6″,”Mouse”)
- Output: “Mouse”.
- The HYPERLINK function returns a clickable value. Here, we’ll get the link to cell B6. Moreover, we’ve put a hash (“#”) to indicate within this Sheet. Then, we’ll display the second part in cell B12 which is “Mouse”.
- Secondly, press ENTER.
Then, we’ll see the output as described in the formula breakdown part.
If we click on the text “Mouse”, we will select cell B6. Thus, we’ve shown you yet another formula to Hyperlink to a cell in the same Sheet.
Read More: How to Get Hyperlink from an Excel Cell with VBA (3 Methods)
5. Creating Hyperlink to Cell in Same Sheet from a Value
For the last method, we’re going to create Hyperlinks to cells in the same Sheet from values by using the HYPERLINK and VLOOKUP functions. Moreover, we’ve adjusted our dataset a bit for this.
Steps:
- Firstly, type the following formula in cell C13.
=HYPERLINK(VLOOKUP(B13,B5:D10,3,0),B13&" Sold")Formula Breakdown
- VLOOKUP(B13,B5:D10,3,0)
- Output: “#C6”.
- The VLOOKUP function returns a value from a range from the left side.
- Firstly, we’re looking for the value in cell B13 which is “Mouse”.
- Secondly, we’re setting our range in B5:D10.
- Thirdly, we’ll extract the value from column 3 which is the “Link” column.
- Finally, we’re putting 0 to find an exact match.
- Our formula reduces to, HYPERLINK(“#C6″,B13&” Sold”)
- Output: “Mouse Sold”.
- The HYPERLINK function returns a clickable value. Here, we’ll get the link to cell C6. Moreover, there is a hash (“#”) to indicate within this Sheet. Then, we’ll display the second part in cell B13 joined by the text “Sold” which is “Mouse Sold”.
- Secondly, press ENTER.
- Finally, use the Fill Handle to AutoFill the formula.
We can click on any of the Hyperlinks to go to their respective “Unit Sold” values in the dataset. In conclusion, we’ve achieved our goal of creating a Hyperlink to a cell in the same Sheet.
Read More: Excel Hyperlink to Cell in Another Sheet with VLOOKUP (With Easy Steps)
Practice Section
We’ve provided practice datasets for each method in the Excel file.
Conclusion
We’ve shown you 5 methods of Excel Hyperlink to cell in the same Sheet. If you face any difficulties in understanding any of the methods, feel free to comment below. Thanks for reading, keep excelling!
Related Articles
- 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
- [Fix:] Edit Links in Excel Not Working
- How to Activate Multiple Hyperlinks in Excel (4 Ways)
- How to Find and Replace Hyperlinks in Excel (3 Quick Methods)
- Excel VBA: Open Hyperlink in Chrome (3 Examples)
- [Fix:] Hyperlink to Website Not Working in Excel
