Excel formulas disappear

Question

I use excel to calculate class grades with typical grade foremulas, like x % for homework, y% for exam grades, etc.

When I reopen the file, the numbers are all correct, but I cannot see anymore the formula with which I calculated the number. Is this just a display issue? How can I fix it to show the formula?

Also, when I copy the field and paste it into a new field, it used to re-calculate by the same formula but for the new data fields, say E5 instead of B5. Now it just copies the number (that is another students grade) into the new field.

Answer 1

The issue is related to the recent MS Office update. Many file types, which would allow to calculate formulas in the past, do not allow for it any longer. csv, which seems inexplicably to be the default, does not, for example.

xslx is the format you want. Also, worth mentioning, there is a 'show formula' button. If you use that, it shows a formula but you can't execute the formula.

Answer 2

Hi Rudi,

Thank you for sharing your situation. Based on the behavior you described, the formulas may have been replaced with their calculated values. 

To verify this, select one of the affected cells and look at the Formula Bar: 

• If you see a formula beginning with =, the formula is still stored in the cell. Try selecting Formulas > Show Formulas, or press Ctrl + `, to display the formulas. 
• If you see only a number, the formula is no longer present, and the cell now contains a fixed value. This is why copying it to another location copies the same student’s grade rather than recalculating it using the new cell references. 

If the formulas are no longer present, check File > Info > Version History for an earlier version of the workbook. You can also copy the formula from another unaffected cell or recreate it manually. 

After restoring the formula, use standard Copy and Paste or drag the fill handle to apply it to other cells. Avoid Paste Values, since it pastes only the calculated number and removes the formula from the copied result. 

Additional guidance: Display or hide formulas in Excel

If possible, share what you see in the Formula Bar after selecting an affected cell in the comment below. Once I have it, I'll be better positioned to guide you through the next steps. 

I look forward to hearing your thoughts. 

---

Note: Follow the steps in the forum documentation to enable email notifications if you want to receive email notifications related to this topic.

Answer 3

Select one of the affected cells and inspect the formula bar: if it shows a formula beginning with =, this is a display issue; if it shows only the grade number, the formula was removed and must be restored.

If the formula bar still contains the formula

Excel normally displays the calculated result in the cell and the underlying formula in the formula bar.

• To display formulas directly in every worksheet cell, press Ctrl+` (the grave-accent key, usually beside 1).
• Alternatively, select Formulas → Formula Auditing → Show Formulas.
• Use the same command again to return to calculated results. ^1^

To copy a grade formula:

1. Select the formula cell and press Ctrl+C.
2. Select the destination cell.
3. Choose Home → Paste → Paste Special → Formulas.
4. Check the formula bar to confirm that the references changed appropriately. Relative references change when copied; absolute references containing $ remain fixed. ^2^

For example, copying:

=B5*30%+C5*70%

to another row changes the relative row references automatically. If part of the formula must always refer to one fixed cell, use an absolute reference such as $B$1.

Avoid Paste Values, because that deliberately pastes only the calculated grade and discards the formula.

If the formula bar shows only a number

This is not a display setting—the cell now contains a static value. The fact that copying it produces the same student’s grade confirms that Excel has no formula left to adjust.

Check the filename extension in File → Info or File → Save As:

• If it is .csv, .txt, or another text format, save a working copy as Excel Workbook (.xlsx). CSV saves displayed text and values rather than a functional Excel workbook with formulas. ^3^
• If it is the older .xls format, use File → Save As → Excel Workbook (.xlsx). A similar reported case was resolved by retaining the working file in .xlsx and using File → Info → Check for Issues → Check Compatibility before producing legacy-format copies. ^4^

Saving as .xlsx prevents further format-related loss, but it cannot reconstruct formulas that have already become numbers. Restore an earlier workbook version or backup if available; otherwise, recreate the formula in the first grade cell and copy it down using the fill handle or Paste Special → Formulas.

---

References

1. Windows
2. Windows
3. Excel formatting and features that are not transferred to other file formats
4. Excel 2021 - Formulas Disappear After Saving - Microsoft Q&A