Creating Hyperlinks In Excel

HYPERLINK in Google Sheets. The HYPERLINK Function works exactly the same in Google Sheets as in Excel: Additional Notes. Use the HYPERLINK Function to create a hyperlink to a cell within the workbook, an external file, or a webpage. Clicking the link will “goto” to reference. Instead, if you create a sheet to a link to each month with a dynamic link then you can save a lot of time. 3 Steps To Create A Dynamic Hyperlink. Follow the below-mentioned step to create a dynamic link: Insert “#”. Enter the “Link Location.” Sheet & Cell Reference. Insert the “Friendly name.” Linking word. Syntax for Dynamic Hyperlink.

Creating Hyperlinks In Excel Spreadsheet
Create Hyperlinks In Excel Automatically
Creating Hyperlinks In Excel Cell

BY J. CARLTON COLLINS, CPA

Q: My accounting software enables me to export my customer data to Excel, but the customers’ email addresses do not show up as clickable hyperlinks. I must edit each email address one at a time to delete the leading apostrophe to convert the text-based email address to clickable email addresses. Is there an easier way to do this?

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. I then copied this formula (in cell M2) down to convert the other text-based email addresses to clickable email addresses.

Caution: Using the Copy, Paste Values commands to convert the =HYPERLINK formula to regular text removes the hyperlink.

In the Hyperlink base box, type the path that you want to use for all the hyperlinks that you create in this document. Note To override the hyperlink base, type the full address of the hyperlink in the Address box in the Insert Hyperlink dialog box. Method 3 - Hyperlinking to a different Excel sheet in same Workbook. Assuming you have an Excel file named hyperlinkexample.xlsx with two sheets named Sheet1 and Sheet2. You want to create an internal link from cell(A1) of Sheet1 to another cell(A1) of Sheet2 using Openpyxl. Excel uses the # for same file links. You can use it to hyperlink to.

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

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:

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.

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

Creating Hyperlinks In Excel Spreadsheet

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.

Create Hyperlinks In Excel Automatically

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?

Click on the HTML link code below.
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>

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

Creating Hyperlinks In Excel Cell

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