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.
Multi-User Access (no pun intended)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 tape.
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.
(continued...) 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 data.
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.
Eschew ObfuscationData 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 man with two watches—he never really knows what time it is. A relational database stores data in one place and minimizes redundancy. Data can be updated in 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?