St James Software - When to use spreadsheets and when to avoid them

j5LogBook
The pure Web Server version!! Make operator logs work for you! Industrial, proven, configurable. Oracle, SQL Server, Access web-server based. Click here for more information...

j5 HandoverBook
The j5 HandoverBook is an electronic tool designed to effectively manage the information flow between outgoing and incoming shifts. Click here for more information...

Other Products
j5OrderBook jBrowser jHistorian j5KnowledgeBook Click on the links above for details

When to use spreadsheets and when to avoid them

Spreadsheets:

When to use them and when to avoid them.

Spreadsheets Rule! ...Or do they?

Everyone will remember the first time they used a spreadsheet. For me it was Lotus 123. I was (like) a kid with a new toy. Magically, reports and summaries and calculations just seemed to roll out almost by themselves. Over the years, the spreadsheet has steadily evolved to the point where one almost asks; "Is there anything the spreadsheet can't do?" OK, but is there a down side to this fairy tale?

Well, one doesn't have to look far in process control systems today to see some awful scenarios with spreadsheets being the "blunt murder weapon!" Because they are so versatile, they have a habit of sneaking into areas where they really shouldn't be. They can become a system administrator's nightmare. Phone calls (that frequently come in the early hours of the morning) often start with "Someone has been playing with the Lab entry sheet and it don't work!" or "You know that number that comes into the machine-hours cell from the other sheet...well it's just showing asterisks!" or "I think the personnel target sheet has been messed with again!"

OK, so we are definitely not going to get rid of the spreadsheet but maybe we should just make sure it is used in the places it is designed for and it stays clear of the places where it causes agro and high blood pressures. Hence the title of this white paper: When to use spreadsheets and when to avoid them.

Simplifying the problem

Let's simplify the problem and come up with some clear guidelines.

In our experience, most general purpose information projects can be implemented with either a spreadsheet or a relational database like Oracle, SQL Server or Access. Functionally, both will do the job! In fact, this paper could have equally been titled: "When to use a spreadsheet and when to use a relational database"! To come to a conclusion therefore on when to use what, we should compare the basic characteristics of the two.

So, let's compare spreadsheets and relational databases.

Where does the spreadsheet Excel (excuse the pun) when compared with a relational database like Oracle or SQL Server?

....and where does it fall flat? (Again compared with the database.)

Ease of Use: You can't beat this. In fact, some would say that this is in fact the problem with spreadsheets! It is just too easy to knock together a new report without having to think about the big picture. The report evolves, sneaks into the production process and suddenly, it is a vital piece of the operation.

Flexibility: As I mentioned above, there is virtually nothing that a spreadsheet can't do. Add Visual Basic functions and watch this puppy fly!

Developer Skill Levels: To get going with Excel, you really don't need a lot of training, just dive into it! Again, maybe there is a hidden danger in this. It is just too easy for us to create a lot of apps that we really haven't thought about.

Calculations and Presentations: The latest versions of Excel have stunning presentation facilities and they also connect very nicely to Word or PowerPoint.

Security: The very nature of the spreadsheet where the data and calculations are stored together make it a sitting target for anyone to get in and make "improvements". With databases, like Oracle and SQL Server, there are steel walls built specifically to prevent tampering.

Maintainability: Spreadsheets, particularly complex ones (or ones that connect to other spreadsheets or when used by multiple users), can be a nightmare to maintain. Databases on the other hand have all the utilities to manage the issue of maintenance. Complexities, Multiple users and connectibility are all handled in a sane environment.

Capacity: Spreadsheets are just not built to handle tens of millions of records. Databases are.

Multiple Users: Spreadsheets can handle one or two users, but what about 30 or 40? No thanks, databases can handle this in their stride.

Networkability and Connectibility: Once we have our spreadsheet report, can we link it to other sites and get their users to look at it. No I'd rather not! Let's use a database for this. All this stuff is handled smoothly and transparently.

Data Entry: What about getting the operators to enter data and production results. Better not, spreadsheets just don't meet the challenge. Databases on the other hand are perfect for building a solid, manageable data entry scheme.

... and the Conclusion?

Let's put it in a single sentence:

Taking account of the strengths and weaknesses of spreadsheets, may we suggest that you use spreadsheets as:
your own personal Ad Hoc query and calculation tool or as a prototyping tool for the development of other applications, and
you use Databases (or off the shelf standard packages) for everything else.

If you adopt this strategy, you will find the best of both worlds: That you can quickly develop or prototype new ideas, present the information to the powers that be and then allow the database people to use your spreadsheet as a specification and to scale it up to a production version.

Very shortly, we intend introducing jWorkBook. This is an Excel Add In that bridges the gap between the spreadsheet and the database. Our opinion (a little biassed perhaps) is that this is a utility that has been sorely missed over the years. And by the way, it is absolutely free to our subscribers.

View the on-line Demo
Please send me more information

Click here to view our Price List.

[ Back ]

Live Chat