Excel Crashes on Sorting

%3CLINGO-SUB%20id%3D%22lingo-sub-3005072%22%20slang%3D%22en-US%22%3EExcel%20Crashes%20on%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3005072%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20running%20Excel%2016.55%20on%20a%20Macbook%20Pro%20'19%20with%20Big%20Sur%2011.6.%20Yesterday%2C%20I%20installed%20Microsoft%20365%20package.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20keeps%20crashing%20and%20freezing.%20Whenever%20I%20try%20to%20sort%20by%20cell%20color%20a%20rather%20large%20file%20(rows%26gt%3B38k%2C%20two%20columns)%2C%20it%20freezes.%20Excel%20produces%20an%20error%20report%20when%20I%20force%20quit%20the%20program.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20seen%20Excel%20lag%20in%20the%20past%2C%20but%20the%20performance%20is%20very%20disappointing%20compared%20to%20what%20one%20expects%20from%20Macbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3005072%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3005633%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Crashes%20on%20Sorting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3005633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1229434%22%20target%3D%22_blank%22%3E%40Harman365%3C%2FA%3E%26nbsp%3BOn%20a%20MBP%20myself.%20On%20oldy%20from%202012.%20working%20on%20a%20file%20with%2080000%2B%20rows%2C%20as%20many%20formulae%2C%20but%20minimal%20formatting%20with%20a%20file%20size%20just%20over%203.5MB.%20I%20admit%20it's%20not%20the%20fastest%20file%20but%20it%20doesn't%20hang%20up%20Excel.%20Also%20on%20MS365%2C%20by%20the%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat's%20in%20you%20file%3F%20Much%20(conditional)%20formatting%20perhaps%20or%20many%20formulae%20with%20INDIRECT%20or%20OFFSET%3F%20File%20size%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am running Excel 16.55 on a Macbook Pro '19 with Big Sur 11.6. Yesterday, I installed Microsoft 365 package.

 

Excel keeps crashing and freezing. Whenever I try to sort by cell color a rather large file (rows>38k, two columns), it freezes. Excel produces an error report when I force quit the program.

 

I've seen Excel lag in the past, but the performance is very disappointing compared to what one expects from Macbook.

 

 

4 Replies

@Harman365 On a MBP myself. On oldy from 2012. working on a file with 80000+ rows, as many formulae, but minimal formatting with a file size just over 3.5MB. I admit it's not the fastest file but it doesn't hang up Excel. Also on MS365, by the way.

 

What's in you file? Much (conditional) formatting perhaps or many formulae with INDIRECT or OFFSET? File size?

@Riny_van_Eekelen Thanks for your reply. The file is just 1 MB, but yes, I have applied conditional formatting ('highlight duplicate values') on both columns. 

@Harman365 That should not be a problem. What if you remove the CF and just sort in whatever order. Better then?

@Riny_van_Eekelen It works fine if I sort A-Z after removing the CF. But I need to sort after using conditional formatting to highlight duplicates.

 

Other solutions (such as only sorting a few hundred rows at a time) do not work. To explain better here's come context:

 

Column_1 is an old list of items

Column _2 is a new list of items

 

For any non-duplicates, they are either new entries (and must be tagged as such) or deprecated entires (and must be deleted from record). 

 

P.S. I just shifted to using google sheets for the time being, and they work smoothly with the same data file.