I'll never forget the first time I watched a true power user work the tables and formulas of an Excel spreadsheet. I sat in awe of the complex calculations, the production of charts and graphs, and the speed and ease with which he manipulated the tools. I had no idea Excel could do all those things.
I also had no idea how to do any of them. And neither do many small-business owners. For many businesses, Excel has served as a sturdy, capable keeper of data--a giant calculator, basically. Most of the functionality of the tool goes unused.
Still, these businesses often outgrow Excel, even before they exploit all of its potential. That's because Excel is a spreadsheet. It's designed to simulate a paper worksheet, execute formulas, and store numerical information. But many businesses, at some point or another, need something more than a spreadsheet. They need a database.
The differences between a spreadsheet and a database are subtle, but important. A database stores far more than numbers. It can hold text, codes, even images. And a database follows certain logical patterns in organizing that information. It has rules. And because of these rules, it's able to find relationships between data, update information automatically across multiple departments, and allow queries to be made against that data. And, quite often, a database is able to do all of this in real time.
In short, it's a more sophisticated tool for a more sophisticated business. But it's often difficult for businesses to know when it's time to upgrade from spreadsheets to databases. Here are the five ways you know for sure that your business is ready to invest in a database system:
- Unmanageable spreadsheets. If tracking down a simple figure from your business requires scrolling across hundreds of columns of a spreadsheet, you're probably ready to move to a database. Though they can handle reams of data, spreadsheets quickly become unwieldy when they store too much information. Databases can find buried information more quickly and elegantly.
- Version-control issues. Passing around spreadsheets to five different employees is one sure way to introduce confusion and inaccuracy. One of the key advantages of databases over spreadsheets is that they can be shared by thousands of users over a single network. This allows data to be updated and available throughout a company instantaneously. This creates a so-called "single version of the truth."
- Dirty data. Because spreadsheets allow you to type whatever you like into any cell, the data can become inaccurate and, therefore, useless, leaving you with "dirty" results. As the saying goes: Garbage in, garbage out. The rules of databases restrict the type of data that can be entered into any given field (e.g., U.S. currency, acceptable activity codes, or valid e-mail addresses), resulting in cleaner, more reliable outputs.
- Data redundancy. If you find that your business spends a significant amount of time entering the same data into multiple spreadsheets, you're ready for a database. Merging spreadsheets is no way to manage consistent data.
- Customer contact. One of the most useful aspects of databases is their ability to capture information directly from customers, whether that's through a website or a custom software application. You would never ask customers to fill in the cells of a spreadsheet, but you would ask them to enter their information into an online form. That requires a database.
If you're worried about saying goodbye to your trusted spreadsheet, don't be. Most databases allow for data to be easily imported and exported between spreadsheets. So if you're comfortable working in the spreadsheet format, you won't have to abandon it. But if you're running into any of the five tell-tale signs above, it's time to consider making the move.
Dan Briody is the author of two books and is the former Executive Editor of CIO Insight Magazine, a leading publication for information technology managers. He is also a frequent contributor on technology topics for Wired, Inc. and Business Week magazines.