Forum Discussion
Solved
Excel calculation stopped working
I have been using a spreadsheet that summarizes data in a dashboard at the top of the sheet. It had been working fine for several years. Today I added one more row to the underlying data (296 rows - not a long list) and the sheet started exhibiting strange behavior:
- The calculations stopped displaying.
- The formulas are still part of the cell and are correct - they calculate when I press F2+Enter.
- As soon as I press F9 to recalculate the sheet, some (not all) of the formulas stop calculating.
I recorded a video that displays the problem:
https://www.loom.com/share/2f2baeec6ec2454c81ce08941e0696bb?sid=d5f37263-c7bc-4137-b673-abad0e783c82
My laptop is brand new with 64GB of RAM. I would not expect it to be a memory issue.
Thanks in advance for your help!
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.
It seems that your video link is not accessible to me, but based on your description, here are a few suggestions to troubleshoot the issue:
- Recalculation Settings: Check the calculation settings in Excel. Make sure that automatic calculation is enabled. You can find this setting in the "Formulas" tab under "Calculation Options."
- Circular References: Verify if there are any circular references in your formulas. Circular references can cause issues with calculations. If found, resolve them by adjusting the formulas.
- Data Types: Ensure that the new row you added does not contain any data that might be causing errors in calculations. For example, unexpected characters or data types could disrupt calculations.
- Check for Errors: Use the "Error Checking" feature in Excel to identify any cells with errors. It can be found in the "Formulas" tab under "Error Checking."
- Excel Version and Updates: Ensure that you are using the latest version of Excel. Sometimes, issues are resolved with software updates.
- Excel in Safe Mode: Try running Excel in Safe Mode. This can help identify if the issue is related to any add-ins or external factors. You can open Excel in Safe Mode by holding the "Ctrl" key while launching Excel.
- Workbook Corruption: It's possible that the workbook has become corrupted. Save a copy of your workbook and try opening it in a new session of Excel.
- Volatile Functions: If you are using any volatile functions (e.g., NOW(), TODAY(), etc.), consider limiting their use, as they can lead to performance issues.
If the issue persists, providing more details about the formulas and the specific behavior may help in providing a more accurate solution. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
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.
