How to use Excel HYPERLINK function to create different link types

This tutorial explains the basics of the Excel HYPERLINK function and provides a few tips and formula examples to use it most efficiently.

There are many ways to create a hyperlink in Excel. To link to a certain web page, you can simply type its URL in a cell, hit Enter, and Microsoft Excel will automatically convert the entry into a clickable hyperlink. To link to another worksheet or a specific location in another Excel file, you can use the Hyperlink context menu or Ctrl + K shortcut. If you plan to insert many identical or similar links, the fastest way is to use a Hyperlink formula, which makes it easier to create, copy and edit hyperlinks in Excel.

The HYPERLINK function in Excel is used to create a reference (shortcut) that directs the user to the specified location in the same document or opens another document or web-page. By using a Hyperlink formula, you can link to the following items:

  • A specific place such as a cell or named range in an Excel file (in the existing sheet or in another worksheet or workbook)
  • Word, PowerPoint or other document stored on your hard disk drive, local network or online
  • Bookmark in a Word document
  • Web-page on the Internet or intranet
  • Email address to create a new message

The function is available in all versions of Excel 365 - 2000. In Excel Online, the HYPERLINK function can only be used for web addresses (URLs).

The syntax of the HYPERLINK function is as follows:

HYPERLINK(link_location, [friendly_name])

Where:

  • Link_location (required) is the path to the web-page or file to be opened.

    Link_location can be supplied as a reference to a cell containing the link or a text string enclosed in quotation marks that contains a path to a file stored on a local drive, UNC path on a server, or URL on the Internet or intranet.

    If the specified link path does not exist or is broken, a Hyperlink formula will throw an error when you click the cell.

  • Friendly_name (optional) is the link text (aka jump text or anchor text) to be displayed in a cell. If omitted, link_location is displayed as the link text.

    Friendly_name can be supplied as a numeric value, text string enclosed in quotation marks, name, or reference to a cell that contains the link text.

Clicking a cell with a Hyperlink formula opens the file or web-page specified in the link_location argument.

Below, you can see the simplest example of an Excel Hyperlink formula, where A2 contains friendly_name and B2 contains link_location:

=HYPERLINK(B2, A2)

The result may look something similar to this:
Examples of using the HYPERLINK function in Excel

More formula examples demonstrating other uses of the Excel HYPERLINK function follow below.

Moving from theory to practice, let's see how you can use the HYPERLINK function to open various documents directly from your worksheets. We will also discuss a more complex formula where Excel HYPERLINK is used in a combination with a few other functions to accomplish a non-trivial challenging task.

The Excel HYPERLINK function enables you to insert clickable hyperlinks of a few different types depending on what value you supply to the link_location argument.

Hyperlink to another worksheet

