Home

excel slow on large number of entries with calculations

%3CLINGO-SUB%20id%3D%22lingo-sub-116292%22%20slang%3D%22en-US%22%3Eexcel%20slow%20on%20large%20number%20of%20entries%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116292%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Experts%3C%2FP%3E%3CP%3Ethis%20is%20an%20issue%20that%20refuses%20to%20go%20away%2C%20i%20noticed%20that%20with%20an%20excel%20sheet%20with%20large%20number%20of%20rows%2C%20usually%20more%20than%2010K%20rows%2C%20along%20with%20a%20simple%20calculation%20like%20countif%2C%20causes%20excel%20to%20stop%20respinding%20showing%20%25%20counter%20progressing%20slow%20and%20even%20slows%20down%20the%20PC.%20i%20have%20an%20I7%20CPU%2C%2016GB%20RAM%20and%20256%20HD%20SSD%20so%20i%20think%20its%20quite%20a%20powerful%20machine%2C%20yet%20still%20slow.%3C%2FP%3E%3CP%3Eis%20there%20a%20way%20to%20wrokaround%20this%3F%3C%2FP%3E%3CP%3Eaccess%20or%20and%20sql%20db%20is%20fairly%20limitied%20as%20i%20will%20have%20to%20spend%20a%20lot%20of%20time%20learning%20sql%20queris%2C%20and%20sometimes%20its%20gust%20to%20much%2C%20to%20get%20similar%20results%20to%20index%2C%20Xlookup%20and%20other%20function%20in%20excel.%3C%2FP%3E%3CP%3Eplease%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-116292%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eexcel%20slow%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-123825%22%20slang%3D%22en-US%22%3ERE%3A%20excel%20slow%20on%20large%20number%20of%20entries%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-123825%22%20slang%3D%22en-US%22%3Ebean%20there%2C%20done%20that%20%3A-)%20anything%20else%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-123789%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20slow%20on%20large%20number%20of%20entries%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-123789%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergey%3C%2FP%3E%3CP%3Enot%20sure%20how%20to%20formulate%20the%20question....%3C%2FP%3E%3CP%3Efor%20example%20the%20following%20countif%3A%26nbsp%3B%20%3Dcountif(%24a%242%3A%24A%2450000%2Ca2)%2Cis%20calculation%20time%20linear%3F%20e.g.%20number%20of%20rows%20multiplied%20by%20the%20sub%20calculations%20where%20each%20time%20you%20have%20to%20go%20over%20the%20whole%20array%20to%20count%20each%20entry%3F%3C%2FP%3E%3CP%3Eis%20the%20time%20doubled%20if%20i%20do%20countif%3CSTRONG%3ES%20%3C%2FSTRONG%3Efor%202%20columns%3F%3C%2FP%3E%3CP%3Eis%20the%20calculation%20time%20linear%20until%20some%20point%20and%20than%20in%20becomes%20exponential%20of%20sort%20of%20simply%20take%20so%20much%20time%20that%20it%20looks%20like%20it%3F%3C%2FP%3E%3CP%3Eand%20for%20any%20case%2C%20is%20there%20a%20number%20from%20which%20it%20becomes%20not%20efficient%20to%20run%20this%20type%20of%20calculation%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-123081%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20slow%20on%20large%20number%20of%20entries%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-123081%22%20slang%3D%22en-US%22%3E%3CP%3E10K%20rows%20shall%20not%20be%20an%20issue%2C%20if%20only%20array-like%20formulas%20and%20VLLOKUPs.%20Just%20in%20case%20here%20are%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Foffice%2Fmt709003.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Foffice%2Fmt709003.aspx%3C%2FA%3E%20some%20tips%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-123074%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20slow%20on%20large%20number%20of%20entries%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-123074%22%20slang%3D%22en-US%22%3Ethanks%2C%20but%20i%20have%20already%20exhausted%20these%20options%20%3A-)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-116441%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20slow%20on%20large%20number%20of%20entries%20with%20calculations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116441%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20two%20tips%20that%20may%26nbsp%3Bhelp%20you%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%231%26nbsp%3BUse%20all%20processors%20for%20calculating%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20840px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F22159i07EDC9694FE14CD3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel%20Options.png%22%20title%3D%22Excel%20Options.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%232%26nbsp%3BTurn%20off%20Automatic%20Calculation%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F22160iF28AAA184E9FC8B9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Turn%20off%20Automatic%20Calculation.png%22%20title%3D%22Turn%20off%20Automatic%20Calculation.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Yossi Even Zur
Occasional Contributor

Hi Experts

this is an issue that refuses to go away, i noticed that with an excel sheet with large number of rows, usually more than 10K rows, along with a simple calculation like countif, causes excel to stop respinding showing % counter progressing slow and even slows down the PC. i have an I7 CPU, 16GB RAM and 256 HD SSD so i think its quite a powerful machine, yet still slow.

is there a way to wrokaround this?

access or and sql db is fairly limitied as i will have to spend a lot of time learning sql queris, and sometimes its gust to much, to get similar results to index, Xlookup and other function in excel.

please help

5 Replies

Hi,

 

Here are two tips that may help you:

 

#1 Use all processors for calculating

 

Excel Options.png

 

 

#2 Turn off Automatic Calculation

 

Turn off Automatic Calculation.png

 

 

thanks, but i have already exhausted these options :-)

10K rows shall not be an issue, if only array-like formulas and VLLOKUPs. Just in case here are https://msdn.microsoft.com/en-us/library/office/mt709003.aspx some tips

Hi Sergey

not sure how to formulate the question....

for example the following countif:  =countif($a$2:$A$50000,a2),is calculation time linear? e.g. number of rows multiplied by the sub calculations where each time you have to go over the whole array to count each entry?

is the time doubled if i do countifS for 2 columns?

is the calculation time linear until some point and than in becomes exponential of sort of simply take so much time that it looks like it?

and for any case, is there a number from which it becomes not efficient to run this type of calculation?

bean there, done that :-) anything else?
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies