Excel formulas disappearing when selected with live data?

Question

I have a basic spreadsheet with some live RTD data links. I believe the links are the issue as sheets that do not update or recalculate don't have any issues.

I noticed that if I select a cell, after a few seconds, the formula filed switches from showing a formula to being empty. The formula is not actually empty and the sheet keeps working.

Here cell B1 has a basic formula of SUM(B7:E7) and it works perfectly, but the formula bar is empty.

The issue is that if I try to edit the cell either in the cell or in the formula field when it is empty, it save the empty formula. The worst part is that it also breaks the undo so restoring the old formula is not easy or not possible at all.

I am using 365 currently 2602 Build 16.0.19725.20126. I have been seeing this issue for at least a few months, but have not found a solution.

Answer 1

Hi @Michael Elkin,

Based on your sharing, since the issue only happens with file that has RTD links, this is likely a UI/state issue triggered by RTD/live recalculation or the RTD add-in interaction with your Excel build. I have researched through Microsoft product help and other official information sources but up to now, there is no Microsoft help article or announcement found and also no troubleshooting guidance addressing this issue with RTD in Excel.

In this case, for you receiving the most appropriate assistance, I recommend that the Global Admin in your organization create a service request with Microsoft Support. A technical support engineer can perform a remote session to investigate the situation, verify the backend configurations, and run any necessary synchronization tools to resolve the problem. If the issue requires further attention, they can escalate it to a specialized team for deeper analysis.

Ask the Global Admin in your organization to create a service request with Microsoft support to get advanced technical assistance and diagnostics. For detailed instructions on how to get support, please refer to Get support - Microsoft 365 admin. If you don't know who your IT administrator is, please refer to this article: How do I find my Microsoft 365 admin? - Microsoft Support 

As a forum moderator, my access is limited and only Microsoft’s specialized support team has the necessary tools and permissions to assist with advanced troubleshooting. Please know that even though I can't directly intervene, directing you to the correct, specialized support is the most effective help I can provide from my position, and I truly hope the issue will get resolved swiftly.

Thank you for your patience and understanding. I'm looking forward to your reply.

---

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

Answer 2

This behavior is consistent with formulas that are effectively being replaced or invalidated by issues with external links or calculation, even though the cell still holds a value. When editing, Excel then commits the “empty” state and removes the original formula, and Undo cannot restore it reliably.

To avoid losing formulas in this situation, the safest approach is to replace formulas with their calculated values only when explicitly intended, and to avoid editing cells whose formulas are affected by broken or unstable links.

Use these mitigations:

1. Before deleting or changing any external data sources, ranges, or linked workbooks, replace only the formulas that truly should become static values:
   1. Select the cell with the formula.
   2. Press Ctrl+C.
   3. Press Alt+E+S+V (Paste Special → Values), or use Home > Clipboard > Paste > Paste Values. This permanently removes the formula and keeps the value so it cannot later “disappear” when links recalc.
2. If formulas are already showing errors due to missing or changed references (for example, #REF! or #NAME?), correct the references instead of editing the “empty” formula bar:
   • For #REF! caused by deleted cells or sheets, select the cell, highlight #REF! in the formula bar, delete just that part, and re‑enter the correct range.
   • For #NAME? caused by missing defined names, recreate the name or change the formula to refer directly to the cell range (for example, A2:D8).
3. If external workbooks or sheets that formulas depend on have been deleted or are unavailable, do not edit those formulas. Instead, open the workbook while the links are still valid and then convert the formulas to values as in step 1 so the results are preserved even if the source later disappears.
4. When opening workbooks with external links, if prompted whether to update links and the sources are not available or stable, choose Don’t Update to avoid recalculation that can introduce #REF! or other errors into dependent formulas.

If the issue persists across all workbooks (not just those with RTD links), perform an Online Repair of Microsoft 365 to restore Excel components that might be corrupt, then test again in a copy of the workbook before further edits.

---

References:

• How to avoid broken formulas in Excel
• Manage workbook links
• External links may be calculated when you open a workbook that was last saved in an earlier version of Excel
• When I open an excel file it shows - We're sorry, but excel has run into an error that is preventing it from working correctly, Excel will need to be closed as a result. would you like us to repair now? then shows the option "repair now","help","close" - Microsoft Q&A