Awgar Stone - Moving from Excel Spreadsheets to a Database

Notice of New Website
Click here to go to the new version of the website.

Moving from Excel Spreadsheets to a Database

A database application can help you handle large amounts of related data more efficiently than a spreadsheet.

Too much data or overcomplicated spreadsheets can become very slow and tedious to use, wasting time and forcing mistakes. When this happens, it is probably time to use a more suitable tool such as a database application. Spreadsheets are great for smaller amounts of data, calculations or data whose structure changes but for managing large amounts of inter-related data, database application are much better, they are designed for the job, specifically being more robust, faster to load and use, and enabling multiple staff to access the same data and make changes (spreadsheets do not).

What is a database application? It consists of a database - a central repository of data, and software - where staff enter and view data, generate reports etc. An example of a database application is Sage. There are many, such as Sage, which are off-the-shelf products, the alternative being a bespoke (or custom) system. the choice between off-the-shelf and custom IT systems is complex and depends on the following, among other consideration:

The availability of such a system for the particular business information you are dealing with. For example is there a software application for shoe manufacturing?
How easy the system is to use (how many unused features, how well it fits with the way you work).
How well the system integrates with existing IT systems (e.g. you don't want to be cutting and pasting customer information between software).
How much the systems cost, including initial cost, whether this is payable in one lump or not, yearly support and maintenance fees if they exist and likely updates.

A good example of a system which is almost always worth buying off-the-shelf is an accounting system. It is very complex, has to include a range of standard features and there are advantages to using the same one other people do, e.g. the accountant and the government. A system which must usually be built to order is that which deals with a company's product or service as firstly there may well not be an off-the-shelf system for this, and secondly even if there is a bespoke system is necessary to gain a competitive advantage. For the remainder of this article I will deal solely with bespoke systems.

So a database system (or bespoke system or custom system) consists of a database and a custom software programme (the part users see). You may expect Microsoft Access to be the natural choice of database for a small busi. The problem is with Microsoft Access is that it is a desktop database - it is not designed for multiple users to access it. It is possible, but it is limited in this regard (as well as being limited in other ways). Moving straight on to a database such as Microsoft SQL Server, Firebird or PostgreSQL will mean you have much more power and flexibility and not be locked in as much to a single technology or vendor (these bigger database systems separate the software and database, Access does not). Firebird and PostgreSQL are also free as well as being much more powerful, so why bother with Access? Most software developers and industry professionals take this view.

There is investment needed to get a database application written and this will probably only be worth it if the Excel spreadsheet is (a) mature (doesn't change design all the time) and (b) is used often. This way it is likely that the return on investment will be good and you will have a useful tool. So how much does it cost and how quickly does it pay for itself and start saving money? Well here's and example. It's for a small company, with just 5 people using the spreadsheet and a 3000gbp spreadsheet-replacement database system. It pays the cost back in 6 months and then saves 500gbp per month for the company.

ROI Example, Small Business

cost of database application = 3000gbp

30 minutes saved per staff member per day
5 staff
10gbp/hr staff wages
= 25gbp/day saved (1/2 hr saved per staff member = 5gbp, and 5 staff)
= 500gbp/mth saved (25gbp x 20working days/mth)

= 6 months to repay cost, then 500gbp/mth saved ongoing.

With more staff the ROI would be even better. It's also worth remembering that it is simply not possible to have more than one person work on a spreadsheet, so that in itself would be a reason, regardless of ROI to move from spreadsheet to database application.

Alternatives To A Database Application

An alternative to replacing spreadsheets is to enhance them. This may be worth trying before moving to a full DB system, but don't spend too much time or money on this. Simple things like not duplicating data, protecting sensitive areas from being altered and colour coding and adding notes in text boxes all help. Not many people realise that Microsoft office contains a powerful programming language called VBA which allows you to create data entry screens, data processing features and link with other programmes such as Word and Outlook. You can get started and tinker by recording macros* and looking at and changing the code produced, but a professional programmer is likely to give a better result and be less expensive considering your time and learning curve.

* Macros are recordings of things you do within Excel. You can replay them to automate repetitive tasks for example.

Paul Rigby, 16 September 2009. Paul is a developer and director at Awgar Stone Ltd and has been designing and creating database applications for over 10 years.