How fixing one spreadsheet changed my career
I improved a spreadsheet, and ended up developing a reporting system spanning over 20 hospitals and 100 GP practices, and supporting improvement work that reduced inpatient falls by over 25%.
I was asked if I could help make some spreadsheets easier to use and maintain. Little did I know that doing so would shape several years of my career.
We had to use Excel so that clinical staff could record data relating to their Quality Improvement efforts. Excel was accessible, and seemingly ideal for the purpose of storing data and producing charts that could be printed off and displayed so the teams could track their progress. (I didn't know it at the time, but these charts were not just regular line charts, but run charts, which became quite an obsession for me).
I started off building one new spreadsheet. The original version allowed staff to record data across columns. This made it easier to create charts, but made it very difficult to maintain. So I flipped it around, so that data was recorded in rows, with some summary formula in a hidden sheet creating the totals for the chart.
I used my new VBA skills to develop a user-form. Some staff had difficulty with entering data in Excel, so the form popped up with prepopulated drop down lists. This was when I discovered the importance of setting the correct tab order for each control on the form! We did not have the calendar control available, so I had to split the dates up into separate text boxes, for day, month and year, and then use VBA to combine and correctly store them as dates. It was a bit of a minor miracle considering how widespread these were used, that we had hardly any incorrectly formulated dates entered across any of the tools.
Along the way I added a button so that charts could be annotated to help describe the reasons for peaks or declines, and to record the quality improvement journey. Some VBA powered a pop-up scroll bar that showed the comments as the charts progressed from left to right.
It started with one new spreadsheet for one ward in one hospital. This became many new templates, for lots of wards across 4 hospitals, covering work in theatres, critical care, and general hospital ward areas. Over time, whole new programmes developed in Paediatrics, Primary Care and Mental Health. Each time, new tools were required. Alongside this, I had to create a means to collate the data from individual tools so they could be reported at hospital and organisation level. This is where I was pointed towards learning SQL, particularly SQL Server, SQL Server Integration Services, and SQL Server Reporting Services. I will never forget figuring out how to create my first looped data integration and seeing all the wards data being pulled into my reporting database.
Over the years I developed Excel dashboards, began learning R alongside SQL, learned about and introduced horizon plots, and developed many versions of reports in SSRS and QlikView. Automating the analysis of run charts in Qlik was a huge technical achievement. That particular dashboard was the most effective dashboard I ever built. Questions were asked and answered, and decisions made, based on what that dashboard showed us, while all the subject matter experts were in the room. We identified our pilot areas for improvement work, we saw patterns in days and times when patient falls were occurring that had not been visible before. The dashboard wasn't the reason, but it informed the work that led to a reduction in patient falls in hospital of over 25 percent.