it's not excel that's the problem - it's you or your replacement 6 months later trying to reverse engineer the iterated solution that you willed into being... ;-)
"Excel THINKS IT CAN DEAL with many gigs of data thanks to PowerPivot and the addition of an in-memory database."
It's so cute when I hit ctrl-downarrow on a blank sheet and Excel sends me to row 1,048,576. Wishful thinking because if I ever filled 1M cells with functions, well... lololololol... time to use JMP...
If you're putting the data in Excel's new data model, this is no longer a problem. I regularly have files with tens of millions of rows of data which pivot tables can work against with sub-second aggregations across multiple columns.
Actually much earlier in 2012, when Excel first shipped with xVelocity branded PowerPivot. It supports a new data model that reminds me of Microsoft Access in some ways (drag & drop relationships etc). This is a whole different beast from copy/pasting data into sheets - in fact, the data doesn't show up in sheets by default and you usually have to add other things (like pivot tables) to take advantage of it.
Microsoft is a sleeping giant in BI self-service right now, and the things they've been "quietly" adding (only if you don't follow them) are actually very compelling. I actually run a Windows VM on my MBP just so I can run Power BI.
Is this power bi..? Ah, I think it is. I’ve really tried to get up to speed with it, but it feels so alien to normal excel in many ways. I feel an existential dread when I drop a column in power BI.
But yeah, it’s very powerful. It’s very sql like in the way you have to treat actions and data.