18a Plas St. Pol de Leon

Penarth Marina

Penarth

Cardiff, UK

CF64 1TR

Tel: 029 2071 2664

Spreadsheet vs Database - A White Paper

Management decisions - giving your employees the right tools for the job.

Would you deliver ten tons of bricks in a wheel barrow?

 

Click here to download our white paper in PDF format.

Like many business people you or your colleagues probably keep data in a spreadsheet, and initially it works just fine, problem solved. However after a short while the number of spreadsheets you work with increases and you spend a large proportion of time tweaking them to get them to pass data between your growing library of spreadsheets.

Your colleagues can not access the data from your spreadsheet when you are working on it. Additionally when they do make data changes, they may not be what you are expecting or you cannot quickly find the answers to what you are looking for.

Worse still, as a few clone copies of your spreadsheet have now been created (to overcome the single user issue), you no longer have the confidence that the version you are now entering data into is the latest!!!

This is a typical example of spreadsheet practice in all types of organizations and businesses today. Many people simply do not appreciate that they are skating on thin ice when they use spreadsheets to store important business data. Permanent data needs to be stored in a database. Spreadsheets are great for financial modeling, that is what they were designed for, they can store limited volumes of data but that is not what they are designed to do.

Most business people use spreadsheets too often and too much as a crutch to avoid learning the more complicated but sophisticated features of databases. This can result in:-

 

• Many hours of expensive management time consumed unnecessarily.
• Poor management decisions based on inaccurate or incomplete data.
• A high risk of losing critical business data.

As a business owner or corporate head you should be aware that employees like creating spreadsheets. They enjoy the challenge and generally, because they do not know better, they think they are working very efficiently. Many find creating spreadsheets enjoyable.

Spreadsheet maintenance can become addictive with far more time being spent on data maintenance and much less time being spent on primary tasks. Very often these are intelligent and therefore highly paid staff.

Do you know how many hours, you’re best paid staff are spending on spreadsheets each month?

In addition the life of a spreadsheet is one employee. The employee who uses the spreadsheet is likely to also be the one that designed it and now maintains it. Should that person get employment elsewhere the spreadsheet often becomes of little value to the business. The replacement employee makes their own spreadsheet.

A database is the correct medium to store share and maintain your data. Here are some of the reasons why you should not be using a spreadsheet:-

Only 1 person can edit the same data file at one time. Spreadsheet users have to wait until nobody is using a file before it's free for them to use. This is often impossible when you have several people all wanting to prepare for a 14:00 management meeting. With a database, data is easy to share. You can have two or more people editing a database at the same time. 

Updates to the data can be lost unless the file is saved and the file saving process itself puts data at risk. The more times you save your file the higher the risk of file corruption. Databases save information on a record by record basis this method is far more efficient and secure. 

A spreadsheet file is overwritten each time you save your work. If a power cut or hardware error etc should interrupt the saving process you will almost certainly corrupt the file, losing all data. If you have backed up your file you will have to restore it, but that file will only contain the data that was in it when the backup was made. Modern Industry Standard databases such as Microsoft SQL Server are designed to minimise this risk. 

Unlike databases spreadsheets do not support relational data. Therefore if you have a list of contacts for each customer, and one large customer has 20 different contacts at the same address, you will need to retype that address for every contact record. Likewise if the address changes you will have to correct it in 20 records. With a database you link 1 address to each of the 20 records in a data-relationship. Databases are relational, allowing you to link related tables to minimise duplication. 

Data validation is extremely limited and can result in the quality of the data becoming very poor as it is built up. Any type of data (Date/Number/Text) can be entered into any cell. Databases do not have this problem. Each element of data is given specific type and validation rules. Logical data validation can also be applied, such as ensuring the VAT amount is zero when entering a transaction for postage. 

There is no way to guarantee that the data contained in a single row stays together. Look at this sorting nightmare, Mr Barry with account number BA002 owes you £4000 and wants to close his account and provide you with a settlement cheque. Your spreadsheet should look like this:-

However, yesterday someone in your office incorrectly sorted the balance column to find out the largest balance. This mistake is very easily done. Furthermore there is no way of knowing who or when this was done or whether the data is correct or not.

Therefore the spreadsheet now looks like this:- 

As a result you ask Mr Barry to write a cheque for £676 instead of £4,000 and potentially lose £3,324.

As you can see, because the logical link between data in the same row can be broken, there is no way to guarantee the ongoing accuracy of the data. Databases by design will ensure fields in logical records stay together.

Finding the information you need is very slow with high volumes of records especially when there are more than a few dozen columns or rows. What makes spreadsheets so simple to use is the fact that the programming & formatting is on one page with the data. However this also means that they are hard to maintain. Databases do not have this problem as they are designed to maintain, sort and filter huge volumes of data. 

There are no time saving, data entry support routines. Databases can fill in data intelligently for you based on a predesigned set of business rules. You can adopt what is in an experienced employees mind and make the database behave that way. 

With data views limited to rows and columns seeing what you need at any one time can be limited and therefore be a breeding ground for mistakes. Databases use data entry screens and reports designed to efficiently perform the task in hand. 

Data in spreadsheets is not secure; it can be easily saved onto a floppy disc or USB devise and stolen without your knowledge. With a database you can protect private information from curious eyes. You can also protect users from their own mistakes, such as adding up the wrong column or forgetting to include VAT in a quotation. 

Data duplication wastes time and leads to errors. Spreadsheets have a habit of creating clone files, you have probably seen this yourself. Spreadsheets do not share easily and cloning is the result. When the data is updated the only way you can recirculate it is to email your colleagues more cloned copies. Knowing what data is in which version is another matter and can result in dangerous mistakes. 

Spreadsheet users create clone copies of source data. Most spreadsheets duplicate information already housed in a database, such as an accounting system. Modern databases communicate with account systems ensuring data is entered once only then redeployed within your organization, whenever it is required. 

Spreadsheets have limited reporting tools. With a database, you can format the same data many ways in different reports. For example with monthly sales reports, you want to look at sales by:-

• Sales Person
• Regional Office
• Product Type


In a spreadsheet, you have to enter or copy the sales data three times in three spreadsheets to get the desired information. In a database, you enter it once and then use the reporting features to compile the data in the three formats with a click of the button. 

Databases are future proof. They have the capacity of holding billions of
records, using industry standard database platforms such as Microsoft SQL Server. This will ensure you have the capacity to handle the high volumes of records that your business may throw at it. This will also reduce the risk of data capacity restraints, hitting your business at an inconvenient time.

Businesses use spreadsheets as they initially appear quick and easy to use, most look no further, others find the prospect of creating databases too difficult or daunting.

Here are 7 fundimental reasons why a database is a better tool for storing data:-

1. Easier to share
2. Faster & more reliable data entry
3. More efficient
4. Less duplication
5. Better capacity
6. Enhanced security
7. Easier reporting

We have been creating cost effective database solutions for almost 20 years. To find out more click Enquire:- 

Spreadsheet vs Database - Management decisions White Paper Download

Click here to download our white paper in PDF format.