Excel, asking for a friend

Introduction

I’ve been working with spreadsheets for quite a few years now. In that time, I’ve been asked to advise on a wide range of spreadsheet requirements and to help sort out lots of spreadsheet problems. Most of those problems were not, as far as I could tell, about to create catastrophic, career-ending errors, but instead presented severe performance or productivity issues.

Prevalent as these performance or productivity issues seem to be, they tend not to hit the global news agenda very often – in contrast to the ever-popular catastrophic spreadsheet error news headlines. In fact, a few years ago, I was at an Excel conference and a journalist was chatting to delegates about their spreadsheet experiences. When I spoke to them, they were keen to hear about any significant errors I had come across. I suggested that, as important as spreadsheet errors undoubtedly are, my experience was that insidious inefficiency was a much bigger issue and that I could provide many examples. Politely declining the offer, they moved on to try and discover a juicy error story instead.

The European Spreadsheet Risk Interest Group (EuSpRIG) has an extensive list of Excel issues that have risen to global prominence: EuSpRIG horror stories, and I always recommend that people have a look through the list of stories to see exactly what can go wrong, and the devastating impacts that can result. The Excel Community has also covered many such spreadsheet issues in our article archive.

Beyond horror

I have often wondered why such stories don’t create a more immediate and panic-stricken response amongst the average audience of spreadsheet users. As someone who ekes out a modest living through selling Excel consultancy and training services, I have always been a bit disappointed that the revelations in the EuSpRIG list don’t provoke people to immediately book spreadsheet training courses for their entire workforce.

My assumption is that most people don’t have personal experience of losing billions of pounds or dollars through an error in an Excel formula and so they see the globally famous horror stories as something that only happens to other people. On the other hand, although not everyone has daily experience of catastrophic spreadsheet errors, lots of people frequently encounter examples of how bad other people’s spreadsheets are. They also see other people wasting vast amounts of time through inadequate training and the lack of key spreadsheet skills.

Projects that waste weeks

Spreadsheet inefficiency takes many forms. There are spreadsheet projects that take months of time and effort when using a more suitable application could have achieved a better result much more quickly. I was once asked by a medium-sized firm of accountants to help with the production of month end reports on their work-in-progress. Their existing system involved copying data from their dedicated accounting system, then pasting it into a spreadsheet. Several days of partner time were then involved each month in manually transforming and manipulating the data to produce the required graphical reports. This was in the days before Power Query and Power BI revolutionised data handling and reporting in Excel, so we used the Microsoft desktop database, Access, to link directly to the required data and generate the reports required, more or less with the click of a single button. Redesigning the system only took about the same amount of time as the partner was spending every month. Consequently, the Access solution resulted in many weeks’ worth of savings of, very expensive, partner time over a year. These days Power Query or Power BI could achieve an even better result.

On another occasion, an employee of another client started calling me to ask questions about ever more complex uses of Visual Basic for Applications code in Excel. I did try and suggest that they considered whether Excel was the right tool for whatever it was they were trying to achieve, but they persevered. It was only when they left the client organisation that the true horror of what they had done became apparent. They had built an incredibly complex client investment management system using VBA in Excel. It was a remarkable achievement but would have taken them months to do what they could have done in days using a more appropriate package. This might not even have been the worst consequence. Once they had left, there was nobody in the organisation that could understand or manage the system and, even worse, they couldn’t be sure that the results generated were correct. After persevering for a while, they abandoned all the work that had been done and replaced it with a system based on using a database.

Do sweat the small stuff

The effect of the previous two examples was obvious, but big project errors might only account for a fraction of the overall time wasted by spreadsheet inefficiency. It only takes a modest amount of spreadsheet experience for someone to be able to lean over a colleague’s shoulder and hit them with half a dozen ‘did you know there’s a much better way of doing that?’ hammer blows. Even if a keyboard shortcut or a simple Excel technique only saves a few seconds, if it saves those few seconds many times a day, most working days, that can add up to substantial time savings.

Then there’s all the simple methods in Excel to automate manual processes. To cover just a few: the use of the appropriate functions to perform calculations and text manipulations; using Tables to make formula references automatic and to copy formulas and formatting to new rows; applying conditional formatting to apply formatting and graphics automatically and using Data Validation to allow users to select from a list rather than type text in in full.

I have no empirical evidence for this, but I’ve often thought that 3 hours of targeted Excel training could save the majority of users at least 50% of the time that they currently spend using spreadsheets.

Your community needs you

Mirroring the EuSpRIG horror stories, it would be really useful to have a set of independent, practical examples that people have come across of people (other people, obviously) wasting time when using spreadsheets, whether that be a massive project that could have been accomplished much more quickly, or just a repetitive manual process that could be speeded up or completely automated. So, if you are happy to share real-world experiences of Excel inefficiency that you’ve come across, and the Excel features that you have been able to use to dramatically improve productivity for yourself and others, it could help us to address specific productivity issues through future community articles, webinars and training courses.

We’ve added a LinkedIn post where you can add your comments. We eagerly await your contributions to our Excel productivity project. As an added incentive, our three favourite comments will each receive free access to our on-demand Excel course series, packed with time saving hints and tips.

Additional resources

You can explore all aspects of Excel, including a series of articles on productivity and automation, in the ICAEW archive:

• Excel archive