Article 3: What are the Main Sources of Excel Errors?

Research estimates that more than 90% of all spreadsheets contain errors (KPMG).

Indeed 95% of all Excel users think their Spreadsheets are Error-Free. (Spreadsheet Software Ref. 1) 

Figure 1: Errors are common in Excel Spreadsheets and can be overlooked.

“It takes only one error to result in disastrous consequences: Financial, commercial, and reputational loss can be a heavy price to pay for unaudited spreadsheets. Management may believe there is little reason for concern because they have used the same spreadsheet software for many years. Spreadsheets however can easily be changed, may lack certain controls, and are vulnerable to human error.

Media and devoted pundits have published an abundance of the results of spreadsheet errors over the years, many of which have caused commercial, financial and reputational loss.”

These spreadsheet errors are characterised as “Spreadsheet Horror Stories” but are probably only the tip of the iceberg. There are many workbooks where the errors remain ‘hidden’. This is because, unless the risks prove to be a breach of regulations in financial services firms or are catastrophic, one wishes  to avoid  publicising their failings.

Figure 2: Spreadsheet Horror Stories – (Spreadsheet Software Ref. 2)

A recent example of a spreadsheet horror story (Horror Stories) -

·        In 2020, 16000 UK Covid-19 results were lost. Lives were put at risk because the contact – tracing process had been delayed.

"The badly thought-out use of Microsoft's Excel software was the reason nearly 16,000 coronavirus cases went unreported in England.”

Another spreadsheet horror story example (Financial Times) - 

Figure 3: Spreadsheet Horror Story Example

·        Enron Corp. – The 2001 implosion of energy giant Enron has been studied widely and led to regulatory changes. Yes, there were ethical problems at Enron, but they started with and were exacerbated by thousands of inaccurate spreadsheets with many mistakes in spreadsheet formulas that were not caught by auditors.

What is a common cause of Error in Excel Spreadsheets?

There are many causes of Excel errors. A simple inspection of many sites that discuss this issue indicates that a common cause is poor inter-workbook linking. Here we need to understand ‘dependent’ and ‘precedent’. A workbook that is used by a workbook of interest is a precedent. A workbook that uses a workbook of interest is called a dependent.

Whilst Excel has precedent and dependent tracing its capability is very limited.

A workbook linking error occurs when a workbook (here called workbook1) is dependent on another workbook (here called workbook2) and the location of the data in workbook 2 is changed. If workbook 1 refers to the data in workbook 2 using A1 notation, then an error is likely to occur.

This problem is made worse when the author of workbook 2 doesn’t even know that workbook 1 is dependent on it.

Indeed I once was told that a major Mining Company suffered a huge financial loss after an employee’s position was terminated. Consequently the workbooks created by the employee were either relocated or deleted. It turned out that now many other dependent workbooks could now no longer be used.

Solution to the problem of workbook linkage

In my course Professional Excel, I provide various scenarios of making changes to a workbook and participants are required to logically deduce or wildly guess what the effect will be.

One of the problems is what happens when I move data in a precedent workbook. There are of course ways to avoid the problem. Some of these include:

·        Avoid use of A1 notation.

·        Use Excel Names or Excel structured tables.

·        Where possible and appropriate use a relational database such as Microsoft Access.

·        Make sure that all data flow is described using a flowchart so that all colleagues know respective precedents and dependents.

Here we explore what I mean by a dataflow flowchart by considering an example of worksheet interconnectivity.

Even in the diagram here, we can quickly see that the workbook has a large number of 'connections' between worksheets, and a professional Excel developer would try and identify strategies to simplify dataflow.

Figure 4: Dataflow Diagram

Conclusion

When it comes to errors in spreadsheets, an Excel developer should think of 2 strategies:

·        How to avoid errors.

·        How to detect errors if they occur.

We are currently offering a no obligation service to analyse an example of an Excel workbook of your choosing to both identify and avoid errors. Send me a message if interested.

What do you think is a common source of error in Excel spreadsheets?

If you would like to share what you consider to be common sources of error in Excel workbooks you are more than welcome to add comments.

References:

KPMG - https://assets.kpmg/content/dam/kpmg/ie/pdf/2021/03/ie-6-mrm-solutions.pdf

Spreadsheet Software – Ref 1. https://spreadsheetsoftware.com/

Spreadsheet Software – Ref 2. https://spreadsheetsoftware.com/avoid-becoming-a-spreadsheet-horror-story/

Horror Stories - http://www.eusprig.org/horror-stories.htm

Financial Times - https://www.ft.com/content/18db20d8-7726-43e2-87f1-c5861ad3dff5

Professional Excel course: http://www.excelengineering.com.au/