Excel 2021 - Formulas Disappear After Saving

Happy Teacher_63 100 Reputation points
2025-07-28T14:32:27.51+00:00

In Microsoft Excel 2021, I’ve encountered an issue where formulas unexpectedly convert to static values after saving, without any warning. This appears to occur when saving in older formats, such as .xls, even though the workbook was created and opened using Excel 2021.

The behavior is consistent across two machines—one running Windows 10 and the other Windows 11—both with Office 2021 installed. Saving the same file in .xlsx preserves the formulas properly.

Is this a known compatibility limitation in Excel 2021 when saving in legacy formats? Are there recommended settings or warnings to prevent formula loss during the save process? Any guidance would be appreciated to help ensure calculation integrity.

Thank you

Microsoft 365 and Office | Excel | For home | Windows
0 comments
Answer accepted by question author
Alex_T 6,405 Reputation points Microsoft External Staff Moderator
2025-07-28T20:34:35.3+00:00

Hello,

Welcome to Microsoft Q&A.

Based on your description, the issue appears that Excel 2021 is converting formulas to static values when saving in the .xls format, even though the workbook was created and opened in Excel 2021.

This behavior is consistent with known limitations when working with legacy file formats.

The .xls format (Excel 97-2003) does not support many modern Excel features. When saving in this format, Excel may automatically convert formulas to values to maintain backward compatibility — often without warning.

To avoid formula loss, use .xlsx and run the Compatibility Checker: File > Info > Check for Issues > Check Compatibility, before saving in older formats

If you must share files with users on older systems, consider saving a copy in .xls while keeping your working file in .xlsx.

And yes, this is a known compatibility limitation in Excel 2021.

Let me know if you need further assistance

Best regards,

Alex | Microsoft Q&A Support Specialist

------------------------------------------------------------------------------------------------------------ 

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. 

 

 

Was this answer helpful?

1 person found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alex_T 6,405 Reputation points Microsoft External Staff Moderator
    2025-07-30T15:11:25.7833333+00:00

    Hi Happy Teacher_63,

    To send feedback to Microsoft team, you can go to:

    1. Send feedback to Microsoft with the Feedback Hub app and follow the instructions.
    2. Welcome to Feedback (Preview)

    Please let me know if you need further assistance.

    Best regards,

    Alex | Microsoft Q&A Specialist

    ------------------------------------------------------------------------------------------------------------ 

    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. 

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Happy Teacher_63 100 Reputation points
    2025-07-29T17:19:58.75+00:00

    Hello Alex,

    Thank you for your detailed explanation. I appreciate the historical context and understand the rationale behind prioritizing modern formats like .xlsx and .xlsm.

    To clarify one point: when saving an Excel 2021 workbook as an .xls file, does the Compatibility Checker explicitly indicate that formulas will be converted to static values? I’ve encountered this behavior consistently, and I’d like to confirm whether the tool flags this as a significant loss of functionality or if it’s more of a silent conversion.

    Also, for reference, here’s how I’ve been using the Compatibility Checker:

    • Go to File > Info
    • Click “Check for Issues” > “Check Compatibility”
    • Review the list of issues, especially under “Significant loss of functionality,” which may include formula-related limitations such as:
    • Excessive nesting
    • Unsupported functions
    • Structured references
    • Array formulas exceeding legacy limits

    If formulas are silently converted without a warning, that’s a critical gap for users working across formats. I’d welcome any insight into whether this behavior is documented or expected, and whether there are plans to improve visibility during the save process.

    Thanks again for your thoughtful follow-up.

    HappyTeacher_63

    Was this answer helpful?

    0 comments
  3. Alex_T 6,405 Reputation points Microsoft External Staff Moderator
    2025-07-29T15:49:54.37+00:00

    Hello HappyTeacher_63,

    Thank you for your thoughtful follow-up.

    It is highly unlikely that Microsoft will invest in improving formula preservation for the .xls format.

    • The .xls format is nearly three decades old. Microsoft's development efforts are firmly focused on the modern .xlsx, .xlsm, and cloud-based formats that support new features, enhanced security, and larger datasets. Supporting a legacy format is primarily for backward compatibility, not active development.
    • Many modern Excel functions (like XLOOKUP, dynamic arrays, etc.) have no equivalent in the old .xls structure. It wouldn't be possible to make them work without fundamentally changing the format itself, at which point it would no longer be .xls.

    The Compatibility Checker is Microsoft's official tool for your confusion. While the warning should ideally be more prominent during the save process, the current design puts the onus on the user to check for issues when they know they are saving for an older version.

    I know that's probably not the answer you were hoping for, but I hope this explanation of the "why" helps you manage the situation effectively.

    Best Regards,

    Alex | Microsoft Q&A Support Specialist

    ------------------------------------------------------------------------------------------------------------ 

    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. 

     

    Was this answer helpful?

    0 comments
  4. Happy Teacher_63 100 Reputation points
    2025-07-29T12:55:11.0866667+00:00

    Thank you for the explanation.

    Given that this behavior stems from legacy format limitations, can you confirm whether there are any plans to address this in future Excel updates or releases? For example, might Excel provide clearer warnings or offer improved preservation of formulas when saving to .xls?

    Your insights would be appreciated, especially for those of us working in mixed-format environments.

    Best Regards,

    HappyTeacher_63

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.