Calculating (12 Threads): 0%

[Danger_SF]

Good morning!

Office 365 user.

With the addition of a few new formulas, my spreadsheet has become very laggy. I now see the above subject line in the status bar of my application. We're talking 15-20 second delays with every change to the drop-down list.

Any suggestions to relieve this?

[#2]

without an example sheet (without personal/private information) that is rather difficult but a few quick pointers:
avoid volatile functions like INDIRECT, RAND, and OFFSET if and when possible
avoid referencing entire columns
minimize lookup functions (i.e. use helper cells or defined names instead of having everycell re-calculate the same value, have that calculation in only 1 location and have all the other cells point to that
use newer, more efficient functions were possible (i.e. use XLOOKUP instead of LOOKUP or VLOOKUP but the EXCEPTION is to NOT use IFS when you have binary non-array calculations because IFS will calculate ALL the conditions while IF() will only calculate the needed parameter as long as there aren't arrays and such)
I'm sure there are more, but that is a start.

[#3]

Thank you. The latest formula that really put me over the edge is:

=SUM(--(IF(SITES!B:B=B2,COUNTIFS(SITES!B:B,B2,SITES!C:C,SITES!C:C),0)>1))

I assume COUNTIFS are equally problematic to IFS?

I also have a large number of formulas on one of my tabs that references entire columns (B:B, for instance). Thanks again.

[#4]

That formula looks particular suspect. the COUNTIFS:
COUNTIFS(SITES!B:B,B2,SITES!C:C,SITES!C:C)
looks like it may be doing an array of arrays as you conditional is an array. Do you REALLY want SITES!C:C to be checked for the whole column of C or just C2 like you are doing for column B?

i.e.:

COUNTIFS(SITES!B:B,B2,SITES!C:C,SITES!C2)

I also HIGHLY recommend defining the data range as a table (HOME -> Format as a Table) if possible so you can then use Table references INSTEAD of using the entire column so something like:

COUNTIFS(SITEtable[Col B],B2,SITEtable[Col C],C2)

[#5 · Accepted solution]

I concur. The killer here is B:B / C:C. Observed this myself only 2 days ago.

[#6]

This help is HUGE! Thank you. Fixed, and running 100 X faster. Now I will go through and convert the rest of the similar references. Appreciate you!!!!