Hyperlinks

Hyperlinks: subtle oddities

I’ve been working on a project which I thought I would explain some nuances about parts of Excel that I learned while doing this project. Today’s post will be on hyperlinks.

So far that I know, there are kinds of hyperlinks: 1) one where you apply links via the Insert tab in the menu bar and 2) the other via the function HYPERLINK.

Function HYPERLINK

The function HYPERLINK is new to me and I thought I would lead off with this method. This one is specifically for linking to another file or to a website; however, I couldn’t see why I would use this over just referencing the other file with = and the cell or referencing the website with the URL.

After conferring with CoPilot, here is what it told me:

Great question, Veronique — you’re noticing that Excel often gives us multiple ways to accomplish the same thing, but each has subtle differences in functionality, flexibility, and user experience. Let’s break down why the HYPERLINK function exists and when it’s more powerful than just pasting a URL or linking cells.

🔗 Why Use the HYPERLINK Function?

1. Dynamic Linking

  • With HYPERLINK, you can build links dynamically using formulas.
    Example: =HYPERLINK("https://example.com/report/" & A2, "View Report") This creates a clickable link that changes automatically based on the value in cell A2.
  • Copy-pasting a URL is static — it won’t adapt if your data changes.

2. Custom Display Text

  • HYPERLINK lets you show friendly text instead of the raw URL or file path.
    Example: =HYPERLINK("C:\Reports\Q1.xlsx", "Open Q1 Report") → Users see “Open Q1 Report” instead of a long, messy file path.

3. Linking Across Workbooks

  • If you just use =, Excel references the cell value in another workbook, not the file itself.
  • HYPERLINK actually opens the external file or location when clicked — it’s an action, not just a reference.

4. Navigation Within the Same Workbook

  • You can jump to a specific cell, range, or worksheet.
    Example: =HYPERLINK("#Sheet2!A10", "Go to Details") → Clicking takes you straight to Sheet2!A10.

5. Cleaner User Experience

  • Instead of cluttering your sheet with raw URLs or paths, you can present neat, descriptive labels.
  • This is especially useful in dashboards, reports, or files you share with others.

⚖️ Comparison

MethodWhat It DoesLimitations
= referencePulls the value from another cell/workbookDoesn’t open the file or website; breaks if source file moves
Paste URLOpens the website/file when clickedStatic, shows raw URL (ugly in reports)
HYPERLINKCreates a clickable link with custom text, can be dynamicRequires formula knowledge

👉 So the key difference:

  • = is about data linking (pulling values).
  • HYPERLINK is about navigation and usability (jumping to places, opening files, making dashboards user-friendly).

Having a cleaner look or changing the cell referencing to create dynamism is an interesting idea.

Now, I tried using approach 4 – linking within the same file – and it did not work for me. I wrote a HYPERLINK formula, using an internal cell as location, and when I tried testing the file, the formula would go out to OneDrive or the internet and then exclaim: “File not found”. (Or something similar.)

Maybe my formula set up was wrong.

At present time, I cannot use the function to create hyperlinks within the file.

Using Insert from the Menu Bar

Within the Insert tab found in the menu bar, there is a tool called “Links”. This method allows you to create hyperlinks to websites, outside files or within the file. This is the one I’m most familiar with.

This method works very well for creating hyperlinks within your workbook to help guide other users. BUT, there appears to be a nuance in using that hyperlink.

When I was working on that project. I developed the file on my hard drive which syncs up with my OneDrive. I uploaded the file onto someone else’s OneDrive for her to take a look.

When she was reviewing it and providing feedback, she asked, “Can you take off the links” or something along those lines? It sounded like the links were not working.

When I reviewed the file sitting in her OneDrive, I noticed that Microsoft would show the address of the link pointing back to my OneDrive. And clicking on that link was sticky, so it works but you have to click maybe twice.

I was not expecting that.

Since this was a file that she might have others use, this could become a big problem.

Solution to the linking problem

I did figure out a workaround so that the links do not refer back to my OneDrive:

  • Make a copy of the final file and save it to your Download folder.
  • When you open it, turn off the Autosave.
  • Now add in the hyperlinks create via “Insert – Link” method (from menu bar).

That approach seems to create a workable hyperlink.

Or it could be she wasn’t using the file right. There’s indications that she wasn’t pulling up the file right due to other weird issues that I did not see on my machine.

My next post will be on the curious world of EDATE.

Similar Posts