Edit: My contention is that spreadsheets are in fact, very opaque, and it is incredibly difficult to check that every part of a spreadsheet is actually doing what its users think it is supposed to be doing. Specifically, I posit that it is harder to check a spreadsheet, than it is to check a "traditional" program in a normal programming language, which works on pure data.
I don't think I can agree with this. The average codebase I see is pretty terrible readability-wise. Nasty levels of function nesting, tons of variation of behavior based on arguments passed into functions, etc. I assume the average Excel spreadsheet is also pretty nasty, but it has the advantage of having a debugger "built in" vs looking at the code generally without actually seeing the data flowing through it (e.g. who runs every code review they do through a debugger?).
I also think this ignores that a "traditional" program replacing Excel would still need to get data from somewhere, which commonly implies a SQL database or a data warehouse that speaks SQL these days, and complex SQL is itself NASTY to verify/fully grok through reading alone.
1) Code formatting - spreadsheet formulae are generally presented all on one line with minimal spacing and no syntax highlighting. Any mildly complex formula should ideally be presented a) across multiple lines, b) with indenting to show function nesting depth, and c) in colour.
2) Copy and paste - spreadsheet formulae are generally copy-and-pasted every time they're used (i.e. on every row), rather than being defined once and referenced in each place they're used. Some spreadsheets are now good at highlighting if one formula is out of place in a column of otherwise-similar formulae, but it's hard to check if formulae are supposed to be "the same" in different columns, or different worksheets, or in different spreadsheet files.
Answering your edit, not all spreadsheets are opaque either. We design ours to be audited.
Auditors don’t show up to critique your formulas. They are looking at business processes, which means usually the data that flows between things. If it gets to the point where they are digging that deep, they have a finding and are trying to assess the severity.
Software, including major business systems are often fucked up. There are major companies using RPA software to robotically use excel to correct some fubar in Oracle financials that wont get fixed for a few years.
The beauty of excel is that the business speaks it.
