The missing link of HYPERLINK function in #Excel help

I seldom use the HYPERLINK function in #Excel.   Normally I insert hyperlink by CTRL+K, then setting the reference I want the link to go to.   That is super easy (or quick and dirty)!

Excel Tips - Hyperlink0

Note: You may go to Insert tab on ribbon –> Link

However, laziness comes with a price, i.e. limitation ==> Static link.  We cannot insert different hyperlinks based on the contents in a range of cell.  And that’s the reason I need the HYPERLINK function.    Here’s my story:

Excel Tips - Hyperlink

Recently, I created a workbook with more than 50 sheets.  For easy navigation for users, I created a summary page, with a table of content.  Well, 50 sheets!  And I am not going to insert 50 different links by doing the CTRL+K 50 times.  NO WAY.

As I said, I seldom use HYPERLINK function and I did not remember the arguments for this function.  I paid close attention to the hint while I typed =HYPERLINK in the formula bar…

Excel Tips - Hyperlink01

And this is my first attempt, a fail attempt…

Excel Tips - Hyperlink1

I clicked the “fx” icon on formula for more details…

Excel Tips - Hyperlink2

Although I realized that I need to input a text string for the “Link_location”, I was not sure how to do that for a location within the same workbook.  Then I clicked “Help on this function” in order to find the correct way to construct what I need.

This is what I do for many functions… who knows all 400+ functions in Excel?  Maybe there are someone but not me. 😛

Here’s what I get from Excel Help:

The Syntax:

HYPERLINK(link_location, [friendly_name])

  • Link_location    Required. The path and file name to the document to be opened. Link_location can refer to a place in a document — such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file that is stored on a hard disk drive. The path can also be a universal naming convention (UNC) path on a server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL) path on the Internet or an intranet.Note   Excel Online the HYPERLINK function is valid for web addresses (URLs) only. Link_location can be a text string enclosed in quotation marks or a reference to a cell that contains the link as a text string.If the jump specified in link_location does not exist or cannot be navigated, an error appears when you click the cell.
  • Friendly_name    Optional. The jump text or numeric value that is displayed in the cell. Friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location as the jump text.Friendly_name can be a value, a text string, a name, or a cell that contains the jump text or value.If friendly_name returns an error value (for example, #VALUE!), the cell displays the error instead of the jump text.

Good news is what I highlighted in red.  The function works for jumping to a specific location in an Excel worksheet or workbook.  Not too good news is it did not specify how…

So it’s better to look at examples…  Because my objective was to create links within current workbook, I focused on examples demonstrating it…

And here’s the example:
Example Result
=HYPERLINK(“[Book1.xlsx]January!A10″,”Go to January > A10”)  To jump to a different location in the current worksheet, include both the workbook name, and worksheet name like this, where January is another worksheet in the workbook.
Source: Excel Help
I followed the example carefully and input the following formula:
=HYPERLINK("[Sample File - HYPERLINK.xlsx]" & A3 & "!$A$1",
"<-- Go to this report") 
'where A3 resides the sheet name; 
Note: The ! after sheet name before cell reference is necessary
Note: As I need to set multiple hyperlinks based on cell values, I used & to concatenate file name, sheet name, and cell reference instead of hard-coding it.
Excel Tips - Hyperlink3

Exciting moment… Did it work?

Yessss!  It did.  Then I copied down the formula to B7 and see if other links work as well.  BINGO.  They all did… until I saved the file as another file name.
After the formula was successfully created, I saved the workbook with a new file name: “HYPERLINK_Final.xlsx”.  I tested the hyperlink again and then I realized a big problem: The formula were hard-coded with the file name “Sample File – HYPERLINK.xlsx”.  That was a total failure as the links opened the original workbook (provided that it is saved in the same folder) but not the current workbook.
Well… shxt happened, especially when writing an Excel formula.  😛
Then I tried to remove file name from my formula, even though Excel Help stated explicitly that BOTH file name and sheet name are required.
Oooops… Excel Help didn’t lie.  My formula did not work without the file name.
Excel Tips - Hyperlink4.PNG
To further verified that, I tried Store1!$A$1 instead…
Excel Tips - Hyperlink5.PNG
No luck!  😦
I read the Excel Help thoroughly, studied the examples listed there again.  I found the following:
=HYPERLINK(CELL(“address”,January!A1),”Go to January > A1″) To jump to a different location in the current worksheet without using the fully qualified worksheet reference ([Book1.xlsx]), you can use this, where CELL(“address”) returns the current workbook name


Indeed, it was not about jumping to a different worksheet in the current workbook.  Nevertheless it reminded me of the function CELL.  After many semi-successful and fail attempts, I guessed I understood how HYPERLINK function works:  I needed to feed a text string of file name + sheet name + reference into the first argument (i.e. Link_location).  The challenge up to this point was to get the file name.  There are many ways to do so.  And I used the following formula:



Then I put this into the first argument of HYPERLINK:

& A3 & "!$A$1",
"<-- Go to this report")
where A3 and "!$A$1" are the sheet name and cell reference respectively

