Excel 365, Calculating (8 threads), lag time = unproductive

[fkarim74]

I have a very large file, 25MB, with a LOT of different formulas & over 15 sheets.
I am basically building a manufacturing company database within Excel for use.
All of a sudden I started seeing the calculating (8 threads) message and the lag time is unbearable.

- Power Query connects additional information from inside the file.
- I have multiple V-look Up & X-lookup formula that works nicely.
- I have multiple sum & sumifs works perfectly in my Report sheets.
- I have multiple Pivot tables for my different kinds of reports. .
- used indirect and address formula for my helps to make some report.

Any advise on how best to troubleshoot?
Your knowledge is welcomed and revered.

Regards
Fazlul Karim

[AliGW]

INDIRECT is notorious for slowing things dow, as are full column references.

[Glenn Kennedy]

1. Avoid INDIRECT and other volatile functions, wherever possible.

2. Check which functions are using whole column references. They're fine with SUMIFS, but not with SUMPRODUCT, etc.

3. Conditional formatting is volatile. If you've used it extensively, it can cause problems, too.

4. If this started "suddenly"... what did you add JUST before the problem arose?

5. It's hard to diagnose further without seeing it.

[TMS]

To add to point 2 above, Array Formulae are not good with full column references, especially if many columns are included.

You may find that using 365 functions are more effective (if you're not using them already.

Do you need 15 sheets? Could the data not be in one sheet and thus avoid using INDIRECT and ADDRESS.

Could you have inadvertently copied any formula/formulae to the end of the column?

[fkarim74]

Thank you Glenn. I will try your suggestion one by one. Hope its improve

[fkarim74]

yes. thank you,
I will try to avoid the formula paste special by value of earlier all data.
Use the formula only for the current month's data,
Hope its helps a lot