The Problem With Spreadsheets

Probably the most abused computer program ever written.

I was asked recently to help a business that had a problem with a spreadsheet. This manufacturer made a product that was very complex and the customer needed a lot of help deciding on the specifications of their purchase. So 10 years ago a very clever employee wrote a spreadsheet that would take the customer's requirements and produce a manufacturing specification for the product. All was well in the beginning; they created a new spreadsheet for each job and produced their products, until the employee who wrote the spreadsheet left the business. When changes were needed they tried to "patch" the spreadsheet but failed. The only person who knew how the spreadsheet worked was unavailable. Time went on and the spreadsheet became more and more useless. New materials could not be added, the pricing could not be updated etc. When I was referred to them it had been two years since the author had left the business and the issue had become very urgent.

The first thing I noticed was the spreadsheet was very complex. Unnecessarily so in my opinion. And because it was so complex it had errors that had not been corrected. There were a large number of these files and they were stored in many different places. It was a real mess.

The problem with spreadsheets is that they are often written by people who know just enough. Just enough to get the immediate job done but not much else. Spreadsheets are easy to use but difficult to master. They are ok when they are well designed, use a small amount of data and are to be managed by one person, but bad when large amounts of data is to be managed by many people. Data integrity is a problem because the data is often entered without being checked or verified. One typo can give you an erroneous result and the spreadsheet will not warn you. They and the errors they hold are easy to copy.

Spreadsheets are best used to model a problem, to test calculations or the simulation of a wide variety of variables, not as pseudo databases. If you use spreadsheets to store data in your business you are looking for trouble, there is a better way.