Ideal System Configuration for Excel Operations

[TJ7009]

Can somebody suggest ideal system configuration which can handle complex excel files without any interruption.

I am a finance professional and my work is purely based on excel. Usually the file size is raging from 20 MB to 5 GB. About 3-4 of such files are open in the system at a time. These file consists of more than 20 sheets and all these sheets are interlinked. The files having function like, IF, SUMIFS, SUMPRODUCT with multiple conditions,INDEX AND MATCH, VLOOKUP, HLOOKUP, and Tables. Majorty of the times the files are hanging with the message "NOT RESPONDING". This is serious problem and it kills my productive time. I feel this is due to inadequate system configuration.

It will be very helpful if someone can suggest the best/ideal system configuration to work with complex excel files without any interruptions.

Thanks in advance,
TJ

[AliGW]

Hard to tell. What are you currently working with? How are the formulae configured?

[avk]

It is difficult to say anything without seeing your files. But I feel that, why not use pivot table, instead of lot of formula.

[TJ7009]

Hard to tell. What are you currently working with? How are the formulae configured?

Hello AliGW,

I am working with Intel Core I5, 8GB Ram , 64 Bit. I hope i haven't understood the second part of the question correctly!

Thanks in advance,
TJ

[AliGW]

It really would be easier if you shared a sample workbook so that we can see what type of formulae you are using. It may be that you need to use VBA.

[MrShorty]

With several lookups and sumproducts with multiple conditions, I would guess that you will gain more by rewriting the spreadsheet to eliminate bottlenecks. Since you have not shared anything about what your formulas are actually doing, we cannot give specific recommendations. If they will help a couple of past examples from this forum:

In this one we rewrote the spreadsheet so that it went from taking a week to calculate to taking 5 seconds to calculate: https://www.excelforum.com/excel-pro...lculation.html
https://www.excelforum.com/excel-pro...t-process.html
One where I suggest using a helper cell/column to reduce duplicated effort: https://www.excelforum.com/excel-for...ows-excel.html

See if the ideas in any of those discussions will help speed your spreadsheet up.