1st Feb // 2016

When to replace a spreadsheet with a database driven solution

When to replace a spreadsheet with a database driven solution

Designing a database driven solution may seem like a daunting process but in the long term it can be a real benefit.

We've been building computerised solutions to replace paper records and ledger books since the mid 1990s but now more and more systems are being created to replace spreadsheets.

While spreadsheets are a good way to keep track of data, as soon as you start sharing them or they get too big, there is a risk that they can not be relied on. It just takes a few key strokes to delete data or enter incorrect data. Some spreadsheet solutions are better than others, for example Google Drive offers excellent version control with the ability to roll back. But if you are entering data manually without verification there is a risk of, for example, getting a formula wrong. With Microsoft Office, you can get an exclamation mark if the formula looks different from nearby cells, but you are still relying on manual entry that can't be easily checked.

A database driven system allows you to specify what type of data is expected and is more robust. Formulae are put in as part of the development and can't be changed by mistake. Our process also ensures that the solution goes through rigourous testing to assure you of the integrity of the data. Once you have a spreadsheet, that gives us an excellent basis for creating a database driven solution.

There's obviously a cost in creating a system but compare that with losing data or having incorrect data. You also need to consider how important the spreadsheet is to your organisation and how much you would miss it if it went wrong.

You will be able to add charts or graphs to the system as well and don't forget that a web based solution can look a lot better than a spreadsheet.

As a rule of thumb if you're regularly working with a spreadsheet that is more that 500 rows or 20 columns - especially if it's shared or you are copying data between worksheets - it's worth looking at a database driven solution. We specialise is web based solutions which can be accessed by multiple users from anywhere but with strict security controls.

One of our recent projects began life as a two and a half thousand row spreadsheet which is now a web based tool. You can see on our case studies pages.