Comments on: How to use Excel HYPERLINK function to create different link types

There are many ways to create a hyperlink in Excel. If you plan to insert many identical or similar hyperlinks, the fastest way is to use a Hyperlink formula, which makes it easier to insert, copy and edit hyperlinks. Continue reading →

Table of contents

Comments page 2. Total comments: 73

  1. Hi,
    I created hyperlinks in my excel workbook to link to a specific place in the same workbook, i.e. the note numbers in the balance sheet of the financial statements are hyperlinked to the actual note disclosure. However, every time I insert some new lines into the workbook, the hyperlink does not automatically update it's reference to the new cell. How can I fix this? It is very time-consuming to update each hyperlink again after new rows inserted.

    Please advise

  2. I'm having the same problem as Lisa (post 7. When I use the hyperlink function to point to a cell on another sheet in the same workbook, the hyperlink doesn't adjust dynamically with the cell when it is moved. I.E. I use the formula =HYPERLINK("#'Calcs. Form'!D12","1-100"), but if I delete cell D11 on the 'calcs. form' and my 'D12' cell moves up, my hyperlink still points to D12 instead of moving up to D11. The only solution I have found is to actually give the cell a unique name, via define name, and point a hyperlink at the name. Suggestions? I'm I just missing something in my formula? I don't have the D12 set as absolute. ($D$12)

    1. Hello!
      I think the best way is to give your cell a unique name. Then use this name in the formula

      =HYPERLINK("#Calcs.Form!Cell_D12")

  3. Hi, i prepare one excel sheet hyperlink for my projects its more than 300 projects now my hyperlink is not working when i clink any project name in excel sheet its showing "can't open specified file" like that can you help me? to solve this issue.

  4. I have many sub folder, i want link exact match in colum A:A find, pls give sub folder view folmula

  5. Hi, i have dought on hyperlink, i will use concetate fonction using in herperlink, colums have same file name, i will use above function to view the listing link in another colums , it's working, but i cat link sub folders, give any idea,
    =Hyperlink(contecate("//file path/",file name or cell value,"file type"),"friendly name"))this formula i have using, pls give any suggestions for sub folders

    1. Hi,
      This should work fine to link to sub-folders, as long as you are specifying the correct full file-path and ending it with "\" in place of the filetype. e.g. if cell A1 contained the name of the subfolder that I wanted to open instead of a file, then I would use =hyperlink(concatenate("\\filepath\",A1,"\"),"Friendly name")

  6. Sir,
    I have a file containing Invoice No. ( Manually enter in Sheets ) with different file ( Excel file ) name in different directory .
    I want to assign a hyperlink to respective Invoice no , what is the short cut for the same.
    For Example
    Excel file contains Column A - 1 to 10 numbers
    and in one of directory contain 1 to 10 excel file ( Invoice format ) how to hyperlink at a time when i click 1 in column A it should show me INVOICE NO. 1 Excel file

  7. how to change bulk txt into hyperlink at one click

  8. Hi,
    Please, could anyone help me out with the hyperlink? Is there any way to add hyperlinks for 50 excel files in one go.
    Example
    In the Excel sheet.
    1)Invoice A Link (A Excel file.xlsx
    2)Invoice B Link (B Excel file.xlsx
    3)Invoice C Link (C Excel file.xlsx
    Continue till 50)

  9. I have around 800 PDF files that I need to attach to the excel sheet as hyperlinks. Is there a way that I can batch insert these files to the sheet and not have to go through manually inserting these items to the cells?

  10. Brilliant. That saved me a HUGE amount of time.
    Thank you so much!

  11. I'm using Office 365 and trying to insert a conditional hyperlink using the if statement but it doesn't work. Because I have many columns in my data sheet, I want to use the if statement to quickly look at a particular cell, if it meets the condition insert a hyperlink to another cell in the same sheet. The formula I am using is: =if(CF1="Blocked",hyperlink(CG1,"Blocked"),"")

    Not sure why it doesn't work - seems pretty straight forward

  12. I use Excel based work instructions. I have a hyperlink built in which imports a "User list" of names of active people who use the work instructions. The list gets imported automatically so if the given employee is done with a given operation, they can click a drop down box and select their name to sign off an operation.

    The problem I have alot, is that if I save the network location into the link path (Even though I set it as UNC!!!) to the "User list" and my network path is on "X" letter drive, Excel updates the link as letter drive, even when I have it set to UNC. Now when the operator opens it on their PC and the letter drive is different, the hyperlink is automatically now broken.

    How can I set the link not to be clicked on to open, I just want the name list automatically imported to the hidden list that populates the drop down menu. but I need Excel to update the link as UNC not letter drive. I fix this dozens of times a day.

  13. Nice article, thanks for that.
    Do you know if it's possible to edit screen tip for hyperlink() function? It's an option when hyperlink is added via Insert in ribbon but not sure if it's doable when a function is used.

  14. hi good evening,
    please advice hyperlink create folder & excel file.

  15. I'm having the same problem as Lisa (post 7. When I use the hyperlink function to point to a cell on another sheet in the same workbook, the hyperlink doesn't adjust dynamically with the cell when it is moved. I.E. I use the formula =HYPERLINK("#'Calcs. Form'!D12","1-100"), but if I delete cell D11 on the 'calcs. form' and my 'D12' cell moves up, my hyperlink still points to D12 instead of moving up to D11. The only solution I have found is to actually give the cell a unique name, via define name, and point a hyperlink at the name. Suggestions? I'm I just missing something in my formula? I don't have the D12 set as absolute. ($D$12)

  16. This post was exactly what I was looking for--thanks! I had thousands of URLs in a spreadsheet and I needed each one to display "Read More" instead of the displaying the whole URL.

  17. All hyperlinks in my Excel file have Changed to the following path
    C:\Users\hamidreza\AppData\Roaming\Microsoft\Excel
    The main path is on the network drive map \\filesrv\...
    This change of course has happened suddenly.
    1-Why is this happening?
    2-How to correct the paths to the main path in the network drive?
    The number of Hyperlinks is high
    Please help.
    Thanks

  18. I have tried every variant from simple to complex and I don't even get an error message (#VALUE or N/A) returned, just the text of the formula as I typed it as if Excel 2007 doesn't even recognize it as a formula. Is the formula part of a toolpak or add-in I need to install?

  19. =HYPERLINK("[D:\Word files\Price list.docx]Subscription_prices","Price list")

    I want to inset cell reference i.e C3 at (subscription_prices) i.e bookmark place..
    Pls kindly help with respect to above..

  20. Hello! Can you provide a few examples for why someone would use the create a hyperlink that creates a pre-addressed email message feature in Excel. I'm going through the Excel 2016 exam 77/727 guide, and I'm having a little difficulty determining under what circumstances a user would use this feature.

    Any guidance would be appreciated. Thanks!

  21. Hi,
    I have an Excel file with different reference numbers in each cell (row) Hyperlinked as shown below. when I add new rows and drag the last reference number down I get reference numbers in serial but the hyperlink doesn't change. Every time I have to spend lot of time to change manually. IS THERE ANY WAY TO CHANGE THE HYPERLINKS AT A TIME??

    AL-TS-L-3580
    AL-TS-L-3581
    AL-TS-L-3582
    AL-TS-L-3583

    Please help.

    Thanks
    Johnson

  22. I am using hyperlink formula "HYPERLINK("#Bhoo!c6","")" When i am using this i am getting the result but i want this formula in the same cell and i tried this. ="270000"&HYPERLINK("#Bhoo!c6","")- not getting the result

  23. =IF(G23=0,"",HYPERLINK("mailto:"&R23&"?body="&$M$22&"%0A"&M23&"%0A"&$L$22&"%0A"&L23&"%0A"&$O$22&"%0A"&O23&"%0A"&$G$22&"%0A"&G23&"%0A"&$I$22&"%0A"&I23&"%0A"&$K$22&"%0A"&K23,"send"))

    HOW I AM ADD O23 CELL IN DATE FORMET IN MAIL

  24. I have cells with a link and text copied from a web page. The short cut shows in the cell and a mouseover the cell shows the link. Is there a way to separate these and put them in different cells for example:

    A1: Church Record Mouseover says: http:www.familysearch.org in popup

    A2: Church Record
    A3: http:www.familysearch.org

    Thanks,

    Gene

  25. Hi Svetlana ,
    How can I attach an Excel wk sheet rather than a lenthy writing
    Wk-sht has only 6-7 lines and the problem will be obvious at the first glance
    It is on HYPERLINK with Dymanic wk-sheet name
    It shows what works correctly - Non Dynamic
    But when made to be a Dynamic wk-sht name via INDIRCET() it failed miserably

    cheers
    kanil

  26. I have a hyperlink to a cell in the same worksheet, however, when a row is added or deleted, the hyperlink then points to the wrong cell. How can I make the hyperlink relative so it always points to the correct cell, regardless of how many rows are added or deleted? Thank you so much!!

    1. Hi Lisa,

      Were you able to get this to work?

      Thanks!

  27. as tag a href

  28. i have a column about mail address exp:m1@yahoo.com, 'd2@yahoo.com,....
    and want to replace column to "!m1", "@m1" how to set it?

  29. I have a formula which relates to a separate sheet in the workbook. I would like it to be hyperlinked to the other sheet. The problem is that the list is too long to do this individually. Is there a way I can add the hyperlink into the existing formula.

    =OFFSET('1st Quarter'!A$33,ROWS(A$33:A33)*31-31,0)

    Thanks is inticipation

  30. Very nice Article! Thanks

  31. Never mind...I was able to get an absolute reference to work only when a defined/named range is used instead of direct cell reference. For anyone who cares :)!

  32. Is there a way to create a basic hyperlink formula using an absolute cell reference within the same or a different workbook? I have tried adding the $ to cell reference in link location but it does not appear to work. Thanks!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)