Excel HYPERLINK function • My Online Training Hub

If your workbooks ever feel like a maze, the Excel HYPERLINK function can turn them into a smooth, one-click navigation experience. In this tutorial, you’ll learn how to use HYPERLINK to clean up messy URLs, navigate between sheets, open external files or folders, and even create dynamic links that respond to your selections.

By the end, you’ll know how to transform your spreadsheets into interactive tools that save you time and impress your boss.

Watch the Video
Download Workbook
What Is the HYPERLINK Function?
1. Clean Up Messy URLs in Reports
2. Build a Table of Contents to Navigate Between Sheets
3. Link Directly to Files and Folders
4. Create Dynamic Hyperlinks with XLOOKUP
Common Mistakes to Avoid
Take It Further

Watch the Excel HYPERLINK Function Video

Get the Free Example File

Enter your email address below to download the free file.

By submitting your email address you agree that we can email you our Excel newsletter.

What Is the HYPERLINK Function?

Excel’s HYPERLINK function lets you create clickable links, not just for web pages, but also for internal sheet navigation and file shortcuts.

Syntax:

=HYPERLINK(link_location, [friendly_name])

link_location: The destination URL, file path, or cell reference.
friendly_name: [Optional] The text you want displayed (instead of the raw link).

1. Clean Up Messy URLs in Reports

If your reports are filled with long, ugly URLs like this:

You can replace them with neat anchor text like “View Report” resulting in a cleaner, neater report free of clutter:

Formula:

=HYPERLINK([@[Report Link]], "View Report")

This formula creates a clickable label while keeping the actual URL hidden in another column (which you can safely hide). It makes your reports look professional and much easier to read.

Pro Tip: Always test your links before sending the file to someone else. Excel won’t tell you if a URL is broken.

2. Build a Table of Contents to Navigate Between Sheets

When your workbook has multiple sheets, clicking back and forth wastes time.

how to build a table of contents in Excel?

Instead, create a Table of Contents (TOC) sheet with clickable links to each tab.

Example:

=HYPERLINK("#"&C6&"!A1", C6&" Report")

An Excel Table of Contents using the HYPERLINK function

Here’s what’s happening:

The # tells Excel it’s an internal link.
C6 contains the sheet name (e.g. “North”).
!A1 is the target cell within that sheet.
The friendly name shows as “North Report”.

This technique helps others navigate your workbook without getting lost.

Bonus: You can also create navigation buttons using shapes:

Insert a rectangle → type the sheet name.
Press Ctrl+K → “Place in This Document”.
Select the sheet you want to link to.
Repeat for all sheets.
Colour code shapes to indicate which is the currently selected sheet.

Tip: You can even hide the sheet tabs (File → Options → Advanced → Display options for this workbook) to make your dashboard look sleek and controlled.

3. Link Directly to Files and Folders

The HYPERLINK function isn’t just for websites — it can also open files or folders directly from Excel.

Examples:

=HYPERLINK("C:\Project Gemini\Specifications.pdf", "Customer Requirements")

=HYPERLINK("C:\Reports\", "Open Reports Folder")

This trick lets you create a single Excel hub for all project files. It’s perfect for teams that constantly share supporting documents, contracts, or images.

Note: Use OneDrive or SharePoint paths for shared workbooks so everyone can access the same links.

4. Create Dynamic Hyperlinks with XLOOKUP

You can even use formulas to create dynamic links that update based on a selection.

Imagine you have an order database and a dropdown list of Order IDs. You can use XLOOKUP to return details for the selected order and a HYPERLINK that jumps straight to the matching row:

how to create dynamic hyperlinks in Excel?

Formula:

=IFERROR(
HYPERLINK("#"&CELL("address",
XLOOKUP(D6,Orders[OrderID],Orders[OrderID])),
"Go to "&'Dynamic Hyperlinks'!D6),
"Order not found")

How it works:

XLOOKUP finds the matching Order ID and returns its cell reference.
CELL("address", …) gets that cell’s address.
# makes it an internal link.
The friendly name shows as “Go to [Order ID]”.

Clicking the link takes you directly to that order in your database. No scrolling, no searching.

Common Mistakes to Avoid

Broken File Paths:
Excel doesn’t check whether the linked file actually exists. Always double-check your paths and test them before sharing.
Sheet Names with Spaces:
If your sheet name includes spaces (e.g., Sales Data), wrap it in single quotes:

=HYPERLINK("#'Sales Data'!A1", "Open Sales Sheet")

Tip: It’s good practice to always include quotes, just in case.

Take It Further

If you love turning workbooks into interactive tools that do the work for you, you’ll love my Advanced Excel Formulas Course.

You’ll master formulas like LET, LAMBDA, IFERROR, and XLOOKUP to automate workflows and eliminate repetitive manual steps.

Table of Contents