With the HYPERLINK function, you can enter a file path or URL with the sheet, cell, or defined name you want to link to. For example: “=HYPERLINK(“[HTG_Desktop.xlsx]Sheet1!A6″)” Insert display text to make your link friendlier.
Excel offers its own built-in linking feature in the toolbar. But the HYPERLINK function lets you do more, like a link to a workbook on your company intranet, shared server, another drive, or even a bookmark in a Word document. Let’s look at everything you can do with this versatile function.
What Is the HYPERLINK Function?
HYPERLINK in Excel is a function for creating clickable links to all sorts of places and objects. You could, of course, do some of the same things that HYPERLINK does using the Link tool in Excel. However, a formula gives you finer control of the link, and HYPERLINK is a simple function to learn that can get you comfortable with creating formulas in general. Additionally, HYPERLINK lets you link directly to a specific cell or defined name.
The syntax for the function’s formula is HYPERLINK(location, text) where only the first argument is required and contains the path to the Excel file.
You can use the second argument to display specific text as the link. Place text in quotation marks or use a cell reference. For example, instead of displaying the file path or URL, you can display “Click Here” or a value from another cell in the current sheet.
1. Link to a Cell in the Spreadsheet
To link to a certain cell in the current spreadsheet, you’ll include the file name, sheet name, and cell reference. Notice that you place the file name in brackets and the entire argument in quotes.
For example, with this formula, we link to cell A6 in our current spreadsheet named Sheet1. The file name is HTG_Desktop.xlsx. Remember to include the file extension, which is either .xls or .xlsx depending on your version of Excel.
=HYPERLINK("[HTG_Desktop.xlsx]Sheet1!A6")
To link to the same file, sheet, and cell, but use a friendly name for the link like “Go to A6”, you can use this formula:
=HYPERLINK("[HTG_Desktop.xlsx]Sheet1!A6","Go to A6")
2. Link to a Cell Within the Workbook
To link to a cell in the current workbook, but on a different sheet, simply replace the current sheet name with the other sheet’s name.
Here, we’ll link to cell B2 on Sheet2:
=HYPERLINK("[HTG_Desktop.xlsx]Sheet2!B2")
You can also insert the CELL function as the location argument rather than typing the file name. Here’s the formula for linking to the same cell as above:
=HYPERLINK(CELL("address",Sheet2!B2))
For this combined function formula, you can also enter display text for the text argument. Instead of adding text in quotes, we’ll use the value in cell D1 which is the word “Title”:
=HYPERLINK(CELL("address",Sheet2!B2),D1)
3. Link to a Defined Name
Maybe you’d like to link to a named range of cells in the current or another Excel workbook. In this case, you’ll add brackets around the path directly before the defined name.
To link to the defined name Scores in the current workbook titled HTG_Desktop.xlsx, you’d use the following formula:
=HYPERLINK("[HTG_Desktop.xlsx]Scores")
To link to the defined name Totals in a different workbook, you’ll enter the complete path in brackets followed by the cell range name like this:
=HYPERLINK("[C:UsersSandyDesktopMyDataEntryForm.xlsx]Totals")
To use something different for the display text, in either example, simply add it to the second argument:
=HYPERLINK("[HTG_Desktop.xlsx]Scores","Go There")
4. Link to a Workbook on Another Drive
Linking to an Excel file on a different drive on your computer is another option. You can also link directly to a cell or named range if needed.
To link to the file without a designated cell or defined name, include the complete path in quotes. Here, we’ll link to the file MyDataEntryForm.xlsx on the E drive.
=HYPERLINK("E:MyDataEntryForm.xlsx")
For a particular cell, we’ll link to C9 on Sheet1 in the same file and location. Add brackets around the path with the sheet name and cell reference afterwards.
=HYPERLINK("[E:MyDataEntryForm.xlsx]Sheet1!C9")
For another example, we’ll link to the named range Totals in the same file and location. Because you’re using a defined name, be sure to insert the brackets around the path. Here, we’ll include the display text Totals:
=HYPERLINK("[E:MyDataEntryForm.xlsx]Totals","Totals")
5. Link to a Workbook on the Web
Maybe the workbook you want to link to is on your company intranet or a website. You can link to a remote XLSX file by including the full path in quotes as follows:
=HYPERLINK("http://www.mysite.com/report.xlsx")
To link to a specific sheet and cell, include the file path in brackets with the sheet name and cell reference directly following.
=HYPERLINK("[http://www.mysite.com/report.xlsx],Sheet1!A7")
6. Link to a Word Document
If you want to integrate your documents with your spreadsheets, you can use the HYPERLINK function to link to a Word document. Plus, you can link straight to a bookmark you’ve created in Word.
To link to the Word document, include the full path in quotes and don’t forget the file extension. For Word, it’s either .doc or .docx depending on your version:
=HYPERLINK("C:UsersSandyDesktopMiscWorkDoc.docx")
To link to a bookmark in Word instead, you’ll add those brackets followed by the bookmark name. Here, we link to the same file as above but directly to the bookmark named Details and include the display text Report:
=HYPERLINK("[C:UsersSandyDesktopMiscWorkDoc.docx]Details","Report")
If you’re looking for an alternative way to create links in Excel, especially for a particular cell in another workbook or one on the web, keep the HYPERLINK function in mind.
For more, look at how to link to cells or spreadsheets in Google Sheets too!