17-09-2021

Creating Hyperlinks In Excel

Everyone would agree that creating a hyperlink in Excel is pretty straight forward. All you have to do is to copy the url (web address) from your web browser and paste the link into one of the cells in Excel. Using the INSERT method, you can also create a link that allows you to jump to another worksheet, another cell in the same file. But that is not what we are going to share with you here. We would like to share with you the worksheet function version that can do more than just

How to Create a Hyperlink in Excel Using VBA Code? You can add a hyperlink to a text or one sheet to another worksheet within excel using hyperlink add property. The format needs to be followed by specifying where the hyperlink should be created and navigation URL etc. A: Excel 2010, 2007, and 2003 provide a hyperlink function that converts text to a clickable hyperlink. In the example below, I entered =HYPERLINK(L2) in cell M2, which converts the text-based data in cell L2 into a clickable hyperlink.

jumping to another cell in another worksheet or to link to a website. It can help you search for text located in another worksheet.

Basic use of HYPERLINK formula

First let us look at the basic setup of the formula:

With your Excel document open click on the cell where you want to create the hyperlink and under the Insert tab click on the Hyperlink button. The Insert Hyperlink dialog box will pop up and here is where you want to add the text to display and browse to the location of the file you want to link to. Now you will notice the text in that cell is. Return a text-string that contains the Excel (!)-formula for hyperlinks, preceded by an apostrophe ‘. After the data has been loaded to the sheet, check the column and replace ‘= by = to activate your Excel-formula: Activate the HYPERLINK formula by replacing ‘= with =. You can then format the column to “Hyperlink”.

There are 2 inputs required for this formula to work.

The first input is the link location and the second is the friendly name, i.e. the displayed name. Supposed you would like to link to a cell C5 in the worksheet Sheet2 from Sheet1. You have to enter the full name of the file followed by the worksheet name and then the cell. Assuming that your file is located in D:everydayExcel MktgMsg_1208, the file name is called search.xls, you will have to enter the formula as

=Hyperlink(“[D:everydayExcel MktgMsg_1208search.xls]Sheet2!C5,”Displayed Name”)

Note that the file directory must be enclosed within the square brackets [xx]

Displayed Name is used to disguise or shorten actual link. It is also commonly known as the anchor text.

Between this worksheet formula and INSERT HYPERLINK formula, I would rather choose the latter which is more straightforward. It’s found in the pop-up menu when we right click within a cell.

So what is use of learning this function?

Combined with other formulas such CELL, ADDRESS, MATCH and Ampersand (“&”), you can turn it as a powerful search function.

Let’s say I have 3 worksheets & I want to put a link in C5 (Sheet1) to find whatever’s in B5 in another worksheet named “Sheet2”.

For the first input which requires the filename and its location on the computer, we can use the CELL formula to automatically return where the Excel file is located.

“Cell(“filename”,[reference]).

The “reference” should be a cell in the worksheet you wish to jump to. In our case, we’ll go to Sheet2 & select A1.

Hyperlinks

The CELL formula gives the full details for the directory, current Workbook & Worksheet Name as shown.

[D:everydayExcel MktgMsg_1208search.xls]Sheet2

To link it to a cell reference, we need an exclamation mark after the worksheet name & the cell we are looking for. To find the row number “Curtis Lemanski” is at, we can use a MATCH formula:

The “lookup_value” is where “Curtis Lemanski” in Sheet1 is at (i.e. B5). “lookup_array” is a range in Sheet2 where “Curtis Lemanski” is be located. That range is “A1:A15” for our example. And putting “0” in “match type” suggest that we only want to find an exact match.

The ADDRESS formula returns a cell reference when it is given the row number & column number.

Row number has already been obtained by using the MATCH formula while column number is “1”, referring to Column A.

Using the “&”, we combine the filename, worksheet name, “!” and the cell reference to form this:

=HYPERLINK(CELL(“Filename”,Sheet2!$A$1)&”!”&ADDRESS(MATCH(B5,Sheet2!A1:A15,0),1),”Link”)

And we’ll get:”[D:everydayExcel MktgMsg_1208search.xls]Sheet2!$A$6 When we change the name given in B5, the hyperlink will be re-directed to the particular name.

New! Comments

Have your say about what you just read! Leave me a comment in the box below.

Share this page:

Enjoy this page? Please pay it forward. Here’s how…

Would you prefer to share this page with others by linking to it?

  1. Click on the HTML link code below.
  2. Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.

<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>

Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft

Creating hyperlinks in Excel can be beneficial in terms of helping you to navigate better, i.e. jumping from one worksheet or cell to another, or opening an external file that is linked to a particular worksheet. Here are six types of hyperlink that you can easily create in Excel:

(1) Link from a specific cell to another cell in the same worksheet (i.e. link within the same worksheet)

1. Choose the specific cell that you want to link to another cell. For example, in Sheet1, you want to jump from cell B2 to H10.

