| > because spreadsheets are impossible to scale, which in turn makes the processes that rely on them impossible to scale. 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... |