Also it is common knowledge that MS tries to get rid of open spreadsheets - only allowed competition are google sheets. If your Excel doesnt have array formulas - please get a newer version. Even 2003 had them.
I dont claim that Excel doesnt have problems, also it could be better - but there are ways to make more robust calculations.
On a side note Excel and the dreaded (obsolete?) VBA are one of the last democratic things that allow non-programmers to build things.
Personally I hate tables, you can in fact add some random crap in one of the random cells.
Lots of your criticism are valid, Excel could definitely be better. But now we dont have better.
As someone trying to learn SQL, the syntax of typical queries(one page long, not some basic ones) is horrible - the language is completely not designed for tasks when you glue 20 tables together... are those queries easy to understand / inspect? IMHO no.
it's the other side of the coin for "last democratic thing that allows non-programmers to build things" (which I sympathize with). you can't just ignore the capabilities of the userbase demographics.
>Also it is common knowledge that MS tries to get rid of open spreadsheets - only allowed competition are google sheets.
it's not microsoft's fault LibreOffice doesn't have tables. OnlyOffice has them. there's a feature request thread in the LibreOffice archives, someone asked for excel-style tables to be implemented. the LO devs didn't even know what they were(!), people had to explain it to them. dev response was lukewarm. there doesn't seem to be any great push to implement it.
https://bugs.documentfoundation.org/show_bug.cgi?id=132780
>If your Excel doesnt have array formulas - please get a newer version. Even 2003 had them.
I mean the new ones. the dynamic array formulae with the spill behaviour. SEQUENCE, FILTER, SORT, UNIQUE, etc. those are only in office 365.
>As someone trying to learn SQL, the syntax of typical queries(one page long, not some basic ones) is horrible - the language is completely not designed for tasks when you glue 20 tables together... are those queries easy to understand / inspect? IMHO no.
no disagreement there, SQL is not a good language. have you tried M?
Also you act as if there werent any programming horror stories. Very often programmers want to rewrite the code they got from othera.
**
I am not sure which one is M and which one is DAX. Those are the PowerBI ones. In my opinion they are horrible. The idea of "steps" makes sense, but the gui that breaks steps is horrible. Also the syntax is horrible.
**
MS at some point had pivot tables, pivot tables with data model, power pivot, powerBI stand-alone app, "old" sql query editor. Now they try to add powerBI standalone back to Excel.. partially (on a side note I hate that poweBI exports from stand alone app to Excel are so horrible), and they got rid of the old SQL editor. If you want to write SQL in Excel you might as well use notepad..
PowerBI was supposed to be a step forward. OK it allows to build things, but is in my opinion horrible. M/DAX are horrible to use for typical operarions such as;
Changing source
Combining two sources
Joins
Joins on joins on joins
Joins
Syntax is supposed to be explicit, but it is horrible to write and horrible to read
I'm one of the other "amateur" commenters, and I know about all of those things you mentioned:
>array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection).
Problem with all of these is some combination of:
- they suck
- boomers don't know how to use them
- you can't use them for compatibility reasons
Array formulae: boomers don't use them, and the good ones aren't backwards compatible with older Excel versions or LibreOffice/Sheets (major problem when dealing with ppl outside the org)
Formula inspector: I've never found this to be useful
Excel marks inconsistent formulae ... inconsistently. It fights you when you're trying to do something slightly non-standard but otherwise perfectly legit, because it's just pattern-matching. Leads to alarm fatigue. And boomers ignore it.
Data validation for selectors: boomers don't do this (are you seeing a pattern?). More importantly, they don't interact properly with macros.
Pivot tables for summaries: boomers actually do know these, but the problem is they're not reactive. They don't refresh when the data source changes.
Locking cells: there's no convenient way to tell at a glance which cells are locked and which aren't (yes I know about the "Find All" way, that's not convenient).
Named cells: inconvenient, no namespacing, you can have duplicates of the same name that refer to different things, or dead ones that point to nothing, and there's no good way to debug it.
Tables: these are great, but sadly aren't compatible with LibreOffice/Sheets. Column names cannot be dynamic. Also they will fill down a formula even when you don't want them to, there isn't a way to turn that off afaik.
>You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them.
People can and will fuck up even the best-made sheets in irrecoverable ways, that are difficult/impossible to detect automatically within Excel. Yes, we locked all the cells except for inputs, password-protected the sheets, used data validation, used tables, used named references, we did all that stuff you're meant to do and more. It wasn't enough: we'd get back the filled-in sheets and there would be broken references every-fucking-where. I've forgotten exactly what they were doing, but whatever it was, I had to write Python scripts to diagnose it by looping through all the cells and comparing the formulae and sheet layout against a known good copy.
(Yeah I know I know, it's "source: trust me bro". I'm racking my brain to remember what exactly they were doing to cause the corruption, and it's eluding me. But I'm not making it up.)