Sep 03 2019 02:30 AM - edited Sep 03 2019 02:34 AM
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.
Sep 03 2019 03:03 AM
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.