Written by Ian MacOlive.
The spreadsheet is a powerful tool for performing complex calculations and data storage for small projects.
When VisiCalc hit the scene back in the eighties (in the last century), the relatively small group of Apple II users went crazy. It was like manna from heaven for the poor accountants, scientists and engineers who were used to filling in enormous analysis sheets with a pencil. Now, they could use VisiCalc to do the same task, AND calculate the result automatically.
Over time, the spreadsheet grew into what we see today, in the form of Microsoft Excel and a number of similar products on the market. The power of the spreadsheet has grown almost exponentially, allowing the user to create customised formulae, charts, pivots and so on. Worksheets can be linked and updated automatically.
For pure data analysis, the spreadsheet is, indeed, king. To a point…
Despite all of these extremely powerful features, the spreadsheet has limitations, which tend to manifest when you least expect it. Often, Murphy’s Law applies and the spreadsheet falls over when you are right in the middle of a major undertaking, like month-end or meeting a tight deadline.
Spreadsheets have significant limitations. Here are a few:
Size limitation
When a spreadsheet is first constructed and tested, the size tends to be small. However, as the spreadsheet grows with more and more data, it start slowing down, until it reaches that point where your feet go to sleep while you waiting for the re-calculation to finish.
Protection of formulae
How often have you forgotten to protect the cell contains the formula that took you hours to test and perfect. It takes one instance of honest human error, and that formula is gone.
One user at a time
In the instance where there are a number of people required to work on a spreadsheet, they can only access it when no-one else is working on it.
Inconsistent data
This becomes most apparent when trying to find a particular record of cell, based on the contents. If a space is incorrectly added in the wrong place, the search or filter fails.
Not dynamic
Data ranges become redundant when new records are added, resulting in established formulae needing to be altered. Overlooking these errors can be embarrassing and downright expensive, especially if important decisions are being made based on the data.
When your spreadsheet start to hit its limits, what options are open to you?
Perhaps the best option is to consider the conversion of your spreadsheet to a database solution. Relational databases became viable not too long after the spreadsheet was well-entrenched as the affordable and quick means of gathering data and performing calculations.
Early databases available for the PC market were simple two dimensional solutions and were essentially designed as a record-keeping system. However, when computing power increased, the advent of the relational database became an affordable option. Applying the rules proposed by Edgar Codd, the Database Management System (DBMS) became a reality, with products such as dBase being widely implemented in the desktop market.
Today, there are a large number of options to choose from.
The downside to upsizing your spreadsheet to a database system is that databases require fairly in-depth knowledge, especially with regard to extracting data in queries, form construction and reporting.
Rather than having to go through the pain and suffering of learning a new technology and having to experience trial and error development, the best route would be to acquire the services of a developer, who can identify your needs and create a customised solution.
Obviously, the cost of such a project would depend on the size and complexity of the spreadsheet and your requirements. However, you might be surprised at how cost-effective this project would be.
In Part 2, we will look at the features of a database management system and the power it can deliver. Click here to read Part 2.