Spreadsheets vs Databases, Which Should I Use?

Most people wouldn't cut down a tree with a hammer, but many companies continue to collect and manage their data with tools that are ill-suited to the job. You might think, “well I know how to use a hammer and I don’t have a saw, so I might as well try the hammer."  How far do you think you’ll get?  Is the result going to be what you really want?  The reality is, whether you are chopping down a tree or managing your data, choosing the best tool for the job is the first step toward getting you where you need to go.

There are powerful, standardized data management tools on the market, some are at literally at everyone’s fingertips these days (Excel and Access come with the MS Office suite).  How do you know when to use a spreadsheet like Excel or a database?  How do you decide what sort of database you need?  Should you use a single-user database like Access or a multi-user one like SQL Server?  How do they all work together? Here are some tips to use to make sure you find the right tool for job!

Spreadsheets

Spreadsheets are excellent tools for combining and analyzing data.  Excel, probably the most popular spreadsheet program, has extensive capabilities for visualizing data (charts), analyzing data (statistical analysis), and summarizing data (pivot tables).  Its powerful and relatively easy-to-use formulas make complex calculations and logic possible, allowing you to link data from many sources to find the answers you need.

However, spreadsheets are not such a good choice for collecting and storing data.  Without robust access and editing control, it is easy for any person who is editing a spreadsheet to accidentally delete or overwrite existing data.  Even more problematic, if there are formulas in a spreadsheet, changing its layout can break them and result in incorrect calculations that are difficult to detect and fix.  Finally, the limitation of the flat sheet structure in spreadsheets makes it cumbersome to collect and store data that contains complex relationships.

Do: Use spreadsheets to combine data from multiple sources, analyze your data for a deeper understanding, and visualize and summarize your insights to communicate them to others.

Don't: Use spreadsheets as your data collection tool, your data storage solution, or when multiple people need access to the data.

Databases

Databases, in many ways, have the opposite strengths and weaknesses of spreadsheets.  With a powerful relational model, they can store the many, complex relationships that exist in today's data (e.g. a customer that is also a supplier and/or an employee; products with multiple configurations and styles, etc.).  Robust access controls and a server-based architecture provide simultaneous access to data by multiple people while ensuring that only people with permission can access and change the data.

Unlike spreadsheets, databases can be more difficult to use for analysis and reporting.  Their relational model splits data among multiple tables, and knowledge of special querying languages (usually SQL) are necessary to pull data out.  Most databases do not have built in reporting or analysis packages.  Though there are often related offerings that can bring that functionality (e.g. SQL Server can be used with SQL Server Reporting Services and SQL Server Analysis Services), they take special knowledge and experience to program.  Finally, databases do not connect easily with other data sources, which can make cross-source analysis difficult.

Do: Use databases to capture and store complex data, allow multiple people to access and edit data at the same time, and couple them with statistical and reporting packages to perform very complex analysis or reporting.

Don't: Use databases for ad hoc analysis and querying, charts and graphing, combining data from multiple sources.

Why Not Both?

Spreadsheets and databases both have their own strengths and weaknesses, but they are very complementary.  For day to day data capture and storage, you should use a database.  However, when it comes time to do analysis and dig into your data, making an extract of some of the data in your database and putting it in a spreadsheet is a good option.  The combination of these two tools allows you to collect and store your data in a reliable location while allowing you to dig deeply into your data to find other insights.

When choosing between a spreadsheet and a database, keep in mind their individual strengths and weaknesses and make sure you are using the right tool for the job!  Not sure how to choose for your specific situation?  Drop us a line and we're happy to point you in the right direction!