Excel HYPERLINK Function — Build Clickable Links That Update Themselves

TL;DR — HYPERLINK(link_location, [friendly_name]) is a formula that builds a clickable jump: link_location is where to go, friendly_name is the text to show. It is not the same as Insert > Link — that menu drops a static, hand-made link, while the HYPERLINK formula recomputes its target every time, so the destination can be calculated by other formulas. Jump inside a workbook with a # prefix (=HYPERLINK("#Sheet2!A1","Go")), and combine it with ADDRESS and MATCH to build a link that finds a row and jumps to it.

=HYPERLINK("#Sheet2!A1", "Go to Sheet2")          ' jump inside THIS workbook — note the #
=HYPERLINK("#'Q1 Data'!A1", "Open Q1")            ' sheet name with a space -> single quotes
=HYPERLINK("mailto:" & E2, "Email " & D2)          ' build an email link from cell data
=HYPERLINK("#" & ADDRESS(MATCH(H2, IDs, 0), 1), "Find row")   ' search-and-jump
=HYPERLINK("https://excelmaster.ai", "Visit")     ' open a web page

Most people meet hyperlinks through the Insert > Link dialog and never realize there's a function. The difference matters: the dialog makes a link you have to edit by hand every time the target moves, while the HYPERLINK function makes a link that a formula can steer. Once you see it as "a link whose destination is computed," a set of navigation tricks — clickable tables of contents, jump-to-the-matching-row buttons, mailto columns — becomes one short formula.

What you'll learn

The mental model: HYPERLINK is a formula, not the Insert > Link menu
Jumping inside a workbook with the # prefix everyone forgets
The real power: targets computed by ADDRESS, MATCH, and &
The #1 confusion: it navigates on click, it doesn't fetch the target's value
Why you should almost always pass friendly_name
Links to files, the web, and email — and the paths that quietly break

There are two completely different things in Excel both called "hyperlink," and confusing them is the root of most trouble. Insert > Link (Ctrl+K) attaches a fixed link to a cell — you pick a target once, and it sits there until you manually edit it. The HYPERLINK function is a formula: it returns a clickable link, and like any formula it recalculates, so its target can be built from other cells and other functions.

That is the entire reason to use the function instead of the menu: when the destination should depend on data, only a formula can express it. A static link to "row 42" is wrong the moment the data re-sorts; =HYPERLINK("#"&ADDRESS(MATCH(...)…)) recomputes which row to jump to every time. If your link never changes, the menu is fine. If it should follow the data, you need the function.

Jump within the workbook: the # prefix everyone forgets

The single most common use — and the single most common failure — is an in-workbook jump. The rule is small and absolute: an internal location must start with #.

=HYPERLINK("#Sheet2!A1", "Go to Sheet2")     ' correct — # means "this workbook"
=HYPERLINK("#A1", "Back to top")             ' # with no sheet -> a cell on the current sheet
=HYPERLINK("#'Q1 Data'!A1", "Open Q1")       ' sheet names with spaces need '...'
=HYPERLINK("Sheet2!A1", "Go")                ' WRONG — no # -> Excel hunts for a FILE named Sheet2

Without the #, Excel reads the string as an external filename, fails to find it, and the link errors or does nothing when clicked. The # says "stay inside this file." Sheet names containing spaces or symbols must be wrapped in single quotes ('Q1 Data'), exactly as in a normal reference. Get the # right and you can build a clickable table of contents — one HYPERLINK per section — in a couple of minutes.

The real power: targets computed by other formulas

Static links are the boring 10%. The reason HYPERLINK earns its place is that link_location is just a string, so any formula that produces a string can produce a link. Concatenate with &, compute the address with ADDRESS and MATCH, and you get a link that finds its destination:

=HYPERLINK("#" & ADDRESS(MATCH(H2, IDs, 0), COLUMN(IDs)), "Jump to " & H2)
=HYPERLINK("mailto:" & E2 & "?subject=Invoice " & A2, "Email")
=HYPERLINK("#" & CELL("address", INDEX(data, MATCH(H2, keys, 0))), "Go")

The first reads as: MATCH finds which row holds the ID typed in H2, ADDRESS turns that row-and-column into a text address like $B$57, the # makes it an internal jump, and & glues on a friendly label — a working "search box" that jumps to the matching record. This is the payoff of the mental model: because the target is computed, the link tracks the data instead of pointing at a row number that goes stale.

The #1 confusion: it navigates on click, it doesn't fetch the value

Here's the misunderstanding that generates the most "HYPERLINK isn't working" questions: people expect the cell to show the value at the target. It doesn't.

=HYPERLINK("#Sheet2!A1", "Go")   ' the cell shows "Go"; clicking moves the cursor to Sheet2!A1
                                 ' it does NOT display whatever is stored in Sheet2!A1

