PowerQuery. It's a tool built into Excel. It's a GUI that wraps an almost purely-functional DSL designed for ETL and data munging, called the M language. You can either use the GUI or write the code directly. It has first class functions and closures and normies are programming in it. It's great. More people should know about it.
Btw it's kind of funny seeing so many HN users, many of whom must be working on software that competes with Excel either directly or indirectly, who are so unknowing of the full capabilities of Excel, capabilities that are the bread and butter of any e.g. financial analyst, or logistics manager, or any smart non-programmer white collar worker. Maybe this "hacker repulsion field" is the secret of its dominance -- you can't compete with it if you never learn what it can do.
Your aside is exactly why I wanted to use a spreadsheet. It's the only tool that has that market penetration for non-programmers, and I wanted to see what made it tick. It seems like that may have backfired by not using Excel, however.
If you enable PowerPivot (hidden in the COM addons settings) you'll get a quite capable data analysis tool for small-ish data with M + DAX with some rough edges.
They're the only reason I actually like to use Excel now. PowerBI has those natively built-in in a more modern iteration but is not as flexible (little direct data entry capability). That said PBI is ultimately meant for reporting.
I would love to understand more what limitations you faced with GSheets that are non-existent in Excel. I used to think the same but then I built some pretty complex computations in GSheets that I could not easily replicate in Excel. And U have built some complex stuff in both.
Extending on this, if you want to make really powerful business software — ingest and emit Excel sheets that other departments can use in their own flows.
PowerQuery is powerful, but also compromised by the fact it is so closely tied to Excel. If you want to do data transformation tasks (such as joins) on your Excel data and you don't want to learn R or Python+Pandas then you might be better off doing it in a no-code tool designed explicitly for the task, for example Alteryx (if you have deep pockets) or Easy Data Transform (if you don't).
There is another reason that is less often openly disclosed, but very much in play. I recently proposed RStudio or Python/Panda combo to deal with some of the limitations of Excel ( which also conveniently bypasses Alteryx's cost ), but the response I got was somewhat surprising, because it revolved around and I am paraphrasing 'will others be able to learn and use it'. And here is the rub. Excel has a lot of online tutorials and in some ways it is a known quantity. And, well, some people don't want to learn new thingsshrug.
No code data wrangling tools are significantly easier to learn than R or Python+Pandas. That is their main selling point (inevitably they trade some flexibility for this) and there is a range of them to cover all budgets.
Btw it's kind of funny seeing so many HN users, many of whom must be working on software that competes with Excel either directly or indirectly, who are so unknowing of the full capabilities of Excel, capabilities that are the bread and butter of any e.g. financial analyst, or logistics manager, or any smart non-programmer white collar worker. Maybe this "hacker repulsion field" is the secret of its dominance -- you can't compete with it if you never learn what it can do.