I agree that certain functions can cause problems. Actually the biggest culprit in my kind of work is VLOOKUP(), which I know could be replaced with INDEX()/MATCH() but less conveniently.
I find that filling down with a function like VLOOKUP(), whilst you have multi filters on, is the real killer. And yes you can rethink your workflow so this is not necessary, but if you have to do that you have immediately reduced the utility of Excel, which is easy manipulation. If you have to 'paste special' your subset into a new sheet first, then it means you can't easily test different filters or scenarios. If I have to do something more complex to get my answers, then I would begin my manipulations in sql instead.
I still find that once Excel gets anywhere near its row limit (circa 1M), there is a big increase in memory usage, which I have found can cause a big slow down on ordinary desktops that normal office workers have (i3/i5 4GB ram). Curiously (and without extensive testing) I feel this is much worse in later versions of Excel (after 2007 and the BIG change), and my Excel colleagues agree.
It sounds like your workflow is quite different from mine. Specifically:
1) As you say, you can use INDEX/MATCH instead of VLOOKUP. I only choose the latter if I'm 100% sure I'm not going to save the sheet. I don't know whether VLOOKUP has performance problems, but at some point someone's going to insert a column and break the references.
2) I've never had a filter (I presume you mean filtering on the content of columns by clicking on the column headers) which affects the value of a formula. I tend to have one-way data flow: inputs are clearly marked (ideally, but not always, on their own sheet(s)), and you use filtering only on output sheets.
I'm not sure about RAM usage. I use Excel much less these days for things with that many rows.
I find that filling down with a function like VLOOKUP(), whilst you have multi filters on, is the real killer. And yes you can rethink your workflow so this is not necessary, but if you have to do that you have immediately reduced the utility of Excel, which is easy manipulation. If you have to 'paste special' your subset into a new sheet first, then it means you can't easily test different filters or scenarios. If I have to do something more complex to get my answers, then I would begin my manipulations in sql instead.
I still find that once Excel gets anywhere near its row limit (circa 1M), there is a big increase in memory usage, which I have found can cause a big slow down on ordinary desktops that normal office workers have (i3/i5 4GB ram). Curiously (and without extensive testing) I feel this is much worse in later versions of Excel (after 2007 and the BIG change), and my Excel colleagues agree.