Excel Performance optimisation tips!

Working in demand planning I have got it the point where I am making some pretty advanced files using a suite of techniques.

My files often have lots of rows, with lots of Columns of complex formula including with sumifs, xloopup, ifs & Let. I’ve not advanced to using tables regularly though as I find the constraints & syntax annoying but am trying to get there & have started using power query to blend data for output analysis.

The problem I am encountering is I filter ALOT drilling down into product groups etc, & excel tends to ‘hang’ a lot with ‘Not Responding’.

Now I’m not sure it’s due to an underpowered machine (intel core i7 HP Elitebook) or, more likely lots of complex formula referencing ranges or tables.

My question to the hive brain: share your optimisation tips & tricks!

-Can Lamda combined with Let speed things up?

-Are Tables vital to speeding up complex sumifs & lookups?

- are match helper columns combined with Index leaner & faster than xlookup?

Hit me with best tips & tricks!