2. Right-click cell B2 and select Hyperlink from the pop-up menu.

3. A new Insert Hyperlink dialog box appears. Click the Place in This Documentbutton, then delete “A1” from Type the cell referencetext box and replace with “H10”. Click the OK button.

4. Cell B2 turns into a hyperlink (i.e. underlined and in blue). Click the hyperlink and it will jump to cell H10.

(2) Link from one worksheet to another worksheet in the same spreadsheet file (i.e. link between two different worksheets in the same Excel file)

Let’s just say, you want to link cell A2 in Sheet1 to cell A2 in Sheet2 within the same spreadsheet file that you’re working on.

1. Right-click cell A2 (in Sheet1) and select Hyperlink from the pop-up menu.

Creating hyperlinks in excel worksheet

2. A new Insert Hyperlinkdialog box appears. Click the Place in This Documentbutton, then delete “A1” from Type the cell reference text box and replace with “A2”. Select Sheet2 from the Cell Reference tree list and click the OK button.

3. Cell A2 (in Sheet1) turns into a hyperlink (i.e. underlined and in blue). Click the hyperlink and it will jump to cell A2 in Sheet2.

(3) Link to a separate file (i.e. link from one worksheet to another Excel file, Word document, etc.)

You can create a link in the current spreadsheet file that opens up another file when a reader clicks the link. For example, when a reader clicks the link in cell A2 in a spreadsheet file called Status Report, it will open a Word document called Project Charter.

1. Right-click cell A2 (in the Status Report file) and select Hyperlink from the pop-up menu.

2. A new Insert Hyperlink dialog box appears. Click the Existing File or Web Pagebutton. Note that the Current Folderbutton is selected by default. Locate the file that you want to link it to, select that file and click the OK button.

3. Cell A2 turns into a hyperlink (i.e. underlined and in blue). Click the hyperlink and it will open the Project Charter file.

(4) Link to external website

You can also create a link in the spreadsheet file that directly opens up a web page. For example, when a reader clicks the link in cell B2, it will open a web browser that directs the reader to the home page of the web address, www.perrinator.com.

1. Right-click cell B2 and select Hyperlink from the pop-up menu.

2. A new Insert Hyperlink dialog box appears. Click the Existing File or Web Page button. Key in the web address “http://www.perrinator.com” in the Address bar and click the OK button.

3. Cell B2 turns into a hyperlink (i.e. underlined and in blue). Click the hyperlink and it will open a web browser that displays the home page of http://www.perrinator.com.

Note: Another way to quickly create a link to a website, is to type the web address into a cell and hit the Enter button.

(5) Link to email address

Creating a link to send an email is possible but this will launch Microsoft Outlook or Windows Live Mail as the default email application (it depends on the setting of your computer). For example, when a reader clicks the link in cell B2, it will launch Windows Live Mail with a blank email message that contains the recipient’s email address.

1. Right-click cell B2 and select Hyperlink from the pop-up menu.

2. A new Insert Hyperlinkdialog box appears. Click the E-mail Addressbutton. Key in the recipient’s email address in the E-mail address text box and the subject of the email in the Subject text box. Click the OK button.

Note: When you key in the recipient’s email address, you don’t have to type “mailto:” It will automatically appear as you type the email address.

Creating

3. Cell B2 turns into a hyperlink (i.e. underlined and in blue). Click the hyperlink and it will launch Microsoft Outlook or Windows Live Mail (depending on your computer setting) that opens up a blank email message with the recipient’s email address and the email subject.

(6) Link to create a new file

This type of hyperlink is rarely used in Excel. It typically means that when someone clicks the hyperlink, it will open another new Excel file.

1. Right-click cell B2 and select Hyperlink from the pop-up menu.

2. A new Insert Hyperlink dialog box appears. Click the Create New Documentbutton. Enter the name of the new Excel file (e.g. “Learn Excel”) in the Name of new documenttext box.

3. The default location where the new file will be created and saved in your computer, is shown under the Full path section. You can change the default location to a different path by clicking the Change button, choosing the desired location and clicking the OK button.

4. Under the When to editsection, there are two options to choose from, as explained below:

1. Option 2 – Edit the new document now

You can open / edit the new Excel file later, i.e. after you click the OKbutton, you will return to the existing spreadsheet that you’re working on.

2. Option 1 – Edit the new document later

You can open / edit the new Excel file straightaway, i.e. after you click the OK button, you will be immediately switched to the new spreadsheet.

For the purpose of this tutorial, let’s go ahead with Edit the new document later, and click the OK button.

5. You’re back at the existing spreadsheet where cell B2 turns into a hyperlink (i.e. underlined and in blue). Click the link and, depending on your computer setting, a Microsoft Excel Security Noticemessage will appear and prompt you to either proceed to open the link or cancel it. Click the Yes button.

6. And that’s it! You have successfully opened a new Excel file “Learn Excel”!