Excel 2016 crashes with large sheets and filtering
When opening a large file and start filtering, work with that filter, use a second filter dn remove one of the filters Excel crashes
Hi all, the original issue reported in this thread was due to Win10 changes in Accessibility. The fixes for this have been released for Excel 2013, Excel 2016 and Office 365 subscribers in March.
For more information, this thread has the discussion: http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/excel-filtering-problemmatic-after-windows-10/f6f34982-d4e0-47b3-bd37-772fd51e0ff3.
Ashvini Sharma [MS]
Try update Office https://docs.microsoft.com/en-us/OfficeUpdates/office-updates-msi with latest updates.
Thanks Amy. That formula works.
Spencer Dodson commented
I am new to Excell but how does Amy's formula work? Like what cell do I place it in? Do I change the A's to whatever column I checking for duplicates?
THANK YOU AMY! That formula worked and saved my laptop from being thrown out the window!
I have found this happens to me ALL the time, so instead of using conditional formatting to identify dupes and then try and filter these by colour. I use a formula to identify dupes instead and then filter them. I use this formula for anyone interested =IF(COUNTIF($A$2:$A$21812,A2)>1,"Dupe"). I have found this the best way to work around it for now till there is another solution bit ridiculous!
Delia Watson commented
I've been experiencing this issue for a while already. It even happens when two files open, with one containing conditional formatting, if I filter or sort on the second open file then excel crashes.
I found a blog somewhere (can't find it just now) whereby the solution was to REMOVE ANY COMMENTS -- in your header rows for example. I now delete all comments before filtering and I have not had this miserable problem ever since. Don't know if it is coincidence, but might be worth a try ...
I am also having this issue, have been for a few months. Costing my company a boatload of money.
This is still an issue. using office 365 and win10 16gig RAM, I5 processor, 500gig SSD with over 250gig free space. I use conditional formatting to find duplicates, and then when I either try to sort by font (or color), or sort by background color it locks up excel "not responding" even waiting 20 minutes it's still locked up.
It does the same thing if trying to filter on color. Just a single column, 182,000 rows. Tried on both a 32bit and 64bit install of excel and it does the same thing in both of them.
Henry Garcia commented
I have had this issue for over a year as well. I added RAM to my laptop thinking it would help. Even installed 64 bit version. I cannot express how much time I have spent waiting for spreadsheets to sort only having it crash on me and start over until it works. Please let us all know if there is any plan to correct this issue or let us know what the solution is. Thank you.
D Nash commented
Same, this is very frustrating and has been happening to me for a number of weeks, sort it out Microsoft!
Mark Williams commented
Where can I get these updates?
My job is to filter large amounts of data and I am having the same issue It seems to freeze then the filter reappears on the screen,everytime I enter the text search it does the same think displaying a blank text box. I need to provide counts for my clients. Can anyone suggest another program other than Microsoft where I can filter my excel data?
I have been trying to do this count for the last 1 hour with the same issue. I am only working with 84,445 within my search and I do not consider this enough records for the system to fall over. Can you please help.
Still encountering issue August 2019. When will this be fixed?
Filtering tables using filter buttons or formulas causes Excel 365 in Windows 10 to become unresponsive and crashes. I could have anywhere from 10,000 to 500,000. I am using Excel while using slicers are instantaneous. Is there a fix for it?
No updates available (on latest 365) and this happens regularly. Keep it up Redmond, you're begging Open Office or Libre to harvest your users. Heck even G Sheets is becoming an alternative.
Quit adding **** like Linked In into my spreadsheets and maybe this stuff wouldn't happen. Nobody needs to share spreadsheets on Social Media. You clearly don't even use your own products.
And now it's August 2019 and the issue still happens!
It's now July 2019, still not fixed.
I am having this issue as well. I turned off auto calculation and only wanted to sort one table and my whole sheet has been locked up for 20 mins. Using Excel 2016 64bit on fully loaded developer laptop. Oh finally... it stopped processing! When will this be fixeD?
J Rapoport commented
I have several clients who are experiencing this issue as well. They try to filter a large spreadsheet and Excel goes non-responsive forcing the user to kill Excel. IN which faerie kingdom does Microsoft believe that they have resolved this issue??
Having the same problem on "large" (16k rows) sheets. The second I press filter the whole thing becomes unresponsive.