I can also draw parallels to literate programming in this: The code and visualization of what the code is doing are very tightly integrated.
For example, in my experience Excel is really good at providing job security to those who develop complex spreadsheets. But the cost to the organization tends to be enormous as well, because spreadsheets are impossible to scale, which in turn makes the processes that rely on them impossible to scale.
What are you replacing the spreadsheets with, desktop to web apps?
I worked on a team that helped business users who had "outgrown excel", i.e., they had hung themselves with the rope Excel provides. Almost always their scaling problems were solved simply by better Excel practices: better management of the calculation mode; setting the RTD throttle interval to 1-2 seconds; replacing Bloomberg's streaming data function (BDP), with the native alternative {=RTD("BLOOMBERG.RTD...)}; optimizing the division of labor between what is done on the sheet with formulas versus with vba\xll code; meta programming, i.e., creating all or part of your calc sheets and formulas with code so that you get the understandability and observability of Excel formulas while avoiding the things that are hard to do with formulas, such as grouping, joining, filtering, looping; making 3rd-party add-ins workbook-specific such that they're not always on (many of these add-ins listen to application-level events like selection_change and on_calculation which can diminish the performance of all open Excel models, even the ones that don't use that add-in).
He is a smart guy, but refuses to learn even basic Python or R, despite doing some very significant statistical work in an area related to preventing machines harming humans.
I just wonder, even if initially perfect, how many spreadsheets have been unknowingly perverted by someone sitting on the mouse, or a pet cat treading the keyboard.
I have at times remarked that typical Microsoft Office installations should exclude Excel! :-)
And I am saying all this about Excel even after being a power user myself and the primary author of the OP. :-)
Mathcad, Mathematica, etc. could be good alternatives if not Python or R.
Spreadsheets are entirely possible to scale, that just tends to be a skillset in and of itself, and domain experts organically creating a complex spreadsheet likely don't have the background in process engineering and software design principles to do so themselves.
Generally speaking, you can usually refactor an unmaintainable and complex spreadsheet to mimic software engineering best practices, all without dropping down to VBA. Leveraging named ranges[1], locked cells[2] and formulas[3], data validation[4], and error handling[5]. Combined with some defensive validation checks, you can generally sort out the complexity issue nicely.
Additional enhancements (based on Excel 2010+) can be made using tables and structured references[6], factoring out "data" worksheets into their own workbooks[7] and linking to them from the "user" workbook, adding relational integrity via a data model[8], or scaling data size via PowerQuery[9] (which stores data within the Excel file in a highly compressed, columnar format that transparently gets processed by a local instance of the same VertiPaq engine[10] that powers SQL Server Analysis Services)
You can also drop down to VBA or Javascript[11] if you truly want/need to jump out of the rails of the built in options above. Or in more common cases (which leads to the hell-to-maintain spreadsheets that are more common), if you want to bypass all of the nifty built-in functionality above and do something quick-n-dirty. But if you leverage the above capabilities, you can mature a spreadsheet-based solution quite well and have a battle-tested, stable PoC that can be handed off to a software developer for migrating into a more permanent application.
[1] https://trumpexcel.com/named-ranges-in-excel/
[2] https://support.office.com/en-us/article/lock-cells-to-prote...
[3] https://support.office.com/en-us/article/display-or-hide-for...
[4] https://support.office.com/en-us/article/more-on-data-valida...
[5] https://www.exceltactics.com/definitive-guide-excel-error-ty...
[6] https://support.office.com/en-us/article/overview-of-excel-t...
[7] https://www.microsoftpressstore.com/articles/article.aspx?p=...
[8] https://support.office.com/en-us/article/create-a-data-model...
[9] https://en.wikipedia.org/wiki/Power_Pivot
[10] https://www.microsoftpressstore.com/articles/article.aspx?p=...
[11] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...
Although if it fits your usage, PowerQuery and M[2] can be even more performant, if for no other reason than the data being in a more efficient/compressed format. With the nice side effect of creating logic that's transferable as-is from Excel to PowerBI or SQL Server Analysis Services (making for a clean migration path as your solution matures).
[1] https://www.microsoft.com/en-us/microsoft-365/blog/2009/03/1...
Really not the intended use or appropriate criticality management of Excel as a tool, let alone the O/S it runs on, yet it is done.