Hyperlink Formula in Excel (Table of Contents)
Hyperlink Excel Formula
Hyperlink formula helps the excel user to create a shortcut location path or a jump to the respective path in the workbook. When you click on the function, it will redirect the user to the specified location or opens the file document that is present in other path located either in a shared drive, share point, or any other network server, etc. This is a unique functionality provided in excel which makes it easier to access the files or folder located anywhere in the drive thus making it simpler and time-saving. Further, the hyperlink once prepared in excel can be dragged and dropped in any file applications such as outlook mail, word file, power point presentation, etc.
The syntax for the HYPERLINK formula in excel is explained below:-
This Hyperlink excel function consists of two arguments, the details are defined as follows.
- Link_location:- The path location for the file or folder to be opened or jumped. The location can be provided as the system path, universal naming convention (UNC) on the server or uniform resource locator (URL) from the browser for the website address, email to be sent to, jump within the workbook, etc.
- [friendly_name]:- This is an optional syntax. This argument will require the name to be displayed which contains the link address to be jumped. The syntax can either be entered as a text string in double quotes or a reference cell wherein the required name has to be displayed for the corresponding link. Also, the hyperlink link is given displays in blue underlined text format. If you choose to omit this syntax then only the absolute link address would be displayed in blue underlined color format.
How to Use Hyperlink Formula in Excel?
Below are the examples of Hyperlink Excel Formula.
The simplest way to create a hyperlink in excel is shown as follows.
Step 1:- Right click on the cell where you would like to insert the hyperlink name and link location.
Now as can be seen in the above snapshot, click on “Hyperlink”.
Step 2:- Next to a dialog box will open to enter the details for the hyperlink.
Now enter the text to be displayed for hyperlink and the address for the location of the link. The data to be entered is shown below.
Once the data is populated in the required fields as shown in the above screenshot, click on OK.
Here we arrive at the output once you click OK.
Consider the below example to get a better understanding of how to use hyperlink formula within the excel file for executing share drive locations.
Assume that you would like to create a hyperlink for the specific server location or share drive location, as shown below.
Step 1:- Now we have the location or path for the share drive and the name to be given for the hyperlink, you can now create the hyperlink for all the four rows.
As can be seen in the above screen print, the link location is given as reference cell C3 and friendly name cell reference as B3.
Step 2:- Below is the hyperlink as seen after applying the hyperlink formula.
The blue text appears after entering the formula for the hyperlink. Similarly, the same formula has to drag for the rest of the below rows.
Here the hyperlinks once executed opens up respective applications from the share drive location as specified above.
Below example illustrates the application of hyperlink for sending emails from only Microsoft Outlook Mail.
Consider the below list of emails to be created with hyperlink.
While creating the hyperlink for email, you have to prefix “mailto:” text which is an HTML code.
Step 2:- Now apply the hyperlink formula, by using the below syntax
This function will display the name as mailto:Example1@yahoo.com, you can replace the email link with customized name by entering the name in friendly name argument as shown below
=HYPERLINK(“mailto:Example1@yahoo.com”,”Example1 Email Link”)
This function will return the name as Example1 Email Link
The below data shows the email links for different email ids.
As can be seen in the above screenshot, hyperlink column C shows the list of email links created with the hyperlink function. The details of the syntax used are shown in Hyperlink Details column D. Here you will see the different manner in which we can customize the email links which will redirect them to compose email in outlook mail.
Now below is a demo to illustrate how to hyperlink formula could be used to jump or link within the sheets in the same excel workbook.
Consider the below list of cell numbers from different sheets in the same workbook.
It should be noted that in order to use a hyperlink to jump within the workbook, “#” should be used to prefix before entering the sheet name you want to jump into.
The syntax for using a hyperlink to jump within the workbook sheets is shown below:-
=HYPERLINK(“#Example 1!A2”,”Jump to sheet Example 1”)
Here the first argument shows that “#” is used before the sheet name Example 1!A2, the second argument contains the name of the hyperlink to be displayed. The moment you click the hyperlink the link will redirect you to the specific cell A2 in sheet Example 1.
The demonstration for the rest of the rows in the data table is shown as follows.
As can be seen in the above screen print, the hyperlink has been shown for the list of sheet name and their respective cell number where you need to be redirected to in column C. The details of the sheet name links used in column C is shown in column hyperlink details (column D).
Things to Remember About Hyperlink Formula in Excel
- If the Excel hyperlink Formula is not able to jump on to the link location entered then an error will pop up when clicked on the cell.
- The link location entered in text format should be enclosed with quotations, whereas the reference cell for the path location could also be provided directly.
- The friendly name argument can be string name, numeric or a reference cell containing text or value. If the text string name entered is not enclosed with quotes then an error message #NAME? Is displayed.
- If you require to select the cell that contains the hyperlink then click the cell and hold till the cursor changes from “hand click” to “cross sign”.
This has been a guide to Hyperlink Formula in Excel. Here we discuss how to use Hyperlink Formula to create a shortcut path for the destination location with some practical examples and downloadable excel template. You may learn more about excel from the following articles –