How to stop Excel from converting formula into text?

Question

Considering many peoples comments on the web, it seems that there is still a problem with Excel that it is converting all cells with formulas to plain text format? On large spreadsheets this is a *** big *** problem. I have this problem now for several months. I work with Excel every day so it is affecting my work. Is it something which has changed about 'autoformat' settings? It is happening on *** all *** my spreadsheets when I reopen them. This seems to me like it might be a security update which has gone wrong?

Answer 1

If Excel is converting formulas into text, it may be due to the formatting of the cells. Here are some steps you can take to prevent this from happening:

1. Check Cell Formatting: Ensure that the cells where you are entering formulas are not formatted as text. You can do this by selecting the cells, right-clicking, and choosing Format Cells. Set the format to General or Number.
2. Re-enter Formulas: After changing the cell format, you may need to re-enter the formulas. You can do this by editing the cell and pressing Enter.
3. AutoCorrect Options: Sometimes, AutoCorrect options might affect how Excel handles formulas. Check the AutoCorrect settings under File > Options > Proofing > AutoCorrect Options to see if there are any settings that might be causing this issue.
4. Check for Add-ins: Some Excel add-ins might interfere with how formulas are processed. Try disabling any add-ins to see if that resolves the issue.
5. Update Excel: Ensure that your Excel is up to date, as updates can fix bugs and improve functionality. You can check for updates under File > Account > Update Options.

If the problem persists after trying these steps, it might be helpful to consult Microsoft support or community forums for further assistance, as there could be specific issues related to your version of Excel or recent updates.

---

References:

• Cell references in formulas
• How to control and understand settings in the Format Cells dialog box in Excel
• Microsoft Security Bulletin MS09-067 - Important
• Microsoft Security Bulletin MS11-072 - Important
• Microsoft Security Bulletin MS09-067 - Important

Answer 2

Thank you for these suggestions to 'correct' the problem. They do not solve the problem since next time the spreadsheet is opened, the problem is back. It is sadly very time-consumming to make these corrections every time I open a spreadsheet.

Answer 3

Hi @Gibb, Alasdair,

Thank you for posting your question in the Microsoft Q&A forum.

I'm sincerely sorry for any inconvenience and troubles this situation may have caused you.

Please notify me if I've misunderstood anything about your situation. Based on your sharing, Excel worked fine with the formulas in the beginning but when you saved the file and reopen it all the cells having formulas were converted to plain text format.

To help you resolve this concern, I suggest you should follow this guideline to narrow down the issue step by step:

1- Check file type

• Double check if your excel file is saved as CVS, TXT files. Formulas will be converted into text in these file types since they don't support formulas format.

2- Delete Book.xltx in XLSTART folder

• Open File Explorer and navigate to this path C:\Users<YourUserName>\AppData\Roaming\Microsoft\Excel\XLSTART
• If you see Book.xltx or Sheet.xltx folder in there, delete it.
• Restart your computer and open Excel again to see if the issue persists

3- Reset Excel default settings

Press Windows + R and type regedit then OK 

• In the bar, paste this address to navigate to the correct path: HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel 
• Right-click the Excel key and press Delete. After that restart Excel to see if the issue persists. 

4- Revert the problematic file

For the files that having problem with formulas are converted to text, you can change it back to formula by

• Select the affected range > go to Home tab > Number Format > change Formatting to General

• Also in Home tab, go to Editing > Find & Select > Replace > set Find what: = and Replace with: = > press Replace all

I hope this information is helpful. Please follow these steps and let me know if it works for you. If not, we can work together to resolve this.    

Thank you for your patience and your understanding. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. 

I look forward to hearing from you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have any 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.