When Should You Say Goodbye To Spreadsheets?

Written by Ian MacOlive.

Many companies use spreadsheets to perform certain data and number crunching tasks.  Spreadsheets work extremely well, up to a point. 

That point, the tipping point, occurs when the spreadsheet becomes too clumsy and onerous to manage and starts taking too long to process the information. 

It is important to remember that the spreadsheet was originally developed for data modelling.  It became the tool to use for cash flow forecasts, manufacturing analyses and scientific modelling.  For this purpose, the spreadsheet was brilliant; small projects, with lots and lots of formulae, performing what-if calculations.

Over time, spreadsheets became more and more powerful.  Multiple worksheets, which could be linked in a three-dimensional model, added to the additional power of complex functions.  People started using spreadsheets more and more and the die was cast; spreadsheets can do anything!  They started plugging in more data, writing complex macros to perform repetitive tasks and everything was just great.  Or was it?

Try finding the formula that was accidentally overwritten by the data clerk in a multiple worksheet spreadsheet.  Remember the one you forgot to protect?  Three hours later and you're still looking...

Or, sitting and waiting for the spreadsheet to gather and calculate the data, so that you print the reports, can be rather frustrating.

I have heard some experts say that if a spreadsheet reaches more than 500 rows or 100 columns in size, rather look to a database management system.  Maybe that is a little extreme, but it does make some sense. 

A good relational database management system (DBMS) can store more information than you will probably need and saves the records to disk right away, ensuring data integrity.  And it's fast.  Really fast.

With input screens and reports that can be customised, powerful querying functionality and multi-user environments, the database management system is certainly the way to go.  Initial programming costs may be high, but in the long run, it is well worth it.