Why do so many of us use spreadsheets for databases? A spreadsheet has serious drawbacks when used for data storage, is cumbersome to retrieve data from any but the most simplistic queries, offers little or no data validation and little or no protection against data corruption from well-meaning but poorly trained users.
Why do we stick with Excel? In a word, familiarity.
Excel is better at a lot of things—displaying charts, showing PivotTables, displaying different types of data on the same worksheet. It’s pretty simple in the current versions of Office to pull the data you need from any database into a spreadsheet, though. Store data in a database, work on it in a worksheet. You may hold out in Excel for a while, but several things may be conspiring against you, plotting your exile from Spreadsheetland, forcing you to create your first database.
The number one reason to create a database is that multiple people need access to a file. Of course, it doesn’t have to be simultaneous access. Five people just need access to the file some time during the week before the Thursday 2:00 pm meeting. Surely they won’t all try to make their changes between 1:45 and 1:59, right? Surely one of them won’t go off to the meeting with Excel still open on their desktop, right? Yeah, right.
You’ve no doubt experienced the “Someone else is using this file, go away” error message above. The typical response is to open the file read only and then create an independent copy using ‘Save As’ another file name—invalidating the reason for ‘sharing’ the file in the first place. Databases allow multiple user access without issues.
Keep Your Data Safe!
Other reasons to create a database are data integrity and data validation. The worst nightmares ever seen in Excel have been caused by the Sort function. Someone sorted on last name, but not any of the other columns. Someone sorted on account code, but not on amount.
It’s a Rubik’s cube of data and there is no way to correct the spreadsheet. Just delete it and look for the latest backup.
False dates like April 31st or February 30 can also cause havoc in a spreadsheet but will be validated and disallowed as a matter of course in a database. The product number 0042 which gets changed by Excel to 42 or the part number 3/14 which gets changed to March 14 are other examples of data corruption which would not take place in a database.
Many public companies are being forced to move key reporting data out of spreadsheets by the Sarbanes-Oxley Act, Section 404. Since the control over spreadsheets is so limited, they simply cannot keep the data in Excel and comply. Access, in this case, will not be an option either. I Get It! Development can also design a database for SQL Server which does meet security and control regulations and port the spreadsheet data. A qualified auditor can inform you of what level of security and control is needed for what types of
Read our success story detailing how we helped a world-leading application infrastructure company move hundreds of Excel spreadsheets into a database which still allowed Excel spreadsheet output for management and accounting review.
Data duplication is another reason for moving data out of a spreadsheet. If the rows of your Excel spreadsheet contain redundant data like a customer name and address on each row of an order, you’re wasting a lot of space. That’s not the major issue since disk space is now so cheap—the real problem is that redundant information eventually gets out of sync. The customer’s address is different on 3 of 100 lines… Is that the new address or is it the old one? There’s a saying about a person with two watches—they never really know what time it is. A relational database stores data in only one place and minimizes redundancy. Data can be updated in only one place and only takes up the space it needs.
Do You Need A Database?
Take a look at the spreadsheets you use for storing data and ask yourself if the data wouldn’t be better stored in a database.
- Is the data for long-term storage (as opposed to a work-in-progress)?
- Do multiple people need access to this data?
- Do I need to safeguard against erroneous entries?
- Does the data need to be protected against inadvertent corruption?
- Is a large part of the data redundant?
If the answer to any of these questions is yes, contact us directly and we’ll be glad to help you migrate your spreadsheet data into a relational database. You’ll be glad you did.