One of the best ways to make an Excel sheet more usable is to improve how people access the information. You don’t need to force users to switch between multiple sheets or scroll through hundreds of rows to search for what they need. The HYPERLINK function can transform these tedious tasks into single-click actions, simplifying navigation beyond keyboard shortcuts.
With the HYPERLINK function, you can create clickable text that takes you to a cell, workbook, PDF, or even an email template. It can even make the resulting link dynamic when combined with Excel’s lightning-fast lookup functions. Let me show you how to utilize this underrated function to minimize clicks and make navigation effortless for yourself and others.
What is the HYPERLINK function?
The key to dynamic links in Excel
The HYPERLINK function enables you to convert text within a cell into a clickable link. Here’s its syntax:
=HYPERLINK(link_location, [friendly_name])Here, link_location is a reference for the information you want to access, such as a cell or sheet (e.g., C2 or #Sheet2!A10). It can even be a file path or URL, such as or mailto:john@company.com. The optional argument friendly_name is the text that will appear as a clickable link in the cell. If you leave the parameter empty, it will display the value in link_location. I would advise using it to make the hyperlinks more descriptive and cleaner.
The HYPERLINK function is also available in Google Sheets.
Suppose A1 contains the name MakeUseOf, and B1 contains the URL https://www.makeuseof.com. We can use the HYPERLINK function to create clickable text in the following way:
=HYPERLINK(B1, "Go to " & A1)Here, the formula will hyperlink the text Go to MakeUseOf. The link is formed by combining the string Go to with the value of A1. The ampersand (&) is used to concatenate the strings. The link in B1 is where we want to go once we click the text.
The traditional method of hyperlinking is to paste the link into a cell and press Enter—this instantly formats it to appear as a hyperlink. However, this is a static link. The benefit of using the HYPERLINK function is that if MakeUseOf changes, for example, tweaking A1 will change the hyperlink everywhere you’ve used the formula.
Creating a table of contents with the HYPERLINK function
The most common use
My favorite use of the hyperlink function is to create a table of contents or navigation buttons in Excel sheets. It helps navigate large workbooks with multiple sheets by allowing one to jump directly to specific worksheets or cells with a single click.
Suppose you have stores in dozens of countries, and you receive a sales report with each country listed on a separate sheet. This can result in a workbook with dozens of sheets. To make navigation easier, start by creating a dedicated sheet called Table of Contents and listing all the countries in column A. If A1 is set to Argentina, you can enter the formula below in B1 to create a hyperlink.
=HYPERLINK("#" & A1 & "!A1", "See sales for " & A1)Since A1 contains Argentina, then link_location will be Argentina!A1. This reference points to the first cell (A1) of the Argentina sheet. The # sign at the beginning, which is very important, tells Excel to treat the cell reference as internal instead of an external file path or URL. The friendly_name parameter will be See sales for Argentina.
With the formula set up as is, drag the formula down to B2, B3, and so on to create hyperlinks for the other sheets. Since the formula uses relative references, A1 will become A2, A3, and so on, the further you go down. If more sheets are added and properly named, add their names to the Table of Contents sheet and drag the fill handle to create hyperlinks for them.
To make the data even easier to find, create a separate sheet, insert a dropdown box in cell A1 with the list of countries, and enter the formula above in B2. Now, when you select a country in the dropdown, a hyperlink for the corresponding sheet with the sales report will be generated on the fly.
Using HYPERLINK with lookup functions
Power your lookups with the HYPERLINK function
Let’s say you have a table with products, and you want to search it using a product ID. After you find the product, you want to generate a hyperlink that takes you to it when clicked. To achieve this, you can use a dropdown and the HYPERLINK function (like in the previous section), along with the MATCH function and VLOOKUP function.
This Excel Function Is So Much Smarter Than VLOOKUP
VLOOKUP’s left-to-right prison had me rearranging data, but this function set me free.
If the products are in a sheet called Products, with column A being the product IDs and column B being the product names, here is what the HYPERLINK function would look like if entered into another sheet:
=HYPERLINK("#Products!A" & MATCH(A1, Products!A:A, 0), "View " & VLOOKUP(A1, Products!A2:B101, 2, FALSE))For the link_location parameter, the formula utilizes the MATCH function to return the index of a product in the Products sheet. A1 (the lookup value) is a dropdown containing product IDs. We then combine the string #Products!A with the index to form a cell reference that points to the product ID in the Products sheet. So if the MATCH function returns 1, the cell reference will be #Products!A1.
The friendly_name parameter combines the string View with the result of the VLOOKUP function. The VLOOKUP uses A1 as the lookup value and cells A2:B101 of the Products sheet as the lookup array to return a product name. The result of everything is a hyperlink that directs you to the cell with the corresponding product ID when clicked.
A whole new way to hyperlink in Excel
The HYPERLINK function is not as well-known as SUM, VLOOKUP, and other popular Excel functions. Now that you know what it is and how it works, I recommend using it in your sheets to make them highly usable. As you can see, combining it with data validation and lookup formulas creates dynamic links that can be particularly useful in reports and dashboards, and other applications.