When spreadsheets just won’t cut it

January 7, 2015

alt text

When you want to crunch some numbers on your computer, the most tempting option is often to build a spreadsheet. And why not? It’s quick and easy and needs no specialist knowledge. Even coders, who could write a program that suits their needs perfectly, will be tempted by this option and will be perfectly happy having to adapt their needs a little to the spreadsheet model.

And it doesn’t stop at number crunching. When the well-known software engineer and blogger Joel Spolsky did some research into how people use spreadsheets, he found that the majority of people actually use them to make lists. This is understandable; with a spreadsheet you can take lists further than a simple text editor, taking advantage of functions like sorting. You can even make your list more complex by structuring them into multiple columns, thus turning it into a table. At this point your file starts turning into some kind of spreadsheet/database hybrid.

But one must be cautious. Eventually you may find, after continuous tweaking and addition of data, that maintaining your spreadsheet becomes painful. Changing formulas causes undesired knock-on effects; the file starts running slowly; there’s just too much packed into multiple sheets. If this happens it makes sense to consider something more powerful and easier to use. But what?

Databases

Your data may well be suitable for a database. If you need to store information that has some semblance of structure to it, then putting it into a database can be well worth the extra effort. Why?

  • Databases provide simple tools for playing with data: storing, sorting, querying, filtering etc.
  • It’s much easier to handle large amounts of data.
  • Some databases let you build reports, so you can still present the data in an appealing way.
  • Assuming it’s set up correctly, it’s much more convenient for multiple people to use a single database. Why tolerate the hassle of passing copies of a spreadsheet file around via email when you could instead put a database on a server and let everyone connect to it via the web?

However, databases have a significant weak spot. When you need to process data according to your business rules, your options are quite limited. Aside from queries, the best that some databases can offer is giving you the option of writing stored procedures. These are subroutines where you can write algorithms for processing your data, but they can be quite awkward to use and their capabilities vary wildly between different databases.

It may well be worth considering the next step up…

Applications

Any basic application consists of a user interface, a backend (the “invisible” part of the program where all the business logic is stored), and a data store. The data store could simply be a file, but it’s very often a database.

Compared with constructing a spreadsheet, writing an application is not necessarily a trivial step. For one thing, you need to be able to program or find a programmer who will write your application for you. Nevertheless there are many benefits that come with taking this approach:

  • For one thing, you get all the benefits of a database (since an application can be thought of as a database combined with some business logic).
  • There’s no awkward adaptation of your approach to the spreadsheet/database-oriented ways of thinking. Instead, you can simply code your application in any programming language and paradigm you like.
  • You can build a nice, user-friendly interface.
  • The program and the data are properly separated (meaning you can think in terms of your business objects rather than rows of a table).
  • An application is much more easily testable. You (or the programmer) can write a suite of tests that continually verify the expected behaviour of the whole program and alert you whenever a new change introduces a bug.
  • You can easily debug application code when something goes wrong.

There are some obvious costs to building an application. One is the time required; developing an application takes more time, and when time is money then applications are the pricier option – tweaking a formula in a spreadsheet takes a fraction of the time needed to update code and rebuild an application. But never forget that the time argument swings both ways. Since a well-designed application will reduce the time it takes the user to do their work, all those time savings will accumulate in the long run. The more users you have, the less of a worry increased development time becomes.

Conclusion

When next you consider creating a spreadsheet, think about what needs you are trying to meet and – especially important – how those needs might evolve in future. Then it could well make sense to forego the spreadsheet altogether and jump straight to a more sophisticated option.

If you really are just doing some fairly simple number crunching then a spreadsheet is probably the right solution now and forever. But if your number crunching is not so straightforward, if you’re trying to capture some business rules in a spreadsheet, then you should consider a more sophisticated option. Business rules have a tendency to change and expand over time and you need a suitably powerful way of handling that.

A programmer might jump straight for an application-based approach. Nothing wrong with that, but not everyone is a programmer. Non-programmers unable to take the application route therefore have the option of using a database instead, especially if their needs are oriented more towards data capture than number crunching. Note that not all databases are suitable for novices, but a few like LibreOffice Base (a free software version of Microsoft Access) and SQLite are simple enough. The good thing about choosing a database over a spreadsheet is that the process of moving from a simple database to a full-blown application is simpler, should your needs ever outgrow what a database can offer.

Next time…

In a following blog post, I’ll move from theory to reality and describe a concrete case where a client requested that we re-engineer their spreadsheet into a full-blown application.

About the author

Dr. Karl Beecher specialises in challenges requiring a high-quality engineering approach and brings an analytical point of view to every software project. Possessing a great sense for user-centered development, he has created products ranging from customised ERP systems for industrial production facilities up to extensions of established development environments for thousands of experts.