Building on this, when purist engineers trash Excel, I point out that Excel cell formulas are the only case of Functional Programming going truly mainstream.
I found it interesting, fun and great. Still, I would stick to using Excel for the personal analysis of data and not as the language/tool on which software is developed: data & programming logic intermixed, the default way to index cells, the difference between normal cells and array cells, and tables, etc., the fact that some things are just not possible to do fully programmatically without creating your own VB functions and therefore asking recipients of what looks like data to accept running arbitrary code in their machines, etc.
I could also just use MATLAB but I'm more used to using python.
Sure, SQL and most environments can tell you distinct values of a column and their counts, but they are much slower and more cumbersome to explore with. I often start out with broad filters then go through many more specific ones depending on what I see from the first filter.
About a year ago Excel added =SWITCH, which is effectively {Logic Test | Output} pairings executed until one is true. Many if/then/else nestings could be eliminated with it.
This might sound like a hack, but being able to see intermediate results makes debugging complex expressions easier too, so in a sense it is working as intended.
Ctrl+Tilda toggles show/hide formulas, which helps identify input cells when you inherit a messy spreadsheet.
Also the number formats and locale settings can cause quite a bit of trouble, if its not English/US. Alot of data warehouse setups output files with U.S formats even in Europe.
It's not perfect, I wish it would automatically format that way, but it certainly makes maintenance easier.
https://insider.office.com/en-us/blog/let-names-in-formulas-...
And it's a hack, but you can put text comments inside an N() function and add them into your formulas.
https://support.microsoft.com/en-us/office/n-function-a624ca...