When looking to understand how to make better spreadsheets, we need to be clear that spreadsheet formulae are a programming language.
Professional software developers learnt long ago that they need to apply good practices to their work. Spreadsheet developers need to learn the same lessons.
Excel: The world's most widely used programming language
According to Microsoft, Excel is:
...the world's most widely used programming language. Excel formulas are written by an order of magnitude more users than all the C, C++, C#, Java, and Python programmers in the world combined.
Microsoft, LAMBDA: The ultimate Excel worksheet function
The worksheet grid is an integrated development environment
We can think of Excel as a type of integrated development environment (IDE). Other programming languages have various types of IDE:
- Command line interface.
- Plain text editor.
- Editor with intellisense (like the VBA IDE).
- An environment that writes large chunks of code for you (e.g., Visual Studio, when you add a database object to a project).
- A graphical interface to drag-and-drop blocks (like Scratch).
The Excel IDE looks different to most other programming languages, being based on writing code in a grid, and using grid addresses in place of variable names. Nonetheless it is a programming language.
Excel formulae are even Turing Complete, meaning that they have the expressive power equivalent to other programming lanaguages like C, C++, C#, Java, and Python.
Building a spreadsheet is equivalent to writing software. That is, spreadsheet formulae are like program code. Consequently, software development issues such as bugs, data integrity, version control, error handling, and testing also apply to spreadsheets.