To insert a hyperlink to a different sheet in the same workbook, supply the target sheet name preceded by a pound sign (#), and followed by the exclamation point and target cell reference, like this:

=HYPERLINK("#Sheet2!A1", "Sheet2")

The above formula creates a hyperlink with the jump text "Sheet2" that opens Sheet2 in the current workbook.

If the worksheet name includes spaces or non-alphabetical characters, it must be enclosed in single quotation marks, like this:

=HYPERLINK("#'Price list'!A1", "Price list")

In the same way, you can make a hyperlink to another cell in the same sheet. For example, to insert a hyperlink that will take you to cell A1 in the same worksheet, use a formula similar to this:

=HYPERLINK("#A1", "Go to cell A1")

Hyperlink to a different workbook

To create a hyperlink to another workbook, you need to specify the full path to the target workbook in the following format:

"Drive:\Folder\Workbook.xlsx"

For example:

=HYPERLINK("D:\Source data\Book3.xlsx", "Book3")

To land on a specific sheet and even in a specific cell, use this format:

"[Drive:\Folder\Workbook.xlsx]Sheet!Cell"

For example, to add a hyperlink titled "Book3" that opens Sheet2 in Book3 stored in the Source data folder on drive D, use this formula:

=HYPERLINK("[D:\Source data\Book3.xlsx]Sheet2!A1", "Book3")

If you plan to move your workbooks to another location soon, you can create a relative link like this:

=HYPERLINK("Source data\Book3.xlsx", "Book3")

When you move the files, the relative hyperlink will continue working as long as the relative path to the target workbook remains unchanged. For more information, please see Absolute and relative hyperlinks in Excel.

Hyperlink to a named range

If you are making a hyperlink to a worksheet-level name, include the full path to the target name:

"[Drive:\Folder\Workbook.xlsx]Sheet!Name"

For instance, to insert a link to a range named "Source_data" stored on Sheet1 in Book1, use this formula:

=HYPERLINK("[D:\Excel files\Book1.xlsx]Sheet1!Source_data","Source data")

If you are referencing a workbook-level name, the sheet name does not need to be included, for example:

=HYPERLINK("[D:\Excel files\Book1.xlsx]Source_data","Source data")

Hyperlink to open a file stored on a hard disk drive

To create a link that will open another document, specify the full path to that document in this format:

"Drive:\Folder\File_name.extension"

For example, to open the Word document named Price list that is stored in the Word files folder on drive D, you use the following formula:

=HYPERLINK("D:\Word files\Price list.docx","Price list")

Hyperlink to a bookmark in a Word document

To make a hyperlink to a specific location in a Word document, enclose the document path in [square brackets] and use a bookmark to define the location you want to navigate to.

For example, the following formula adds a hyperlink to the bookmark named Subscription_prices in Price list.docx:

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

Hyperlink to a file on a network drive

To open a file stored in your local network, supply the path to that file in the Universal Naming Convention format (UNC) that uses double backslashes to precede the name of the server, like this:

"\\Server_name\Folder\File_name.extension"

The below formula creates a hyperlink titled "Price list" that will open the Price list.xlsx workbook stored on SERVER1 in Svetlana folder:

=HYPERLINK("\\SERVER1\Svetlana\Price list.xlsx", "Price list")

To open an Excel file at a specific worksheet, enclose the path to the file in [square brackets] and include the sheet name followed by the exclamation point (!) and the referenced cell:

=HYPERLINK("[\\SERVER1\Svetlana\Price list.xlsx]Sheet4!A1", "Price list")

Hyperlink to a web page

To create a hyperlink to a web-page on the Internet or intranet, supply its URL enclosed in quotation marks, like this:

=HYPERLINK("https://www.ablebits.com","Go to Ablebits.com")

The above formula inserts a hyperlink, titled "Go to Ablebits.com", that opens the home page of our web-site.

Hyperlink to send an email

To create a new message to a specific recipient, provide an email address in this format:

"mailto:email_address"

For instance:

=HYPERLINK("mailto:support@ablebits.com","Drop us an email")

The above formula adds a hyperlink titled "Drop us an email", and clicking the link creates a new message to our support team.

When working with large datasets, you may often find yourself in a situation when you need to look up a specific value and return the corresponding data from another column. For this, you use either the VLOOKUP function or a more powerful INDEX MATCH combination.

But what if you not only want to pull a matching value but also jump to the position of that value in the source dataset to have a look at other details in the same row? This can be done by using the Excel HYPERLINK function with some help from CELL, INDEX and MATCH.

The generic formula to make a hyperlink to the first match is as follows:

HYPERLINK("#"&CELL("address", INDEX(return_range, MATCH(lookup_value, lookup_range,0))), INDEX(return_range, MATCH(lookup_value, lookup_range,0)))

To see the above formula in action, consider the following example. Supposing, you have a list of vendors in column A, and the sold products in column C. You aim to pull the first product sold by a given vendor and make a hyperlink to some cell in that row so you can review all other details associated with that particular order.

With the lookup value in cell E2, vendor list (lookup range) in A2:A10, and product list (return range) in C2:C10, the formula takes the following shape:

=HYPERLINK("#"&CELL("address", INDEX($C$2:$C$10, MATCH($E2,$A$2:$A$10,0))), INDEX($C$2:$C$10, MATCH($E2,$A$2:$A$10,0)))

As shown in the screenshot below, the formula pulls the matching value and converts it into a clickable hyperlink that directs the user to the position of the first match in the original dataset.
Vlookup and create a hyperlink to the first match

If you are working with long rows of data, it might be more convenient to have the hyperlink point to the first cell in the row where the match is found. For this, you simply set the return range in the first INDEX MATCH combination to column A ($A$2:$A$10 in this example):

=HYPERLINK("#"&CELL("address", INDEX($A$2:$A$10, MATCH($E2,$A$2:$A$10,0))), INDEX($C$2:$C$10, MATCH($E2,$A$2:$A$10,0)))

This formula will take you to the first occurrence of the lookup value ("Adam") in the dataset:
Vlookup and insert a hyperlink to the first occurrence to the lookup value

How this formula works

Those of you who are familiar with the INDEX MATCH formula as a more versatile alternative to Excel VLOOKUP, have probably already figured out the overall logic.

At the core, you use the classic INDEX MATCH combination to locate the first occurrence of the lookup value in the lookup range:

INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

You can find full details on how this formula works by following the above link. Below, we will outline the key points:

  • The MATCH function determines the position of "Adam" (lookup value) in range A2:A10 (lookup range), and returns 3.
  • The result of MATCH is passed to the row_num argument of the INDEX function instructing it to return the value from the 3rd row in range C2:C10 (return range). And the INDEX function returns "Lemons".

This way, you get the friendly_name argument of your Hyperlink formula.

Now, let's work out link_location, i.e. the cell the hyperlink should point to. To get the cell address, you use the CELL("address", [reference]) function with INDEX MATCH as reference. For the HYPERLINK function to know that the target cell resides in the current sheet, concatenate the cell address with the pound character ("#").

Note. Please notice the use of absolute cell references to fix the lookup and return ranges. This is critical if you plan to insert more than one hyperlink by copying the formula.

As mentioned in the beginning of this tutorial, one of the most useful benefits of formula-driven hyperlinks is the ability to edit multiple Hyperlink formulas in one go by using Excel's Replace All feature.

Let's say you want to replace the old URL of your company (old-website.com) with the new one (new-website.com) in all hyperlinks on the current sheet or in the entire workbook. To have it done, please follow the steps outlined below:

  1. Press Ctrl + H to open the Replace tab of the Find and Replace dialog.
  2. In the right-hand part of the dialog box, click the Options button.
  3. In the Find what box, type the text you want to change ("old-website.com" in this example).
  4. In the Within drop-down list, select either Sheet or Workbook depending on whether you want to change hyperlinks on the current worksheet only or in all sheets of the current workbook.
  5. In the Look in drop-down list, select Formulas.
  6. As an extra precaution, click the Find All button first, and Excel will display a list of all formulas containing the search text:
    Find all formulas containing the search text.
  7. Look though the search results to make sure you want to change all of the found formulas. If you do, proceed to the next step, otherwise refine the search.
  8. In the Replace with box, type the new text ("new-website.com" in this example).
  9. Click the Replace All button. Excel will replace the specified text in all found hyperlinks and notify you how many changes have been made.
    Change multiple hyperlinks at a time.
  10. Click the Close button to close the dialog. Done!

In a similar fashion, you can edit the link text (friendly_name) in all Hyperlink formulas at the same time. When doing so, be sure to check that the text to be replaced in friendly_name does not appear anywhere in link_location so that you won't break the formulas.

The most common reason for a Hyperlink formula not working (and the first thing for you to check!) is a non-existent or broken path in the link_location argument. If it's not the case, check out the following two things:

  1. If the link destination does not open when you click a hyperlink, make sure the link location is supplied in the proper format. Formula examples to create different hyperlink types can be found here.
  2. If instead of the link text an error such as VALUE! or N/A appears in a cell, most likely the problem is with the friendly_name argument of your Hyperlink formula.

    Typically, such errors occur when friendly_name is returned by some other function(s), like in our Vlookup and hyperlink to the first match example. In this case, the #N/A error will show up in the formula cell if the lookup value is not found within the lookup table. To prevent such errors, you may consider using the IFERROR function to display an empty string or some user-friendly text instead of the error value.

This is how you create hyperlinks using the Excel HYPERLINK function. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel Hyperlink formula examples (.xlsx file)

Table of contents

73 comments

  1. want to mass update hyperlinks connected to network drive.

    Spreadsheet is stored on SharePoint and contains information about certain videos I have stored on a network drive.
    If I use the =HYPERLINK formula and type the full path of where the video is stored on the network drive, link name e.g.
    =HYPERLINK(\\192.168.0.6\video_folder\video1.mp4,VIDEO1) then the link works fine.

    all videos are stored in \\192.168.0.6\video_folder\ just the name of the video changes

    I have tried to use & and CONCATENATE with the HYERLINK formula to try and mass update all other rows and it fail everytime

    Have to use IP address as not everyone accessing the network drive will do so using the same drive letter. Makes no difference if I use the server name instead of the IP address

  2. Hello,

    I have an excel workbook that acts as a visual navigation tool to various documents within a SharePoint site. For example, a user clicks on a specific area/region/location etc to drill down to certain documentation based on their navigation choices.

    Due to the amount of documentation that is stored in SharePoint, I have added a worksheet for a Glossary which I want to have as the main link source for the workbook so that when I update the hyperlinks to the documents in the Glossary, these links will also update in other areas of the workbook.

    I have tried using the 'equals' the specific cell formula, however, this does not provide me with a hyperlink to click to the document, I have also tried the '=Hyperlink' formula to the relevant cell in the Glossary page, however, this tells me "This site can’t be reached....."

    SIMPLE EXAMPLE
    User clicks a country (England, Ireland, Scotland, Wales) from the home sheet (lets say Sheet 1)
    If the user clicks England, they go to sheet 2 which provides more options (Cheshire, Leicestershire, Birmingham, Manchester etc)
    The user is then presented with more options on sheet 3 (e.g. population, transport, etc)
    If the user clicks 'population', they are then presented with various documentation that they can open from SharePoint by clicking a hyperlink (its this hyperlink that I want to update if I update the hyperlink in the main source within the Glossary worksheet) rather than having to update the hyperlinks all throughout the workbook as there will be various ways in which the user can navigate to the specific documentation

    Hope this makes sense, if anyone can help please :)

    • Hi!
      You can create a dynamic hyperlink using a formula like this

      =HYPERLINK("#'"&A1&"'!"&B1)

      A1 - "Sheet1" (worksheet name)
      B1 - "C1" (cell address)

  3. I have a spreadsheet with new tabs that i copy and paste to update every month. Those tabs have links within the sheet that link to other locations in that SAME sheet ('Place in This Document'). I am looking for a simple way to update those links without clicking on the cell each month and updating the hyperlink.

    I have tried Find and Replace and that does not work. I am assuming i need to do VBA coding but i'm not sure what i need to do.

    Thanks!!

    • Hi!
      If I understand the problem correctly, try using a cell reference that contains the link's address.

      =HYPERLINK(E1,"Link to A1")
      E1 --- #A1

  4. Looking for a little help.

    I have a macro that creates a new sheet (we'll call it '221234-1') based on a particular cell value, then hides the new sheet and returns back to the starting sheet. Back at the starting sheet, a small icon is copied/pasted in a specific location. What I'm needing is the VBA to hyperlink the icon to that sheet ('221234-1') based on that same cell value. Basically, the cell value will change each time a new sheet is create ('221234-2' '221234-3' '221234-4' and so on) so the VBA will need to look back to that cell (EV36) to get the worksheet address of the new worksheet that was just created.

  5. I created an excel sheet for my website passwords. It has a Website Name in Column A and Website URL in Column B.
    I only want the url hyperlink under the website name. I can change the Text to Display under LINK options, but it is time consuming. Is there a script that will do it all at once? I did try a VBA script but it still only changes one at a time.

    • Hello!
      Use a VBA macro to display all hyperlinks in an adjacent column.
      Sub HyperlinkExtract()
      With ActiveSheet
      For I = 1 To .Hyperlinks.Count
      .Hyperlinks (I).Range.Offset (0,1).Value = .Hyperlinks (I).Address
      Next I
      End With
      End Sub

  6. Hello!

    I was hoping to have some help with creating a recurring formula to link to multiple different worksheets within the document.

    =HYPERLINK("#Sheet2!A1", "Sheet2")

    So this, but instead of naming the sheet (Example sheet name: 244) in the formula, to instead tell the formula to look at cell A3 which contains the the title of the worksheet (244).

    Then again, wanting to name the hyperlink as according to the same cell, A3, so it returns with the name of the sheet as the Hyperlink (244)... visual example...

    A B
    No# Link
    3 244 =hyperlink("#(LINK TO A3)!A1","(LINK TO A3)")
    4 245 =hyperlink("#(LINK TO A4)!A1","(LINK TO A4)")
    5 246 =hyperlink("#(LINK TO A5)!A1","(LINK TO A5)")
    6 247 etc.

    Thanks!

  7. I need help. I am hoping there's a formula or someway to do what I am needing to do on a spreadsheet. A temp employee (who is no longer with us) created a spreadsheet for our boss with some info she wanted in hyperlink form. The spreadsheet has over 400 "hyperlinks" but the issue is the person who created it didn't do the hyperlinks correctly.

    Here's 2 example of what the hyperlink should look like (using generic websie info)..Boss told the temp he could do it either way
    Option 1: ht tp://www.clickme.com/vinyl1
    Option 2: Vinyl Color 1 (when you click on it it opens to ht tp://www.clickme.com/vinyl1

    His "hyperlinks" look like this 1, ht tp://www.clickme.com/vinyl1 2. ht tp://www.clickme.com/vinyl2 (you get the drift) and they don't even show up as hyperlinks. If I go into each cell and delete the info before the http part like 1. or 2. and then hit enter it turns into a clickable hyperlink. I need a way to do them all at once instead of doing all 400+ one at a time.

    I bet this is the reason he didn't show back up to work the day after sending them to our boss.

    Help!

    Thanks!

    Corryn

    • Hello!
      The information you provided is not enough to understand your case and give you any advice, sorry. Please specify exactly what formula is written in these cells. Then I'll try to help.

      • If other programs created the excel file with correct formula for hyperlink, the cell only display the formula. You still need to click each cell then hit enter to make it clickable. Is there a way to avoid this step? Image some computer program created 10K hyperlinks in a sheet, there is no time to click each one to convert it from formula to links.

  8. I have 2 excel sheets one with old prices and one with new updated prices.

    Both with a SKU, title and price [old] and [new]

    I need to compare skus and if they match then move compare prices, if there has been an increase in price in [new] it needs to update this price in [old]

    Each sheets holds around 3000 items so an automatic formula would be great please, can you help.

    Thanks in advance

  9. Hello,
    Thanks for the formula tip for linking to a bookmark in a Word doc! The template you provided works! Pasted below for reference:

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

    I am attempting to batch create links based on the inputs. For example, I'm replacing the Bookmark_Name ("Subscription_prices") with a xlookup formula, and replacing the File Name ("Price list.docx") with another xlookup formula. But for some reason this breaks the link to the bookmark. The link still functions to the Word file, but doesn't bring you to the specific bookmark within the Word file.

    Any ideas how to make this work?

    • Hi!
      I can't see your data and can't guess which XLOOKUP formula you are using. Therefore I can't check your formula. Check what data is returned by your formula. There may be extra spaces or other characters.

  10. Hello,

    Is there a way I can make an index with hyperlinks works when saved as pdf?

    I want the links to work in pdf, not necessarily to work in excel, but should be done in excel.

    Thank you

  11. I have a document where i get live data from another document via power query, its a big dataset of over 500 projects (Rows).
    I have then made a Gantt chart in another sheet in the document, and I would like to make a link on the far left were i can press (item number) to get to the specific line where the data that i get through power query is on the project within the same workbook.

    Whenever a new project is added in the original document where the original data is at, it is sorted by name of item number, which means that a newly added project might become number 344 or something which pushes every project with a higher number than 344, 1 row down.. therefore when i add hyperlinks, it will quickly be moved around and the links will refer to wrong cells in the worksheet.

    Can i make the hyperlink refer to a specific value in a cell instead of the actual cell, so instead of hyperlinking to cell A1 or whatever then hyperlink to the item number value in the other worksheet? so something like #04815.
    or do you have any suggestion on some other fix of the moving projects part? :)

    also, for each imported project, it fills 1 row, but in the Gantt chart sheet i have added 2 rows for each project, and it understands to get the data and I'm able to drag and drop it all the way down using this formula:
    =I$2>INDIRECT(ADDRESS(FLOOR.MATH((ROW(GANTT!$B2)-1)/2+2;1);28;;;"GANTT"))
    Where I$2 is todays date. And the GANTT!$B2 is referring to the data sheet called GANTT.

    do you have any suggestions on making the hyperlinking draggable where it skips every other row in the Gantt sheet? :)

    Also, I need to make draggable hyperlinks referring project number to a folder called the same as the project number. any notes on how to make this work?

    I am able to add hyperlinks by using formulas and the hyperlink button in excel, i don't have any experience using VBA, but if you think you know how to make a code to make any of this work, I'm willing to use time to try to get to understand it. None the less any help is appreciated

    Kind regards

  12. For example, to open the Word document named Price list that is stored in the Word files folder on drive D, you use the following formula:

    =HYPERLINK("D:\Word files\Price list.docx","Price list")

    Please help, what is the additional function/formula if the file is not available in the drive but I want to appear as "File Not Found!" in the cell?

      • Thank you, can you share with me please the function?

  13. How to automatically derive a particular data by importing delimited data from another tool.
    Example. I have 6 categories of rooms and their revenue report from another tool as delimited data,
    I need only 4 category room revenue in my main excel sheet.

  14. Thank you Svetlana, this post is perfect for me to fill gaps with hyperlink function knowledge.

  15. How can I combine 3 different cells in one which contains texts with a hyperlink? when I do this hyperlinks disappeared

  16. Just curious, is there a way to assign the hyperlink to an Icon or shape?

  17. HI
    I have an excel worksheet and I need to hyperlink to documents that are labelled the same name but to the latest revision in bulk.
    example.
    1010-10-001r0
    1010-10-001r1
    1010-10-001r2
    1544-10-001r1
    1544-10-001r2
    I have a folder of hundreds of pdfs labelled like this example and I want to run some code to hyperlink to my spreadsheet from the folder of pdfs.
    thanks

  18. How to operate using hyperlink?

  19. 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

  20. 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)

    • 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")

  21. 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.

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

  23. 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

    • 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")

  24. 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

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

  26. 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)

  27. 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?

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

  29. 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

  30. 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.

  31. 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.

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

  33. 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)

  34. 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.

  35. 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

  36. 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?

  37. =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..

  38. 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!

  39. 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

  40. 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

  41. =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

  42. 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

  43. 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

  44. 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!!

    • Hi Lisa,

      Were you able to get this to work?

      Thanks!

  45. as tag a href

  46. 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?

  47. 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

  48. Very nice Article! Thanks

  49. 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 :)!

  50. 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 :)