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’.

    Contact Us

    Please feel free to contact us for a free, no obligations discussion about your IT requirements. You can contact us by phone, email or via our contact form.

    contact us

    Latest News

    Illuminaries are now Cyber Essentials Certified

    We are pleased to announce that Illuminaries are Cyber Essentials Certified via IASME.

    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.

    read more

    "The system is looking great. You’ve done what the previous contractor couldn’t do"

    Sylvia Ward, Deputy Chief Executive, Age Concern Sheffield

    "Illuminaries provided creative and imaginative solutions to our data needs and have designed a database that is straightforward to use and has flexible search methods"

    Paula Walker, Sheffield and Rotherham Asbestos Group

    "You were provided with quite a complicated design for an e-commerce site, they created the 'shop' whilst keeping the design as we wanted. They also provided great value for money."

    Lucia Kempsey, Bettyjoy

    "I really have appreciated the time and trouble you’ve taken to help me set up, especially as I’m so unused to using computers!"

    Kathryn Yates, Children’s Food Company

    "We had been thinking of implementing an online system to manage course approvals for some time but options were limited due to security considerations. Illuminaries were able to deliver a solution that works within our IT structure and meets all of our requirements."

    Daniel Kaye, PDO SYEM

    "Illuminaries were fully accommodating to all my needs and have more than delivered on what I had hoped"

    Stephen Radley, Consultant Obstetrician, Sheffield Teaching Hospitals

    "As you are planning to get Tony to set your database up, you can be confident that the system will work. Tony also ran the Trent HCV database for a number of years, which generated over 25 peer-reviewed publications"

    Will Irving, Professor of Virology, University of Nottingham

    "I felt it was very productive today and you really have done an amazing job so far with this, it is beyond the expectations I had and to see it in today’s format was really positive"

    Vicky Jones, Quality Co-ordinator, Health Education England

    "Thanks so much for the quality database. It is a great piece of work. I am demonstrating it today at Willerby and am sure everyone will be impressed."

    Julie Platts, Quality Manager, Health Education Yorkshire and the Humber

    "[The QM application is] a really powerful development that allows timely review of concerns, streamlining engagement between the Trust and HEYH so helping in the accuracy of reporting and feedback the GMC"

    HEYH

    "You people are so helpful"

    John Sellars, ICT Consulting

    "The good work done by Illuminaries was highlighted by everyone (at the launch event). I personally would like to highlight the extra mile you went to get to this point. So a very special thanks to you both [Tony and Jim]"

    Mohamed Naeem, Child Protection Specialist, UNICEF Maldives Country Office

    "I would like to express my thanks to all of you as well as Illuminaires for their dedication and patience in delivering a user sensitive system that will help in transforming the work of the Government to plan and effectively deliver protection measures to the troubled communities and families in Maldives"

    Manssor Ali - UNICEF, New York

    "The very odd occasion I have required assistance, you have been very responsive in helping me resolve the problem"

    Joe Wright, Momentum Transport

    "Illuminaries provided a systematic and logical approach through the workshops to help clarify the data requirements and functionality of the system that would meet our business needs. This approach saved time and ensured both parties were clear about what was required"

    Rosemary Clark, Objective 1

    "I have just input the first patient onto the [Cirrhosis] database. Nice and easy, straight forward and only took 10 mins if that."

    Andrea Bennett, Deputy Nurse Manager / STOP HCV Project Lead

    "So far it’s been remarkably painless going live, so I just want to say thank you for your fantastic work. We’ve had some really positive comments, and I don’t think there have been any negative ones."

    Katharine Wilkie, Finance Manager, SYFAB

    "The flexibility demonstrated by Illuminaries, their readiness to consider and their ability to implement appropriate, alternative technologies into the laboratory’s business IT systems have proved to be of considerable help during a time of hectic and often profound business change."

    Business Systems Manager, Syngenta