Hyperlink Formula in Excel
- 22 Comment
I’m going to add this section to the Microsoft Excel Help section (menu on the left) of the website. An excel user contacted me and questioned how to create a hyperlink inside of excel using the value in another cell to as part of the link. The Formula looks like this:
HYPERLINK(link_location,friendly_name)
where link_location is where you want the hyperlink to go to and friendly_name is how the link will appear on your sheet.
For example, if you have a file on your desktop it would look something like this:
Hyperlink(“C:\Documents and Settings\Admin1\Desktop\Example.xls”,”Example Link”)
you would just need to change the exact path to the file on your computer. You can also make the file name a cell reference that contains the name of the file you want to point to, as in this example:
Hyperlink(“C:\Documents and Settings\Admin1\Desktop\” & A1 & “.xls”,A1)
in this example, A1 could equal “Example” and “Example will also be displayed for the hyperlink text.
There is also a way to create this using a macro function, which I will be adding to the macro section in Excel Tips soon.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
More Excel Tips
22 Comments on this post
Trackbacks
-
Sal said:
Link extraction.
Let say cell “A1″ has a name, it also has an email link. I want to extract that email address to cell “A2″. Do you know how to do that.
April 11th, 2009 at 7:29 pm -
J said:
You need a macro as seen here
April 29th, 2009 at 11:07 pm -
ALEX said:
there no examples. pls provide so i could understand more
June 18th, 2009 at 10:02 pm -
BD said:
we link hundreds of drawings in our spreadsheets. does anyone know an easy way to accomplish this. right now we are linking one by one and finding the path for each one. it is very time consuming.
August 25th, 2009 at 3:10 pm -
john said:
I’m not exactly sure what you’re trying to do… but you could build the strings in multiple columns with the path in one column and the filename in another. Then use the hyperlink to link them all together. Hope that helps, if not we’ll give it another shot.
August 25th, 2009 at 10:09 pm -
BD said:
thanks for the quick response. i will try to make this a little more clear. we have what we call a drawing log. we deal with hundreds of drawings that go through different phases. we use the log for easy access to the drawings. say we have drawings 1 thru 100 and we want to pull up #78. instead of looking through the files and folders to find it, we hyperlink our spreadsheet and just click on that number to open it. the problem we have is that when we hyperlink them one by one, we have to open 3 to 4 different folders per drawing to hyperlink. with so many drawings in the folder, it takes awhile to open each time, which ends up to be an all day project for somebody.
August 26th, 2009 at 7:24 am -
Dren said:
Just want to ask if you know how to hyperlink an exe file without excel prompting the security warning once it is triggered.. Thanks
September 11th, 2009 at 7:49 am -
john said:
Check out this article from microsoft…. looks like some registry edits are necessary to make this work:
September 11th, 2009 at 9:02 am -
Dren said:
wow, that was fast. Thanks for the link.. might as well read it tom morning. thanks again
September 11th, 2009 at 9:27 am -
doni said:
Please help me..
I make hyperlink in a sheet with image/shape. But if I moved file to another folder, hyperlink not work, Cannot open the specified file. Please give me solver..September 14th, 2009 at 2:40 am -
john said:
Did you update the location of the file address in the hyperlink as well?
September 14th, 2009 at 8:53 am -
doni said:
I mean , this file will be used by many people, when moving a computer course will change the location of the file. so I wanted to create a hyperlink that I would change as well as otomastis location.
I have one file from some one, a hyperlink is always updated, including if the file name was changed, then hyperling changed.September 14th, 2009 at 11:09 pm -
doni said:
exp : I make hyperlink with pacth file:///D:\Document\Tax\1721.xls
I move file 1721.xls to another folder D:\Document\New Folder\1721.xls
I open file in New Folder, Hyperlink not update to file:///D:\Document\New Folder\1721.xlsSeptember 14th, 2009 at 11:27 pm -
Micromutt said:
Still searching for my answer but…
BD and Doni might benefit from setting the ‘Hyperlink Base’ in the document properties. (Excel 2007 – Office Button / Prepare / Properties / (i)Advanced Properties)
If all of BDs drawings are in the same folder setting the Base to that folder would allow him to have just the file name for the link simplifying the tedious link work.
Doni could move his file anywhere and only needs to change the Base address.
YMMV
September 18th, 2009 at 1:15 pm -
john said:
Thanks for the help with Doni’s question. Great suggestion.
John
September 18th, 2009 at 2:52 pm -
Automobile India said:
I need a formula for creating excel hyperlinks. I have a file with 50 tabs. The summary tab lists each of the 50 tabs. Would like to create a hyperlink on the summary tab to jump to the selected tab. Using insert hyperlink on each one takes a long time.
October 22nd, 2009 at 5:45 am -
BD said:
Thanks for the suggestion Micromutt. That is about 2 steps better than what I am currently doing. Now a question coming off of this suggestion.
I am linking .tif files with a common beginning of the name.
i.e.
922-8596.tif
922-8597.tif
922-8598.tifDo I have to type in the entire file name, or can I link with just typing 8596 or 8597 or 8598 somehow? Bypass the 922- & .tif.
I appreciate any and all suggestions.
Thank YouOctober 22nd, 2009 at 8:19 am -
Automobile India said:
Is there any formula in MS Excel so that I can Hyperlink with the sheet which has been hiden with view code.?
November 1st, 2009 at 9:53 pm -
CC said:
Hello,
I’am trying to write a formula that will allow me to combine two columns. The first column is a tellephone number the second is a website address. How do I combine the two columns and keep the formating for a web address keeping it a clickable link??
please help!
December 1st, 2009 at 12:31 pm -
Mike Wang said:
A tip for doni, as well as related issues:
If you are (hyper)linking to a local file, you can use relative references, which will make things much more portable. This is possible using “./” where the “.” refers to the currently directory the excel document is located in.
e.g. hyperlink(“./” & A1 & “/” & A2 & “.pdf”, “Open file”)
This links to a pdf file located in a subdirectory specified by the value of the A1 cell and has filename (without extension) specified by the A2 cell. e.g. this might open the file /My Docs/Presentation.pdf
Note the use of ampersand (“&”) to concatenate strings.
I am on Excel 2007, so cannot verify whether this works for older versions.
Regards,
MikeJanuary 5th, 2010 at 12:16 am -
john said:
Thanks for the tip Mike, hopefully that was what he was looking for.
January 5th, 2010 at 10:14 pm
Loading ...
[...] Hyperlink Formula in Excel [...]