Guess what?  It worked, finally!

Excel Tips -HYPERLINK1

As I did not hard-code the file name into the formula, I could save the file as new file name without worrying the links go wrong.  🙂

The story has not yet finished…

Although I had fixed my problem, I didn’t think that’s the right way as it was so stupid.  I couldn’t believe there is no simpler way to achieve the same thing.   So what did I do?  GOOGLE!  What else?!

The top search result was an article by Debra Dalgleish of Contextures , one of my favorite Excel resources site.  I was very confident that I could find the answer I was looking for.

Here’s the missing link

With no doubt, I got what I needed from the article of Contextures.  What I needed to do was to replace the long formula for getting the file name with #.

Before, my formula was:

& A3 & "!$A$1",
"<-- Go to this report")
where A3 and "!$A$1" are the sheet name and cell reference respectively

After, my formula is:

=HYPERLINK("#" & A3 & "!$A$1",
"<-- Go to this report")

# is the trick.  It means current workbook here.


Moral of the story:

  • Google maybe more helpful than Microsoft in finding a solution of an Excel problem.  🙂


You may download a Sample File – HYPERLINK to experiment.

This entry was posted in Formula and tagged . Bookmark the permalink.

2 Responses to The missing link of HYPERLINK function in #Excel help

  1. XLarium says:

    There is a link under (almost) every article on “Was this information helpful?”
    Click on “No” and explain yourself.
    Click on “Send”.

    Maybe it will help. 🙂


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

roads bel travelled

Exploring open roads without breaking the bank


Work smarter by Mastering Functions in Excel


Chants of Power BI, Power Query and M

ExcelUser Blog

Work smarter by Mastering Functions in Excel


Leading Consultants on Power BI and the Microsoft Data Platform

Excel Esquire

Helping lawyers make the most of Microsoft Excel

ASAP Utilities for Excel – Blog

Work smarter by Mastering Functions in Excel

The Analyst Cave | Excel, VBA, programming and more

Work smarter by Mastering Functions in Excel


zum Excel Profi werden

Ann K. Emery

Transform your technical information for non-technical audiences through better data visualizations, reports, slideshows, and dashboards. I provide workshops, webinars, conference keynotes, coaching sessions, and custom design projects.

The Ken Puls (Excelguru) Blog

More geeky stuff from the author of

Spreadsheet Page Blog

Work smarter by Mastering Functions in Excel

Oz du Soleil

Excel Training, Education & Entertainment

Peltier Tech Blog

Peltier Tech Excel Charts and Programming Blog


Microsoft Excel Solutions and Training

Let's Talk Excel

Q&A about Excel


Doug Glancy's Excel Site

Option Explicit VBA

A blog of Excel, Dashboards, Visual Basic for Applications, Data Analysis, Operations Research, and Visualizations.


Advanced Excel Techniques with XOR LX

Excel Unplugged

Collection of Excel Know How and little Excel Tips and Tricks

Excel and UDF Performance Stuff

Charles Williams on 'Making Excel go Faster'

Contextures Blog

Save Time and Money in Business

My Online Training Hub

Work smarter by Mastering Functions in Excel

Bacon Bits

Work smarter by Mastering Functions in Excel - Learn Excel & Charting Online

Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity

%d bloggers like this: