THE ULTIMATE EXCEL FORMULAS GUIDE!

What does it do?

Creates a shortcut to a webpage, spreadsheet reference, or a file in the hard drive

Formula breakdown:

What it means:

The HYPERLINK Formula in Excel lets you dynamically create these!

I explain how you can do this below:

STEP 1: We need to enter the HYPERLINK function in a blank cell:

What is the exact link location?

Select the cell containing the link location:

### friendly_name

What will be the display name of the link?

Select the cell containing the display name. This gives your link a more presentable name:

Apply the same formula to the rest of the cells by dragging the lower right corner downwards.

How to Use the HYPERLINK Formula in Excel

Trustpilot

THE ULTIMATE EXCEL FORMULAS GUIDE!

Bryan is a best-selling book author of the 101 Excel Series paperback books.

## MS Excel: How to use the HYPERLINK Function (WS)

### Example (as Worksheet Function)

Let's look at some Excel HYPERLINK examples and explore how to use the HYPERLINK function as a worksheet function in Microsoft Excel:

In our first example, we're using the HYPERLINK function to reference a file called "Doc1.doc" that can be found in the c:\test\ directory. The following formula has been entered in cell A1:

When a user clicks on cell A1, the Word document called "Doc1.doc" will automatically open.

In our second example, we're using the HYPERLINK function to reference an Internet address. The following formula has been entered in cell A1:

When a user clicks on cell A1, the default web browser will open to the site called www.techonthenet.com.

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

### Excel HYPERLINK function - syntax and basic uses

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 for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000. In Excel Online, the HYPERLINK function can only be used for web addresses (URLs).

The syntax of the HYPERLINK function is as follows:

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:

The result may look something similar to this:

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

### How to use HYPERLINK in Excel - formula examples

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.

### How to link to sheets, files, web-pages and other items

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.

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:

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:

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 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:

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:

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

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:

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

#### 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 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 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:

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 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:

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:

For instance:

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

### Vlookup and create a hyperlink to the first match

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:

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:

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.

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

This formula will take you to the first occurrence of the lookup value ("Adam") in the dataset:

#### 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".

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.

### How to edit multiple hyperlinks at a time

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

### Excel HYPERLINK not working - reasons and solutions

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!

### Description

The HYPERLINK function creates a shortcut that jumps to another location in the current workbook, or opens a document stored on a network server, an intranet, or the Internet. When you click a cell that contains a HYPERLINK function, Excel jumps to the location listed, or opens the document you specified.

### Syntax

The HYPERLINK function syntax has the following arguments:

• Link_location    Required. The path and file name to the document to be opened. Link_location can refer to a place in a document — such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file that is stored on a hard disk drive. The path can also be a universal naming convention (UNC) path on a server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL) path on the Internet or an intranet.

Note   Excel for the web the HYPERLINK function is valid for web addresses (URLs) only. Link_location can be a text string enclosed in quotation marks or a reference to a cell that contains the link as a text string.

If the jump specified in link_location does not exist or cannot be navigated, an error appears when you click the cell.

• Friendly_name    Optional. The jump text or numeric value that is displayed in the cell. Friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location as the jump text.

Friendly_name can be a value, a text string, a name, or a cell that contains the jump text or value.

If friendly_name returns an error value (for example, #VALUE!), the cell displays the error instead of the jump text.

### Remark

In the Excel desktop application, to select a cell that contains a hyperlink without jumping to the hyperlink destination, click the cell and hold the mouse button until the pointer becomes a cross , then release the mouse button. In Excel for the web, select a cell by clicking it when the pointer is an arrow; jump to the hyperlink destination by clicking when the pointer is a pointing hand.

### Examples

Hyperlink formula in excel is used to create a shortcut path to quickly jump to any sheet, website or folder. We all have seen this even of many websites where a word highlighted in blue fonts consists of a hyperlink that would take us to some other location if we click on it. The same function works in excel, in the name of Hyperlink.

Syntax:

HYPERLINK () – It will return a shortcut of the given workbook references or URL, which used to be in blue colour and underlined. There is two-parameter – (link_location and [friendly_name]).

An argument in the Hyperlink Function:

• link_location: It is a mandatory parameter, the file or workbook reference that needs to be opened.
• friendly_name: It is an optional parameter, the name of the hyperlink created by the function. By default, it will take Link location as the friendly name.

### How to Use HYPERLINK Formula in Excel?

HYPERLINK Formula in Excel is very simple and easy. Let’s understand how to use the HYPERLINK Formula in Excel with some examples.

#### Example #1 – Basic Hyperlink Function

Let’s look at how to Use the basic Hyperlink Function for inside workbook references in Excel. There are some references given by a user with the name of the hyperlink name. He wants to create a hyperlink for all the references which are within the same workbook.

Let’s see how the Hyperlink Function can solve this problem. Open MS Excel, Go to Sheet1, where the user wants to create a hyperlink for all references.

Create one column header for the Hyperlink Result to show the function result in the C column.

Click on cell C2 and apply Hyperlink Formula.

Now it will ask for link location, select the reference given in B Column, select the cell C2.

It will then ask for a friendly name, which the user wants to give as the Hyperlink Name, available in A2 cell, writes in C2 cell.

Press the Enter key.

Drag the same formula to the other cell of the C column to find out the Hyperlink Result.

Summary of Example 1:

As the user wants to create HYPERLINK for all given references, the same the user achieved by the Hyperlink Function. Which is available in the D column as the Hyperlink result.

#### Example #2 – Outside Workbook References

Let’s find out how to use the Hyperlink function for outside workbook references like URLs and folder paths in Excel. There are some references given by a user with the name of the hyperlink name, which is either URLs or folder paths. He wants to create a hyperlink for all the references which are outside of the workbook.

Let’s see how the Hyperlink Function can solve this problem. Open MS Excel, Go to Sheet2, where the user wants to create a hyperlink for all references.

Create one column header for the Hyperlink Result to show the function result in column C.

Click on the C2 cell and apply Hyperlink Formula.

Now it will ask for link location, select the reference given in Column B, select cell B2.

Now it will ask for a friendly name, which the user wants to give as the hyperlink name.

Press the Enter key.

Drag the same function to the other cell of column C to find out the HYPERLINK result.

Summary of Example 2:

As the user wants to create HYPERLINK for all given references, the same the user achieved by the Hyperlink Function. Which is available in column D as the Hyperlink result.

#### Example #3 – Sending E-Mail

Let’s find out how to use the Hyperlink Function for sending E-Mail to someone in Excel.

An MS Health company wants to create a hyperlink to send welcome mail by clicking on the hyperlink in Excel. They have one format for the welcome mail, which they used to send every new employee who joins MS health Pvt Ltd. In E-Mail content, they have Email-Address, Subject, Mail Body, and Company Signature.

Let’s see how the Hyperlink Function can solve this problem. Open MS Excel, Go to Sheet3, where the user wants to create a hyperlink for all references.

Create one column header for the Concatenate mail content in the A6 cell.

Click on cell B6 and apply to CONCATENATE Function to join all mail content like email-id, subject, mail body, and signature.

Note:

•  “Mailto:” command to send the mail;
•  “?subject=” Command to write the subject;
• “& body =” – Command to write the mail body text;

Click on cell B7 and apply the Hyperlink Formula.

Now it will ask for link location; select the reference given in the B6 cell.

Now it will ask for a friendly name, which the user wants to give as the hyperlink name, available in cellA7 and cell B7.

Press the Enter key. Now click on the hyperlink to find out the HYPERLINK result.

A Window will open, as shown below.

Summary of Example 3: As the MS Health company wants to create a hyperlink to send a welcome e-mail by clicking on the hyperlink in the Excel on their own format for the welcome mail which they used to send every new employee who joins MS health Pvt Ltd. Same implement in the above example no 3.

### Things to Remember

• The Hyperlink Function will return a hyperlink, a shortcut of the given workbook references or URL, which used to be in blue colure and underlined.
• By default, it will take Link location as a friendly name.
• If a user wants to select all the hyperlinks without going to the link location, just use the arrows key from the keyboard and select the hyperlink cells.
• There is some command to write a mail content in the Excel: “Mailto:” command to send mail; “?subject=” Command to write subject; “& body =” – the command to write the text.

### Recommended Articles

This has been a guide to HYPERLINK Formula in Excel. Here we discuss How to use HYPERLINK Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

1. Find External Links in Excel
How to Create a Hyperlink in Excel (3 Most Common Types of Hyperlinks)

The HYPERLINK function creates a hyperlink from a given destination and "friendly name", which is simply the link text. You can use HYPERLINK to construct a clickable hyperlink with a formula. The HYPERLINK function can build links to other cells in a workbook, other sheets, named ranges, other workbooks, pages on the internet, or to files on network servers. You can also use HYPERLINK to create email links.

The HYPERLINK function takes two arguments: link_location and friendly_name. Link_location is the destination or path the link should follow, entered as text. Friendly_name is the text that will be displayed with the link.

When a user clicks a cell that contains the HYPERLINK function, Excel will open the file or page specified by link_location. Link_location can be a cell reference or named range, a path to a file stored on a local drive, a path a file on a server using Universal Naming Convention (UNC), or an internet path in Uniform Resource Locator (URL) format.

### Example #1 - link to cell

To link to another cell in the same worksheet, prefix the cell with "#":

### Example #2 - link to sheet

To link to another sheet in the same workbook, use "#" with the Sheet name like this

### Example #3 - external link

To link to https://exceljet.net/ with the text "exceljet":

### Example #4 - email link

To link to a valid email address in A1, you can concatenate "mailto:" like this:

With two email addresses in A1 and A2, you can create a link like this:

This formula example explains how to construct a more complete mailto email link with cc, subject, body, etc.

### Notes

• Link_location should be supplied as a text string in quotation marks or a cell reference that contains the link path as text.
• If friendly_name is not supplied, the HYPERLINK will display link_location as the friendly_name.
• To select a cell that contains HYPERLINK without following the link, use arrow keys or right-click the cell.

### You will also like:

How she wriggles, moans and waves. Like a half-naked woman on her knees, she takes his big, lumpy cock in her mouth. How he rips off her panties, squeezes her big naked boobs and starts to fuck with cancer.

2278 2279 2280 2281 2282