Forum Discussion
Excel calculation stopped working
There were over 900 formulas and most of them used INDIRECT() - which is a volatile function.
There also were 100+ conditional formatting rules.
I deleted the CF-rules.
I put the data in an Excel table.
I changed the formulas to use structured references.
Everything runs smooth.
The file contains two dashboards.
One in your style and one using an Excel table.
NikolinoDE, thank you for your suggestions. Unfortunately, they didn't solve my problem:
- Changing Autocalc had one effect: turning it on slowed the movement around the sheet. It didn't affect the 'disappearing calc' issue.
- The formulas didn't have any circular references, data types, or volatile functions before this issue began and I didn't alter the formulas since. The formuals contain INDIRECT functions.
- I tried saving the worksheet as a new file - no effecct.
Since you have ruled out some common causes, you might want to try the following:
- Remove INDIRECT Functions: Edit the formulas and replace or simplify the usage of INDIRECT functions.
- Excel Calculation Log:
- Go to the "Formulas" tab.
- In the "Formula Auditing" group, check "Calculation Options" and ensure it's set to "Automatic."
- Click "Formula Auditing" -> "Show Calculation Steps." This will open the Calculation Area to the left of the formula bar.
- Test in a New Worksheet:
- Create a new worksheet.
- Replicate the formulas and structure.
- Check if the issue persists in the new sheet.
- Review Named Ranges:
- Go to the "Formulas" tab.
- Click "Name Manager" to review and edit named ranges.
- Excel Repair:
- Go to "Control Panel" -> "Programs" -> "Programs and Features."
- Right-click on Microsoft 365 (or Microsoft Office), choose "Change," and then "Quick Repair" or "Online Repair."
- Check for Excel Updates:
- Go to "File" -> "Account."
- Under "Product Information," click "Update Options" -> "Update Now."
- Check for External Links:
- Go to the "Formulas" tab.
- Click "Name Manager" and check for any external links.
Remember to save your work before making significant changes and, if possible, create a backup of your workbook. The steps mentioned are generally applicable to Excel 365 subscription-based version. If none of the above resolves the problem, as was already recommended to you by Mr. Detlef Lewin , insert the file (without sensitive data) or share it via OneDrive.