Excel slowing down by using Array formulas

[bearcub]

I've started using a number of new array formulas in some of files. I have noticed that my screen refresh time has slowed down.

Years ago array formulas using CSE and noticed that Excel slowed down. So, I am thinking that the new array formulas is doing the same.

Has anyone been experiencing this with the new Array formulas (like Unique, filter, Let, etc.). Are their ways to use the formulas to minimize cell refresh?

 

[6StringJazzer]

There are some rules of thumb for array formulas, like don't reference entire rows or columns. But this is going to depend on what you are doing. What formulas are you using?

 

[bearcub]

I am using, for now, mostly using the unique and filter functions thou I have Let and ByRow formulas as well.

I have an issues in the past using array formulas at other companies which tended to slow down the file calculation time.

I was just wondering if the new array formulas had the same issues as the old CSE array formulas.

What are some of the other rule of thumb rules besides entire rows or columns? I am aware of those 2.

 

[6StringJazzer]

The new array formulas have the same issues because they work the same way. The only difference is that Excel automatically detects when a formula can be interpreted as an array formula, so it's not required to hit CTRL-ALT-DEL.

Those are the main concerns for array formulas. Overall sheet design could also be an issue, and whether you need to use array formulas at all. I can't give advice on that without knowing a lot more about your file. In some cases a VBA solution may be better if you do not need continuous recalculation.