Excel suddenly crashing when deleting rows or columns
I’ve suddenly started experiencing a frustrating random problem with Excel 2010, which I run in Windows 10 under Parallels Version 12.1.3 (41532) on an iMac.
Whenever I need to delete columns or delete rows from an Excel spreadsheet, at some unpredictable point part way through the process Excel crashes every time.
After rebooting, the error message always says:
“One or more invalid conditional formats were removed from the workbook”.
In the original file that failed, I’ve gone right through and thinned out and simplified all of the conditional formatting, but to no avail.
I have also spent hours trying to isolate when the problem started occurring, but it is not just on files I have edited recently or after any known event, and can happen on any old file I try.
I tried doing a System Restore only to find that it had been turned off so that was useless.
I did run a repair of Office 2010 but nothing improved.
I have tried running Excel in Safe Mode but the problem still occurs.
This has cost a lot of lost time and unreliability with my work and I am no closer to a solution.
The only thing I did notice is that there seems to have been a major update of Office 2010 and Windows in the last day or two.
Has anyone any advice on how I can fix this please?
Thankyou for your assistance.
Whenever I need to delete columns or delete rows from an Excel spreadsheet, at some unpredictable point part way through the process Excel crashes every time.
After rebooting, the error message always says:
“One or more invalid conditional formats were removed from the workbook”.
In the original file that failed, I’ve gone right through and thinned out and simplified all of the conditional formatting, but to no avail.
I have also spent hours trying to isolate when the problem started occurring, but it is not just on files I have edited recently or after any known event, and can happen on any old file I try.
I tried doing a System Restore only to find that it had been turned off so that was useless.
I did run a repair of Office 2010 but nothing improved.
I have tried running Excel in Safe Mode but the problem still occurs.
This has cost a lot of lost time and unreliability with my work and I am no closer to a solution.
The only thing I did notice is that there seems to have been a major update of Office 2010 and Windows in the last day or two.
Has anyone any advice on how I can fix this please?
Thankyou for your assistance.
Is the problem reproducible on other computers? I run Excel 2010 on Parallels on a MacBook Pro if it would help.
Is any of the conditional formatting being set by macros? If so, be aware that you generally need to select the cell being formatted before the macro applies the conditional formatting. The need to select the cell first is especially important if the conditional formatting uses relative addressing.
It may also be that the relative addresses in a conditional formatting "formula is" criteria are falling off the edge of the worksheet if you delete columns or rows.
Does the workbook contain a gazillion styles? Style corruption may be causing the crashes despite the error message to the contrary.
Is any of the conditional formatting being set by macros? If so, be aware that you generally need to select the cell being formatted before the macro applies the conditional formatting. The need to select the cell first is especially important if the conditional formatting uses relative addressing.
It may also be that the relative addresses in a conditional formatting "formula is" criteria are falling off the edge of the worksheet if you delete columns or rows.
Does the workbook contain a gazillion styles? Style corruption may be causing the crashes despite the error message to the contrary.
ASKER
Try to open File / Options / Trust Center / Add-ins and disable all add-ins. Restart Excel and check if problem persists
Thankyou @Tom, I tried that but it did not work.
If this did not help Go to Option / Add-Ins select from drop down menu COM Add-Ins and disable all.
I found that idea elsewhere and so it was already set with all (two) of them disabled and it did not help either.
ASKER
Thankyou @byundt for your feedback
Good idea. I just tried some files on my Toshiba Kira Ultrabook and I can delete rows and columns anywhere I like with no problem (just as I used to do on my iMac until yesterday).
No, the most complex conditional formatting is set by a formula, e.g. if cell > 0, make it red or some such.
No this is not the case. It happens on sheets with no link to any conditional formatting.
No, I don't use styles at all.
It remains a mystery.
Is the problem reproducible on other computers? I run Excel 2010 on Parallels on a MacBook Pro if it would help.
Good idea. I just tried some files on my Toshiba Kira Ultrabook and I can delete rows and columns anywhere I like with no problem (just as I used to do on my iMac until yesterday).
Is any of the conditional formatting being set by macros? If so, be aware that you generally need to select the cell being formatted before the macro applies the conditional formatting. The need to select the cell first is especially important if the conditional formatting uses relative addressing.
No, the most complex conditional formatting is set by a formula, e.g. if cell > 0, make it red or some such.
It may also be that the relative addresses in a conditional formatting "formula is" criteria are falling off the edge of the worksheet if you delete columns or rows.
No this is not the case. It happens on sheets with no link to any conditional formatting.
Does the workbook contain a gazillion styles? Style corruption may be causing the crashes despite the error message to the contrary.
No, I don't use styles at all.
It remains a mystery.
No, I don't use styles at all.Pretty much nobody uses styles. But they nevertheless multiply like rabbits as a workbook is edited over the years, especially so when you import worksheets from other workbooks. I've encountered workbooks with thousands of styles, none of which were used by anybody, and when they were deleted the sun came out, a rainbow appeared and the birds started chirping.
My tool of choice for expunging excess styles is available for free in the Windows Store. It is the only app I have ever gotten from the Windows Store. https://www.microsoft.com/en-us/store/p/xlstylestool/9wzdncrfjptg
That said, the fact that your file works well on another computer suggests that your real problem may be the Office 2010 install, and possible conflicts with other software, missing pieces, registry errors, etc. Lucky for you, Microsoft has a free tool to diagnose such issues called OffCAT (Office Configuration Analyzer Tool). It uses a database that is updated monthly. You can download it from https://www.microsoft.com/en-us/download/details.aspx?id=36852
ASKER
I'm starting to read a lot about the recent update KB3178690 causing random crashes in Excel.
My problems have now extended to not only when deleting rows and columns but on other unpredictable occasions too.
I've realised the failures seem to usually (always?) relate to my automatic backup files (.xlsb) because for my most important files I have the option selected for:
'File, Save as, Tools, General options, Always create backup'
The error log includes the text:
<Errors were detected in file 'C:\Users\ (my details etc) \AppData\Roaming\Microsoft
I have uninstalled KB3178690 and so far the problem seems to be gone.
I'll be rather annoyed if this has been the cause of the problem, as I've lost over a day’s pay trying to fix this.
Here are some articles about KB3178690:
https://social.technet.microsoft.com/Forums/en-US/b0066e1c-1c47-44aa-99d9-b450f9b8be35/excel-2010-update-kb3178690-has-caused-excel-to-freeze-while-running-an-excel-application-via-vb?forum=excel
https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Excel-for-Windows-49d932ce-0240-49cf-94df-1587d9d97093?ui=en-US&rs=en-US&ad=US&fromAR=1
https://support.office.com/en-gb/article/After-installing-KB3178690-in-Excel-2010-calculation-may-result-in-a-hang-or-crash-14cd3b5c-aa2a-4fba-ba0a-414dedeb3ba0
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/kb3178690-causing-excel-2010-to-crash/a949afa1-0102-4dfa-8634-884b2d7d057b?auth=1
https://www.reddit.com/r/excel/comments/5zqvnm/psa_some_users_report_excel_security_update/
My problems have now extended to not only when deleting rows and columns but on other unpredictable occasions too.
I've realised the failures seem to usually (always?) relate to my automatic backup files (.xlsb) because for my most important files I have the option selected for:
'File, Save as, Tools, General options, Always create backup'
The error log includes the text:
<Errors were detected in file 'C:\Users\ (my details etc) \AppData\Roaming\Microsoft
I have uninstalled KB3178690 and so far the problem seems to be gone.
I'll be rather annoyed if this has been the cause of the problem, as I've lost over a day’s pay trying to fix this.
Here are some articles about KB3178690:
https://social.technet.microsoft.com/Forums/en-US/b0066e1c-1c47-44aa-99d9-b450f9b8be35/excel-2010-update-kb3178690-has-caused-excel-to-freeze-while-running-an-excel-application-via-vb?forum=excel
https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Excel-for-Windows-49d932ce-0240-49cf-94df-1587d9d97093?ui=en-US&rs=en-US&ad=US&fromAR=1
https://support.office.com/en-gb/article/After-installing-KB3178690-in-Excel-2010-calculation-may-result-in-a-hang-or-crash-14cd3b5c-aa2a-4fba-ba0a-414dedeb3ba0
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/kb3178690-causing-excel-2010-to-crash/a949afa1-0102-4dfa-8634-884b2d7d057b?auth=1
https://www.reddit.com/r/excel/comments/5zqvnm/psa_some_users_report_excel_security_update/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have had no further problems since uninstalling KB3178690.
Restart Excel and check if problem persists
If this did not help
Go to Option / Add-Ins select from drop down menu COM Add-Ins and disable all