Excel - Hyperlink - Tying to a cell

Lately one friend asked my help to figure out the way to use hyperlink to a specific cell in Excel Sheet; Which was easy after trying 4-5 options and 2 hours of Google search. So I decided to document it for future reference!

One liner:
  The technique is 'define the name' for the cell to be linked and use that 'defined name' in hyper link.

Ref:
http://excelribbon.tips.net/T006195_Tying_a_Hyperlink_to_a_Specific_Cell.html
http://www.k2e.com/tech-update/tips/418-tip-fastest-way-to-create-defined-names-in-excel

Before going on step by step, please find what is 'Name Box'. [courtesy link from http://blog.accountants.intuit.com/ways-to-be-more-productive/low-tech-excel-tips/]

Name box:

 The left most cell in 'Formula bar' is the Name Box. When you hover the Name Box cell, it shows 'Enter a name for a cell range, or select a named range from the list' [in MSOffice 2013]

Step by Step:

Create the Defined Name:

1. Select the cell you want to be hyperlinked (to be jumped to). In the 'Name Box' [check the image above], enter a 'reference' name and press return key. [Assumed 'Reference1' for our example case]

Use the Defined Name in Hyperlink

1. Go to the cell where you want to place the hyperlink (to be jumped from), right click and click on 'Hyperlink' from the menu
2. In the 'Insert Hyperlink' popup window, in 'Anchor' ['Link to' for older version] section, click on 'locate'; which opens another popup window
3. In the 'Select Place in Document' popup, expand the 'Defined Names' by clicking the small triangle [or + icon] next to it [make the triangle point down, or change + to -, by clicking], and select the reference you made in step 2. [Our example 'Reference1']

Now your hyperlink pointed to the reference; so inserting or deleting any rows or columns won't affect your hyperlink pointing cell location.


All the best!