Forum Discussion
Formulas Not Calculating in Workbook
Hello, I have a very large workbook with many formulas across different sheets. I currently have this workbook set to manual calculations only. And it is saved in a Sharepoint Directory with autosave enabled.
When the source data for this workbook is updated. None of the formulas are updating when
1. The Calculate Now button is pressed.
2. The workbook is set back to automatic calculation.
3. When the work book is manually saved and closed.
I have noticed another issue. When I go to File > Options. The Options dialogue never appears.
I am wondering if anyone else have experienced this issue, and if so have been able to successfully troubleshoot the problem?
This happens every now and then. Today's solution found on the Formulas Tab - for some reason the SHOW FORMULAS function in Formula Auditing was selected. Another nymph in the soup. So to speak. As campers dining in the dusk - we eat a lot of tiny nymphs. Both kinds - tiny insects and greek myths. Be well.
I have had a similar problem. I struggled to find the solution which is why I am replying here. I found that my Excel version had been updated and I needed an Application.CalculateFullRebuild, or Ctrl + Alt + F9.
https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculatefullrebuild
I hope this helps someone.
If you're experiencing issues with formulas not calculating and the Options dialogue not appearing in Excel 365, you can try the following troubleshooting steps:
- Check Calculation Settings:
- Click on the "Formulas" tab in the Excel ribbon.
- In the "Calculation" group, ensure that the calculation mode is set to "Automatic." If it's set to "Manual," formulas won't recalculate automatically.
- Click on the "Calculation Options" button and select "Automatic" if it's not already selected.
- Check Dependencies and Circular References:
- In the "Formulas" tab, click on the "Formula Auditing" group.
- Use the "Trace Dependents" and "Trace Precedents" buttons to identify any cells that might be causing issues.
- If there are circular references, Excel might not be able to calculate the formulas correctly. Resolve any circular references.
- Check for External Links:
- If your workbook contains external links to other workbooks or data sources, ensure that the links are valid and accessible.
- In the "Data" tab, click on the "Edit Links" button to review and update any external links.
- Repair Office Installation:
- If the issue persists, you can try repairing your Office installation to fix any potential issues with Excel.
- Open the Control Panel on your computer.
- Navigate to "Programs" or "Programs and Features" and locate Microsoft Office.
- Right-click on Microsoft Office and select "Change" or "Repair."
- Follow the on-screen instructions to repair your Office installation.
- Update Excel and Windows:
- Ensure that you have the latest updates installed for Excel and your Windows operating system. Updates often include bug fixes and improvements that can resolve issues.
Additional Information – Links:
How to Fix Excel Formulas that are Not Calculating or Updating
- Just wanted to add a few more possibilities shared in another post, including that the data is stored as Text. (I had to delete a row, set it to "General" or "Number" formatting, then re-try my formulas again to get them to work.)
https://techcommunity.microsoft.com/t5/excel/formulas-displaying-as-text/m-p/3818576/highlight/true#M191220- "I had to delete a row, set it to "General" or "Number" formatting, then re-try my formulas again to get them to work.)" -thanks this worked