A HYPERLINK cell displays its friendly_name and, only when a human clicks it, moves the selection to the target. The formula never reads the target's contents — navigation and retrieval are different jobs. If you want to show the value at a looked-up location, that's an INDEX/MATCH or VLOOKUP problem, not a HYPERLINK one. Keep the line clean: HYPERLINK takes you there; a lookup brings the value here. You'll sometimes see both side by side — a lookup column showing the number, a HYPERLINK column offering "open the source."

friendly_name: almost always pass the second argument

friendly_name is optional, and omitting it is a small, avoidable ugliness:

=HYPERLINK("#Sheet2!A1")              ' the cell literally shows "#Sheet2!A1"
=HYPERLINK("#Sheet2!A1", "Details")   ' the cell shows "Details" — do this
=HYPERLINK(url, "Open " & Company)    ' friendly_name is text, so build it with &

Leave it out and the cell displays the raw link_location — #Sheet2!A1, mailto:sam@…, a 90-character URL — which is exactly the noise you were trying to hide. Because friendly_name is just text, you can compute it too ("Open " & Company), giving each link a readable, data-driven label. Rule: give every HYPERLINK a friendly name so the cell reads like a button, not like a stray URL.

Links to files, the web, and email — and when they break

link_location handles more than internal jumps; the trap is that external targets are fragile in ways internal ones aren't:

=HYPERLINK("https://excelmaster.ai/blog", "Read the blog")    ' web — opens the browser
=HYPERLINK("[Budget.xlsx]Summary!A1", "Open budget")          ' another OPEN workbook
=HYPERLINK("C:\Reports\Q1.pdf", "Q1 report")                  ' a file by full path
=HYPERLINK("mailto:[email protected]", "Contact sales")          ' email — opens the mail app

Web links (https://) and mailto: are stable because they don't depend on the file's location. File-path links are the ones that break: an absolute path (C:\Reports\…) dies the moment the workbook is opened on another machine, and a relative path shifts if the file moves. And a link into [Another.xlsx] behaves differently depending on whether that workbook is open. Treat external HYPERLINKs as things to test after moving the file, not set-and-forget — the failure is always "it worked on my computer."

The judgment call

The decision tree is short. Static target, set once → Insert > Link is fine. Target that should follow the data, or a link built from a formula → HYPERLINK the function. The two mechanical rules that prevent almost every failure: prefix internal jumps with # (without it Excel goes looking for a file), and always pass a friendly_name so the cell shows a label instead of a raw address. And remember what HYPERLINK is for — it moves the cursor on a click; it never fetches a value, so the instant you find yourself wanting the contents of the target, switch to a lookup. Keep navigation and retrieval separate and HYPERLINK becomes the cleanest way to make a spreadsheet you can actually click through.

How ExcelMaster helps

Dynamic links are fiddly to assemble by hand — one missing # and the whole thing fails silently. Tell ExcelMaster "make a link that jumps to the row matching the ID in H2" and it writes the HYPERLINK("#"&ADDRESS(MATCH(...)…)) for you, quotes any sheet names that need it, and adds a readable friendly name. Ask for "a clickable email column from the address and name columns" and it builds the "mailto:"&… concatenation. And if you paste a HYPERLINK that's missing its # prefix or expecting the target's value to appear, it flags the two classic mistakes before you ship a table of dead links.

Frequently asked questions

What does the HYPERLINK function do in Excel?

HYPERLINK(link_location, [friendly_name]) creates a clickable link in a cell. link_location is the destination — a web address, a file path, an email (mailto:), or an internal jump like #Sheet2!A1 — and friendly_name is the text shown in the cell. Because it's a formula, the destination can be computed by other formulas, which the Insert > Link menu can't do.

How do I link to another sheet or cell in the same workbook?

Prefix the location with #: =HYPERLINK("#Sheet2!A1", "Go to Sheet2"). The # tells Excel the target is inside the current workbook rather than an external file. If the sheet name contains a space, wrap it in single quotes: =HYPERLINK("#'Q1 Data'!A1", "Open").

Why isn't my HYPERLINK working / why does it look for a file?

The most common cause is a missing # on an internal link. =HYPERLINK("Sheet2!A1") makes Excel search for a file called "Sheet2"; you need =HYPERLINK("#Sheet2!A1"). The other common surprise is expecting the cell to display the target's value — it doesn't; it shows friendly_name and only navigates when clicked.

How do I make a dynamic hyperlink that jumps to a matching row?

Compute the address with MATCH and ADDRESS, then prefix #: =HYPERLINK("#" & ADDRESS(MATCH(H2, IDs, 0), 1), "Jump"). MATCH finds the row of the value in H2, ADDRESS turns it into a text address, and # makes it an internal jump — so the link follows the data instead of pointing at a fixed row.

Can HYPERLINK return the value of the cell it points to?

No. HYPERLINK only navigates — clicking it moves the cursor to the target. It never reads what's stored there. To pull a value from a computed location, use INDEX/MATCH or VLOOKUP instead; you can place that lookup right next to a HYPERLINK if you want both the value and a jump link.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-13.