Jun 12 2007

Hyperlink Formula in Excel

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”)




[ad#in-post-ad]

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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

50 Comments on this post

Trackbacks

  1. Top Posts in 2008 | Excel Hints wrote:

    [...] Hyperlink Formula in Excel [...]

    January 1st, 2009 at 4:52 pm
  2. Using VBA to grab a URL address and place in a link on and Excel Worksheet. wrote:

    […] wouldn't need the list of urls. That was my situation and worked nicely. According to this post: Hyperlink Formula in Excel this method will work in Excel 2007 as well. answered Aug 1 '12 at 18:24 marty […]

    April 3rd, 2014 at 1:04 am
  1. 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
  2. J said:

    You need a macro as seen here

    http://www.ozgrid.com/VBA/HyperlinkAddress.htm

    April 29th, 2009 at 11:07 pm
  3. ALEX said:

    there no examples. pls provide so i could understand more

    June 18th, 2009 at 10:02 pm
  4. 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
  5. 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
  6. 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
  7. 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
  8. john said:

    Check out this article from microsoft…. looks like some registry edits are necessary to make this work:

    http://support.microsoft.com/kb/829072

    September 11th, 2009 at 9:02 am
  9. 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
  10. 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
  11. john said:

    Did you update the location of the file address in the hyperlink as well?

    September 14th, 2009 at 8:53 am
  12. 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
  13. 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.xls

    September 14th, 2009 at 11:27 pm
  14. 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
  15. john said:

    Thanks for the help with Doni’s question. Great suggestion.

    John

    September 18th, 2009 at 2:52 pm
  16. 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
  17. 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.tif

    Do 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 You

    October 22nd, 2009 at 8:19 am
  18. 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
  19. 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
  20. 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,
    Mike

    January 5th, 2010 at 12:16 am
  21. john said:

    Thanks for the tip Mike, hopefully that was what he was looking for.

    January 5th, 2010 at 10:14 pm
  22. MO$ said:

    DOES ANY ONE KNOWS HOW TO PRINT WORD DOCUMENT FROM EXCEL USEING HYPLERLINK TO WORD DOCUMENT. BUT WITHOUT OPENING WORD DOCUMENT AND NUMBER OF COPIES BY ENTERING QTY IN EXCEL CELL NEXT TO HYPERLINK.
    THANKYOU…

    September 23rd, 2010 at 1:39 am
  23. JG85 said:

    I’m wanting to add a hyperlink from one cell to several cells that are not in succession. (E.g. If I click A1, I want it to highlight M3, M5 and M9).

    Is there a way to make this happen?
    Thanks.

    November 17th, 2010 at 9:38 am
  24. Hannah said:

    I have lots of hyperlinks to new sheets. On the mainsheet I want to add a formula so I can use a cell within the hyperlinked sheet. But I want the formula to be used on each line for each different hypelink but link to the same cell reference with in each cell.

    December 17th, 2010 at 11:28 am
  25. Hendrik said:

    I have 2 sheets. Sheet one has order no’s, with basic information to about the orders. sheet 2 has detailed information about each order. I have hyperlinked the location of each order no. in sheet 2 on sheet 1. problem is when i change the order of sheet 2 (alphabetize for example) then the hyperlink doesnt change to indicate the new cell where the order no. is after changing the order.
    is there anyway to do this?

    February 14th, 2011 at 6:18 am
  26. Ivan said:

    Hello Guys,

    I read everything you have been posting.
    I found your knowledge extremely helpful.
    I am not a computer savvy person and wanted to ask you for your help.
    Just wondering whether there is a way to create hyperlinks for files that are often being updated by an administrator and how can I synchronize this?

    I am not sure my question is clear so I am going to explain my problem.
    I am a real estate agent and work with around 600 buildings.
    In our data base e have “updates” for each building. For each building it is usually a word file or pdf that the management companies send to our administrator and then she puts them in our data base.
    NOW, I created an excel chart with all these buildings and across each one of them I want to put a hyperlink so that I can save time and check for updates easy. (instead of going to the data base every time for every different bldg).

    MY PROBLEM IS that I can only create a hyperlink to an existing SAVED on my computer file. i want to sync it to the main system so that every time our admin updates a file , when I click on my url I will have the updated file, NOT one that I have created lets say a month ago.
    Basically i want my hyperlinks to be connected to our data base.
    can you help me?

    February 20th, 2011 at 5:31 pm
  27. Amanda said:

    How do I transfer a column of email address to become actual hyperlinks?

    April 5th, 2011 at 12:26 pm
  28. Jas said:

    I have 1 main Sheet which contain Month & date.
    Now i have multiple sheet also which jan,feb,mar……
    I want hyperlink if i clik on this link will automatically go to specific date in a Specific month which i want

    April 29th, 2011 at 7:57 am
  29. randy23m said:

    Hello,

    Here is what I’m trying to do:

    1. In cell A1 I have the text Science
    2. In cell A2 I want to create a button that hyperlinks to google

    I have accomplished these two steps easily, but what I would like the button to do, is open google and then copy the text from cell A1, paste it into the URL referenced from the button in A2 and paste it into google automatically for me. Additionally, I would like the search to be automatically performed on the text in cell A1.

    Any clues on how I can accomplish this.

    Thanks in advance!

    May 6th, 2011 at 8:53 pm
  30. Koos said:

    I’d still like to know how to accomplish what BD was asking for earlier, only I’m using hyperinks to the web, so the answer provided by Micromutt won’t work for me.

    I have titles of web articles in one column, and then the respective URLs in the one next to it. Now I want to create a hyperlink for the cells with the article names, using the URLs in the adjacent column so by clinking the article name will lead you to the actual article on the web. Again, just like in BD’s case, there’s a LOT of rows in my worksheet so I need a macro or another way to speed up this process. Can anyone help?

    June 10th, 2011 at 4:22 am
  31. ashish said:

    I am working with excel. In a cell there are 2-3 comments out of which I want to hyperlink to only one comment or url is it possible?
    eg. if in cell have following comments:
    1: How are you?
    2: What are you doing?
    3: check url: http://www.whatever.com

    my issue is I want ot hyerlink to only “http://www.whatever.com” this part. Is it possible? PLease reply. Thanks.

    June 20th, 2011 at 4:10 am
  32. Naval said:

    Hello,
    please send me some formulas of Excel.

    June 29th, 2011 at 6:30 am
  33. Lois said:

    Hi, I am trying to create a hyperlink in Word that goes to a specific worksheet in an Excel file. I am using the following string:
    myfile.xls#’sheet12′

    , but I keep getting “reference is not valid” message. Any help would be appreciated. Thanks!

    July 8th, 2011 at 12:20 pm
  34. Lois said:

    So I figured it out as soon as I posted, here is the syntax in case anyone else needs it:

    “filename.xls”#”name of sheet””(area)”

    example
    cpw.xls#Billing!$1:$1048576

    July 8th, 2011 at 12:40 pm
  35. Paul said:

    I’m trying to use an IF function with the HYPERLINK function so that if I enter the formula in cell A1 it will only create a hyperlink if I enter a date in B1.

    My formula is in the following format:

    =IF(B1=””,””,HYPERLINK(CONCATENATE(“./foldername/”,C1,”.pdf”),”C1″))

    It works, and I either get a blank cell or my hyperlink as I want, BUT the blank cell also has the hyperlink there (the little hand cursor appears when I mouse over it). I can’t figure out why – any ideas?

    August 23rd, 2011 at 4:47 am
  36. Neveille Mehta said:

    Hi
    Getting the error in function as below
    Find below path not able to open Excel Linked file Reason would be “Datagrp” folder appeared twice in link.
    \\ntbomfs001\DATAGRP\DATAGRP\FINANCE

    Can anyone give the solution

    October 19th, 2011 at 4:42 am
  37. Rajesh said:

    Hyperlink Formula in Excel
    I want to apply this in a sheet where there are many records. So, is there any way we can identify the rows, where the hyperlink is invalid (which means there is no such file to open)? Expecting a solution for this.
    Regards
    RM

    October 25th, 2011 at 6:33 am
  38. avma said:

    Hi,

    Using the hyperlink functionality, is it possible to embed in the hyperlink text from a cell in excel?

    For example:
    I have the following in the hyperlink address:
    http://en.wikipedia.org/wiki/Juillan
    Is it possible to read ‘Juillan’ from cell F6 when A6 contains ‘Juillan’ ?

    Thanks

    November 21st, 2011 at 10:18 am
  39. jp said:

    @avma

    =HYPERLINK(“http://en.wikipedia.org/wiki/”&F6,F6)
    where F6 contains the test “Juillan”

    December 21st, 2011 at 2:01 pm
  40. cal said:

    regarding the last comment is there a way to just get the link http://en.wikipedia.org/wiki/ from the formula? I want to get the link from a bunch of cells that are using this formula.

    January 13th, 2012 at 3:30 pm
  41. maha said:

    how to link a word file in excel and to
    go to a particular page

    February 2nd, 2012 at 10:53 am
  42. Eirik said:

    Hi.

    I need to link up about 800 employees with their picture. The picturefile is in the format “employee number”_First Name”_”Lst Name”.jpg
    e.g. 12345_Tom_Thomson.jpg.

    I have the unique employee number in a cell. Is there any way to make a hyperlink that only looks at the first five digits or chars in the filename? Eg. hyperlink(./12345.jpg; “Link”)?

    February 7th, 2012 at 7:00 am
  43. abdulali said:

    Hai

    Please explain about pivot table

    ali

    February 19th, 2012 at 11:29 am
  44. Ray Lynch said:

    Hi There.
    I have a document register located on a file server with 3 columns each of which contain hyperlinks to documents contained in three separate subfolders within a job folder.
    As I enter the file names into the columns on my register and then hyperlink them, I have to navigate two steps up the folder tree and then two steps back down in order to access the folder with the document I am hyperlinking to. This is very tedious and due to the folders being housed on a remote server accessed via VPN it’s very slooooooooow!
    Is it possible to make the Hyperlink Dialogue box open in the correct folder without having to do the two step up and down dance described above?
    Thanks.

    April 4th, 2012 at 7:58 pm
  45. Ray Lynch said:

    I also have another issue relating to hyperlinking. It seems that regardless of the format of the cell that I create a hyperlink in (say, Gill Sans MT 8pt) the hyperlink appears as Arial 10pt. Is it possible to make the hyperlink respond to the cell format or do I have to retrospectively change it every time?

    April 4th, 2012 at 8:18 pm
  46. abdulali said:

    A1 = 8
    B1 = -3
    C1 = 8

    That is if the cell value is A1,B1 is less than 0
    C! = Consider 0 (zero) sum is to be 8

    May 12th, 2012 at 11:04 am
  47. pupung said:

    Hi,

    I also have a common problem with BD, but my concern is a little bit unusual… I have too much PDF files to Link in excel. Is there any formula/command that will be automatically hyperlinked when i dragged the first cell?

    Column1 Column2

    TP-11-000-WSR-001 TP-11-000-WSR-001.pdf
    TP-11-000-WSS-001 TP-11-000-WSS-001.pdf
    TP-11-000-WSS-0025 TP-11-000-WSS-0025.pdf

    i also want to know how am i going to link this pdf files if the column 1 has an existing datas then i need to hyperlink the scanned files to be shown in column2?

    thank you very much.

    June 12th, 2012 at 5:43 am
  48. saj said:

    same we can do by just right cliking mouse and select “hyperlink” and link the path
    whats the differance ???

    November 20th, 2012 at 2:56 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories