Home

Excel takes too much time to open file containing multiple sheets and huge data

%3CLINGO-SUB%20id%3D%22lingo-sub-834172%22%20slang%3D%22en-US%22%3EExcel%20takes%20too%20much%20time%20to%20open%20file%20containing%20multiple%20sheets%20and%20huge%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834172%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20an%20excel%20with%20multiple%20sheets%20and%20each%20with%20close%20to%20700%2C000%20rows%20of%20data%26nbsp%3B%20per%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20In%20the%20calculation%20sheet%20uses%20data%20from%20other%20sheets%20and%20use%20complex%20functions%20like%26nbsp%3B%3CSPAN%3Esumif%20on%20multiple%20cells%20%3C%2FSPAN%3Eto%20update%20the%20value%20in%20calculation%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDuring%20the%20processing%20excel%20do%20not%20respond%20and%20my%20CPU%20utilization%20peaks%2C%20Memory%20usage%20is%20normal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20system%20with%20various%20configuration%20latest%20being%20a%2016GB%2C%20I7%20Desktop%20with%20windows%2010.%20Also%20tried%20Azure%20server%20with%20DS%2013%20V2%20configuration%20(8%20Core%20vCPU%2C%2056%20GB%20RAM)%20in%20both%20cases%20CPU%20peaking%20is%20observed%20while%20memory%20usage%20is%20normal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20in%20making%20this%20work%20is%20appreciated%2C%20as%20my%20projection%20is%20depended%20on%20the%20report%20made%20out%20of%20this%20calculation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-834172%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834218%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20takes%20too%20much%20time%20to%20open%20file%20containing%20multiple%20sheets%20and%20huge%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402449%22%20target%3D%22_blank%22%3E%40hegdedileep%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20presume%20your%20workbook%20must%20have%20lots%20of%20volatile%20functions%2C%20like%20INDIRECT%20and%20OFFSET.%20Avoid%20them%2C%20if%20you%20can%2C%20by%20using%20CHOOSE%20and%20INDEX%20instead.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
hegdedileep
Occasional Visitor

I am having an excel with multiple sheets and each with close to 700,000 rows of data  per sheet.

 

The data In the calculation sheet uses data from other sheets and use complex functions like sumif on multiple cells to update the value in calculation sheet.

 

During the processing excel do not respond and my CPU utilization peaks, Memory usage is normal.

 

I tried using system with various configuration latest being a 16GB, I7 Desktop with windows 10. Also tried Azure server with DS 13 V2 configuration (8 Core vCPU, 56 GB RAM) in both cases CPU peaking is observed while memory usage is normal.

 

Any help in making this work is appreciated, as my projection is depended on the report made out of this calculation.

 

1 Reply

@hegdedileep 

I presume your workbook must have lots of volatile functions, like INDIRECT and OFFSET. Avoid them, if you can, by using CHOOSE and INDEX instead. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies