SOLVED

Excel calculation stopped working

Copper Contributor

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!

 

15 Replies

@alexyaro 

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:

  1. 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."
  2. 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.
  3. 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.
  4. 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."
  5. Excel Version and Updates: Ensure that you are using the latest version of Excel. Sometimes, issues are resolved with software updates.
  6. 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.
  7. 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.
  8. 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.

 

Could you upload your workbook?

@Detlef_Lewin, I could but don't see an option to include a file in my reply.

@alexyaro 

Since you have ruled out some common causes, you might want to try the following:

  1. Remove INDIRECT Functions: Edit the formulas and replace or simplify the usage of INDIRECT functions.
  2. 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.
  3. Test in a New Worksheet:
    • Create a new worksheet.
    • Replicate the formulas and structure.
    • Check if the issue persists in the new sheet.
  4. Review Named Ranges:
    • Go to the "Formulas" tab.
    • Click "Name Manager" to review and edit named ranges.
  5. 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."
  6. Check for Excel Updates:
    • Go to "File" -> "Account."
    • Under "Product Information," click "Update Options" -> "Update Now."
  7. 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.

You probably haven't met the criteria for upload permission yet.
Try upload to a cloud service (OneDrive, Google Drive, etc.).
best response confirmed by alexyaro (Copper Contributor)
Solution

@alexyaro 

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.

 

 

Thank you - this is great. How can I dynamically extend the table to include new rows?
Just add new data under last row. The table grows. And no need to update formulas in the dashboard because of the structured references.
Got it - thank you very much!

@alexyaro 

I believe the real problem was the option "Enable iterative calculation".

Uncheck it.

excel-iterative-calculation

@Detlef_Lewin , I checked my Options - Iterative calculation was/is unchecked while the problem persists.  I am referring to the old copy using INDIRECT functions.

@alexyaro Indeed, @Detlef_Lewin 's solution is recommended. But, since you still wonder why your original formulas don't work, consider this.

 

All the criteria ranges in the formulas are quite odd. They span two columns as if you created them first to be something like C35:C296 and then inserted a column but leaving the second column reference  hard-coded with "C". The result is a reference to D35:C296 which Excel then 'translates' to C35:D296. And you probably want this to be D35:D296.

 

I corrected all of these in your original file and all seems to work as intended. See attached.

Thank you! I wrote the formulas a while ago and you're probably right - I inserted a column at one point. I do remember needing to do a 'backward' reference (D:C vs C:D) as that was the way to make the formula calculate correctly. I don't remember the specifics. I am surprised and still curious about the reason the formulas started to exhibit this strange behavior now. The formulas, as odd as they are, worked for several months, at least.
1 best response

Accepted Solutions
best response confirmed by alexyaro (Copper Contributor)
Solution

@alexyaro 

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.

 

 

View solution in original post