Teams and Mobile breaks my HYPERLINK formula

Question

Teams and Mobile breaks my HYPERLINK formula

Christopher Thompson 20

I have a formula in my table that takes one cell, and formats it into a clickable URL.

User's image

Here is the formula in that URL column:

=HYPERLINK("https://jira.``[Moderator note: Personally Identifiable Information removed]``.com/issues/?jql=key in ("&ENCODEURL([@[ESPS-Key]])&")","Jira Link")

"ESPS-Key" is the name of the 2nd column in this table.

In row 154, you can see a #VALUE! error. This only happens when someone opens this sheet in Teams OR the ios app.
In row 153, that formula shows the right value and the link works. That's because I hit F2 and Enter to rewrite the cell. I don't want to spam F2 and enter for 30 min fixing this all the time. (Can AI hit the button for me 400 times? lol)

I can probably protect the column, but that's a pain. Is there a simpler fix? Is my formula malformed, but Excel Desktop just deals with it better?

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Hi @Christopher Thompson,

Thank you for taking the time to share what you’ve observed. I really appreciate the level of detail, and I hope the explanation below helps make things a bit clearer.

This isn’t actually a malformed formula. The root of the issue is that the ENCODEURL function isn’t supported in Excel for the web or the mobile/iOS clients (since they rely on the same web-based calculation engine). Because of that limitation, those platforms can intermittently return a #VALUE! error in your HYPERLINK column, while Excel Desktop continues to handle it just fine.

When you press F2 and then Enter on Desktop, Excel recalculates that cell and saves the result as a cached value in the file. Excel for the web or Teams often just displays this cached value and only recalculates cells it considers “dirty.” In your case, row 154 gets recalculated in the web environment, so ENCODEURL runs again and fails, resulting in the error. Meanwhile, row 153 still holds the cached value from Desktop, which is why it appears to work correctly. This is also the reason you find yourself repeatedly pressing F2 and Enter, you’re essentially temporarily freezing a valid result, rather than addressing the underlying compatibility issue.

A more stable approach would be to adjust the formula to avoid using ENCODEURL altogether. For example:

=HYPERLINK("https://jira.xxx.com/issues/?jql=key in(" & TRIM(SUBSTITUTE(SUBSTITUTE([@[ESPS-Key]],CHAR(13)," "),CHAR(10)," "))& ")","Jira Link")

User's image

In this version, TRIM and SUBSTITUTE are fully supported across Excel Desktop, Teams (Excel for the web), and mobile, so you won’t run into the same compatibility issue.

Hopefully this gives you a clearer picture of what’s happening behind the scenes and saves you from having to “spam F2” going forward!!

If the adjusted formula doesn’t quite match your data or you notice anything else unusual, please leave a comment under this post. This is the best way for me to receive notifications and follow up with you promptly.

Appreciate you sharing findings, and thanks again for your patience with this one!

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Anonymous

2026-06-24T07:51:46.17+00:00

Hi @Christopher Thompson,

I’m just checking in to see whether you’ve had a chance to review my previous response. If anything I shared isn’t clear yet, or if you have any follow-up questions or concerns around the information provided, please don’t hesitate to let me know.

I’d be more than happy to continue the discussion and clarify anything that might be helpful for you.
Christopher Thompson 20 Reputation points

2026-06-24T12:10:41.77+00:00

Rin-L thanks for your answer. I replaced all the hyperlink formulas with your suggestions and it does work on all platforms.

I'm frustrated that there are different versions of excel that accept different formulas. Is there a list of formulas that aren't supported across every version? I put in a lot of work to come up with my encodeurl feature, but msft didn't tell me it wasn't compatible if someone on my team used the wrong version.

Related: is there a way to block other version of excel besides Desktop from opening the workbook?

Thanks for your help.
Anonymous

2026-06-25T10:17:46.6266667+00:00

Hi @Christopher Thompson,

Thank you for your thoughtful follow-up.

Unfortunately, Microsoft doesn’t currently provide a single, comprehensive list of formulas that are unsupported across all Excel versions and platforms. Part of the reason is that the list is quite extensive and continues to evolve as Excel (especially Excel for the web and mobile) is updated over time.

As for your second question, there isn’t a built-in way to strictly enforce “Desktop-only” access for an Excel workbook. There’s no direct setting or toggle that prevents users from opening the file in Excel for the web or mobile apps. Any controls around that would typically need to come from broader organizational policies (managed by IT), rather than something configurable within the file itself.

I know this may not be the perfect solution, but I hope it at least gives you a clearer picture of the current limitations.