Excel Formula Disappears After Saving: 5 Ways to Fix It

Excel formulas are a great way to automate calculations in your spreadsheet. But if you’re not careful, it’s all too easy to accidentally delete them — and then spend hours trying to figure out how to get them back.

One issue that’s got users on edge is that after crafting a formula in Excel, it disappears even after saving. This is a strange behavior and in this article, we explore various ways to maneuver around this problem.

Why does my Excel formula keep disappearing?

If you’re seeing a formula disappear from an Excel worksheet, there are a few reasons why this could be happening:

• Accidental deletion – You may have accidentally typed over the cell where the formula was originally placed or deleted the original cell.
• Formula exceeds memory capacity – Your formula may be too complex and is exceeding the memory capacity of your computer’s processor. This is why the Excel cell contents disappear. 
• Autocomplete feature – Excel’s AutoComplete feature can be a huge help when you’re entering formulas, but it can also be a pain if it keeps overwriting your formulas. 
• Too long/complex formula – Formulas may also disappear if they are too long or complex. Excel may not display it properly on screen due to restrictions on screen size.
• Copy/paste – When you copy a formula from one cell to another, sometimes Excel will automatically update it with any changes made in the original cell.
• Protected sheet – It is possible that the sheet has been protected and the formula is in a locked cell.
• Error in the function – When you create a formula in Excel, and it keeps disappearing, there could be a problem with the formula itself, and that’s why Excel is not evaluating the formula.

How do I save Excel without losing formulas? 

1. Use Paste Special

If you are using the regular Paste feature to paste values into cells containing formulas, there is a possibility that your formulas will no longer work properly.

When you copy data from another source without including its formatting, Excel assumes that you want to copy both its contents and formatting in one go. To avoid this problem, select Paste Special from the Home tab or press Ctrl+Alt+V after pasting data into your worksheet. 

2. Clean up your worksheet

Another possible cause of this problem is that your worksheet may contain too many formulas. This forces Excel to recalculate all of them every time you save it.

To fix this, try cleaning up your workbook and removing unnecessary formulas. Ensure you remain with only the ones that are needed to remain visible on your screen at all times.

3. Save the file in the Excel binary workbook

1. On the Excel file, navigate to File.
2. Select Options.
3. Click on Save, and under Save workbooks, select Excel Binary Workbook.

When you save an Excel file, you can choose between two formats: .xlsx or .xlsm. The main difference between the two formats is the amount of data you can save on each. The .xlsm file extension is compressed by default and allows for more complex calculations and functions.

Also, if you save an Excel document as .xlsm, it will be impossible for someone who doesn’t have the newest version of Excel to open it without editing it first.

Read more about this topic

• Microsoft Excel for the Web will support comment filtering
• Microsoft Excel web app gets a new feature to share links to Sheet views
• Microsoft’s Excel web app gains new Copy/Paste experience

4. Delete file protection 

1. Click on the Review tab and select Protect Sheet or Protect Workbook. 
2. Select Unprotect Sheet or Protect Workbook and enter the password.
3. Clicking Unprotect Sheet automatically removes the password from the sheet.

If you don’t know the password for the sheet, it means you’re not the owner of the sheet. In such instances, you have no choice but to create a new sheet.

5. Use a different sheet for solving formulas

If nothing seems to work, your last resort may be to try a different sheet for your formulas. Your current sheet may be corrupted, preventing you from saving any formulas.

If all of your formulas are disappearing after saving, it’s probably because the workbook contains hidden cells. It could also be that there is other data that Excel can’t see.

When you start a new sheet, you eliminate any hidden errors or hidden formulas that could have been the issue.

Aside from formulas disappearing in Excel, the program may present its own problems. Users recently complained of Excel not opening files, and if you’re in the same predicament, we’ve got you covered.

In other instances, you may find that Excel is using too much CPU, so don’t hesitate to visit our helpful guide to solve this issue once and for all.

Let us know if you have been able to solve these issues using any of our solutions above in the comment section below.