Is it time to ditch that spreadsheet?

I’ve been saying to clients for years that spreadsheets are evil. They can start out as a good idea, especially when you just have a few records to manage. And they are highly accessible - there is almost no learning curve to typing values into cells. But it doesn't take long before someone decides to use Excel as a tool manage large volumes of data and that’s when things can turn nasty.

About 10 years ago we did some work on the opposite side of the world for an overseas government and I remember visiting a hospital to meet with some of the clinical staff. They described how details of patients coming into the hospital would be recorded in a spreadsheet, at which point they would be assigned a unique hospital id. Of course people would visit more than once but only needed one id, so the reception staff would search the spreadsheet to see if the patient had been before and already had an id. The current visit would then be added to the next empty row in the spreadsheet. The hospital saw around 1000 patients per year and at the time of our visit the spreadsheet was reaching 7,500 rows. Navigating around large spreadsheets on a small screen can be a bit of a nightmare and the opportunities for accidentally and unknowingly changing or erasing the data in a cell are not insignificant. I could have written them an Access database in a couple of evenings which would have provided adequate functionality in a safe and manageable form but there would have been no-one to maintain it once we left.

More recently a client who we had worked with for many years inherited a process which involved some serious spreadsheet hell. They were a quality assurance organisation who periodically visited regional sites who provided training, to identify and record training concerns. Actions were set which sites were required to undertake within an agreed timeframe. At three monthly intervals the regional body would request progress updates from the sites, in the form of a partially pre-completed spreadsheet, where the site had to supply the missing information. The process was made more challenging because the spreadsheet required responses from different departments within any one site and therefore had to be shared. The completed spreadsheets returned by the sites to the regional body were then merged into a single monster spreadsheet which was submitted to the national body. This final stage alone took two people, one month every quarter to carry out. It was, in short, very labour intensive.

The scope of the project as presented to us had been to design and build a database application to manage the visits, concerns, actions and progress updates. But the client’s vision of the process still involved capturing progress updates from sites through a spreadsheet, for which the system would generate the partially pre-completed form and then (somehow) upload and merge the responses from the returned spreadsheets back into the database. It was this last step which gave us the heebie-jeebies. We couldn’t, without a lot of work, generate from the database a partially completed spreadsheet with locked down areas and drop down lists in cells. So there would be no possibility of controlling or validating the data at the time of input. All of that would have to be undertaken between uploading the Excel file returned from the site and importing the responses into the database. It felt like a project that would turn into a support nightmare.

Our solution was a web based app, available to users at both the regional monitoring body and the individual sites being monitored. Security was controlled though individual user accounts. Visibility onto the data was controlled through roles assigned to those user accounts. Sites were alerted by email when it was time to provide progress updates along with a link directly to the feedback page from where they could enter details of progress and upload files as supporting evidence. Submission of the completed feedback alerted the regional body that it was ready for review. Where the responses are not sufficient, the progress update is transferred back to the site with details of the further evidence required.

The new approach was rapidly accepted by everyone and as user experience and confidence grew, ideas and suggestions to further streamline the process came in from the sites and the regional body. Data quality and turn-around times were dramatically improved and the creation of the final quarterly report to the national body reduced to a single button press.

Reports could be pulled off the system by sites and the regional body at any stage of the QA cycle, in the form of Excel spreadsheets and Word documents. These reflected the current state of play in terms of open conditions and completed/outstanding/overdue progress updates and actions.

In terms of cost, we were able to quote less to deliver this system than if we had had to write something to validate and merge spreadsheet data back to the database. The on-going support costs have been less, with most of the quarterly support budget being used for feature enhancements rather than to debug issues arising from uncontrolled (i.e. ‘bad’) spreadsheet data.

What could have been a project from hell turned into a ‘thing of great beauty’, in our eyes at least - but then we’re a bit nerdy like that.

To sum up, the problem with spreadsheets is that they try to do three different jobs in a single ‘layer’, namely

• data entry
• data storage
• data presentation

Spreadsheets can be handy for the last of these, especially if you want to do some charting, and as a means of transferring bulk datasets (with a flat data structure.)

If you’ve got more than a small amount of data or where storing data in a grid would result in repeating values in multiple columns, for instance where you have customers and orders, orders and items (which are called 1-to-many relationships) then you should be using a database.

Data entry should always be done through a form, so the user is focussed on the record they are creating or editing and there is no possibility of altering values in neighbouring records. Ok, so you can create forms in Excel (we’ve done a bit of that) but if you are going to all that effort you may as well do a proper job with a bespoke App and a proper database behind it to store the data.

You don’t need to just take our word for this. Speak to others who do the kind of work we do. They should be telling you the same story. But if you have a spreadsheet from hell and you’d like a better way to manage the data in it, you could do worse than invite us to listen to your story and let us propose a better way of working. You too could have your own ‘thing of great beauty’.