Excel has its limits

As an engineer, I have built many a spreadsheet in my career. Some basic, some with macros and some just plain enormous. I am convinced that because of the low cost and relative ease of use excel offers to the engineer, it has become the leading engineering instrument in the world today.

Should engineers be using excel so much? I am not so sure… It is definitely a great tool, especially at the beginning stages of a project. Its quick and inexpensive to make changes, which is exactly what you need in the conceptual phase of developing calculations.

My main grievance with using Excel is that these spreadsheets grow and become extremely valuable but also completely unmaintainable. These large spreadsheets hold managers hostage as empires are built. Could you imagine figuring out a 20 sheet monster built by someone that has left the company?

Based on my experience, I have identified some pitfalls of using Excel:

1. File based version control is always a problem, as its nearly impossible to build up calculation histories.
2. Hidden logic and hard to read IF statements.
3. Split logic and calculation between spreadsheets and macros (if and when used). It is a primary programming principle to keep logic in one place.
4. If you are using pivot tables you should seriously consider using a database.

However, in this post I would like to focus on the following flaw: data duplication due to lack of data relationships and lack of object abstraction.

A database is purely a set of tables that are interconnected by data relationships. A set of rules are established which define the nature of the data contained in each row and how inter-table connectivity is managed, these rules are called Classes or Models. The name says it all: these rules classify the data.

We try to abstract all logic and calculations to the model, thus keeping things neat and organized. However in a spreadsheet the logic and data governance is scattered all over the place – even worse – it is hidden by default and you have to click on a cell to see what the logic or governance rules are.

If you are developing large spreadsheets and are frustrated by how difficult they are to maintain and manage, you should seriously consider looking at a more formal development tool.