I am old enough to remember the introduction of the Lotus 123 spreadsheets. As a young accountant, I thought that this was a truly remarkable tool. And it was. Later I started to use the Microsoft Office products and I discovered the wonders of Microsoft Excel. I think Excel is a great piece of software that has placed in the hands of the ordinary person the power to analyse data (both numbers and words) in ways that would previously have required very complex programming skills.
But there are dangers. Due to the fact that spreadsheets are so accessible, they are frequently used by people who are not properly trained in their use. Also rigorous checking is missed because of the assumption that due to a spreadsheet doing the calculation, it must be right.
Here are some of the problems I find in practice.
A list of numbers does not add correctly.
This is by far the biggest error I encounter. How you might ask? There is more than one way, but the simplest is for a cell (or cells) to be left out of a formula that looks like it is adding all the cells in question. Often this occurs when rows are added to a list of numbers that are being summed. Below the list is a nice neat total with the words “total” (or something similar) but the added cells are not in the formula that is supposed to be totalling the list of numbers.
Back in the days of pencil and paper, if the addition of a list of numbers was important, it would be double checked, often by a second person. Frustrating as it may seem, this should also be done for spreadsheets. In Excel, there is a quick way to do this. Highlight all of the numbers that are being added and the total will appear at the bottom of the spreadsheet. You can, of course, check the formulas, but I find the highlighting method works best and is less prone to errors.
Complexity
Because spreadsheets are so powerful, they can quickly become very complex. Typically people using spreadsheets just start working and then they add a bit here and add a bit there and fairly soon there is a large unwieldy mass of calculations that is only understood (perhaps) by the person creating the spreadsheet.
In a good computer program, the code will be broken down into many sub-routines that perform only one function. This creates more code, but it increases the ability to understand the program markedly. Although this principle cannot be followed exactly with a spreadsheet, it is better to have the spreadsheet split into different functions that can be separately checked rather than the spreadsheet “logic” being stuffed into large, incomprehensible formulas. Complexity frequently produces errors.
It doesn’t make sense.
When dealing with younger accountants working for me, I have always encouraged them to sit back from their work to see if it makes sense. Is the answer within the realms of what you expected? This simple test is a good one to use with regard to spreadsheets. The person creating the spreadsheet can become deeply involved in the intricacies of the calculations but lose sight of the overall conclusion. Computers have not abolished the need for common sense.
People putting commas in cells
Here is something that can cause a lot of problems. It is an error that is made by people who have little experience with spreadsheets. These people, typically, are used to putting commas in numbers in programs such as Microsoft Word. They also do this in Excel, not realising that the formatting of the cell will take care of this. If the comma is inserted by the user, Excel then treats the string of characters as a word and not a number. As you can imagine, all sorts of problems can result from this.
Do you have spreadsheets that are used for critical calculations in your business? Perhaps you should get them checked.
Wishing you easier business.
John M. Jeffreys