There’s no question that Microsoft Excel is a remarkably powerful and popular piece of business software. Since it was first created in the mid-80s, Excel has grown to become the world’s most widely-used spreadsheet software package. However, as its use has become practically ubiquitous across business, government and academia, so the potential problems associated with its incorrect use have increased.
Earlier this month, for example, the research of two respected Harvard economics professors was called into question after a student spotted basic errors on the Excel spreadsheet that held their original research data. Unfortunately, the same flawed findings had been used by several politicians around the world as a justification for austerity policies, showing just how serious the ramifications of a simple spreadsheet slip-up can be.
With many organisations still heavily dependent on spreadsheets to manage business-critical tasks and processes (e.g. colleges and universities using Excel for curriculum planning), here’s my take on the most common ways that the incorrect use of Excel can create havoc inside your organisation…
1. The human touch
Whether you are an Excel expert or a total novice, it’s all too easy to make a small, unnoticed human error that throws a spreadsheet seriously out of kilter. The Harvard professors mentioned above, for example, highlighted only 15 of out of 20 pieces of data in a crucial calculation, leading to serious question marks over the validity of their key findings.
Obviously, the more individuals you have accessing and contributing to a spreadsheet, the greater the chance of someone making an error that it will take time and effort to indentify and unpick. A user accidentally over-writing a formula, for example, is a common occurrence.
Tip: If a spreadsheet is being used for a business-critical task – and involves multiple contributors inputting data – it makes sense to have a skilled Excel practitioner put the original together. He or she can then create various data validations (cross calculations) that apply testing formulae to the spreadsheet and pinpoint where problems might originate.
2. Where is the master?
It’s sometimes easy to forget that the spreadsheet was popular long before the internet became part of most people’s everyday working lives. As remarkable and versatile a tool as Excel is, it is often used by organisations in ways that it was never originally intended, which can lead to serious security and operational problems.
Let’s consider the example of colleges using excel for curriculum planning as an example. A spreadsheet is created centrally then distributed via email to a wide range of department heads and lecturers for them to input their course data. This could, of course, lead to multiple copies of the spreadsheet existing and with different people supplying their data at different times it becomes increasingly difficult to keep track of the master document. Ensuring that any changes or updates to the spreadsheet are consistently applied across the various versions of the document is also a problem.
Likewise, the security risks associated with the distribution of spreadsheets via email have increased significantly in recent years. Many spreadsheets contain information that is highly sensitive or protected under data protection legislation, meaning a hacked email account or a lost smartphone or laptop could have serious consequences for your organisation.
Tip: For data gathering tasks involving a number of individuals, don’t issue spreadsheets. Instead, create tailored online forms for the various users to complete, which automatically feed their data into the one, centrally-managed master spreadsheet.
3. What does it all mean?
Finally, even in the event of producing a perfectly constructed, factually accurate spreadsheet, it can be very difficult to print a worksheet or workbook into some kind of coherent report. Anyone who has ever tried to print out an Excel spreadsheet will know that what appears on the A4 page often bears little relation to what you’re viewing onscreen, with related tables being split across different pages, making any kind of analysis difficult to say the least.
Tip: Use a reporting warehouse tool to create and issue tailored reports, which take only the data that is relevant to each particular audience from the spreadsheet and allow the results to be printed in an accessible, easily understood format.