>The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming.

I'm one of the other commenters you called "amateur hour". 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 know how to 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. Sometimes you have formulae that are fine, then you close and re-open the file and they're marked as inconsistent. Or vice versa. It leads to alarm fatigue. And boomers don't know what it means.

Data validation for selectors: boomers don't do this (are you seeing a pattern?). More importantly, they don't interact properly with macros. And it's difficult to express mutually-constrained inputs.

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 (well there is a kind of namespacing but it's implicit), 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 labels cannot be dynamic. You can't put the summary row at the top, only the bottom. They will fill down a formula even when you don't want them to, there isn't a way to turn that off afaik. They don't interact properly with array spill formulae.

>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 (it might have been from dragging cells around), 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.