more-arw search

Spreadsheets vs. Databases -The Right Tool for the Right Job Part II

Separating the Tabs from the Tables

This is the second part in my series where am making the case of when a database is superior to a spreadsheet.  I will reiterate: spreadsheets are a great tool for presentation of data but databases offer advantages when you need to transform your data or analyze large amounts of data from different sources.  Most people are not as familiar with databases as they are spreadsheets.  Once you fully understand what you can do with a database, you may want to invest time in learning how to create databases and queries.

Ok, let’s talk about a major advantage that a database has over a spreadsheet: multiple users can write to the same database table at the same time.  For the purposes of illustration, think of a database as a workbook and the individual worksheet or tabs as a table.  Only one user can edit a workbook at a time but in a database multiple users can write to the same table at the same time.

Think of a database record as a single row in a worksheet.  Let’s say we have multiple employees updating a customer database at the same time.  Most likely, they are entering information for a single customer at a time.  Each employee can make their updates at the same time because they are not editing the same record.  Each customer is a separate record.  Multiple records in the same table may be edited simultaneously.  This allows for faster more efficient processing.  In the event that two or more users attempt to edit the same record at the same time, the database establishes a “priority” on the order in which the updates occur.

Another advantage is that formulas are entered at the field (column) level.  Rather than having to copy a formula down a column or across a row, you merely setup the formula once at the column level.   Formulas reference other field names rather than referencing specific cell values.  Once you set the field, you don’t have to worry about the formula not being copied correctly.

Databases reduce the need to replicate data.  Many times information in one tab or spreadsheet forms the basis of a calculation in another worksheet.  Let’s say each month you track the closing spot rate for a number of currencies.  You use these spot rates to convert the ending balances in your general ledger. (You would be surprised by how many accounting systems don’t provide a service that regularly feeds foreign exchange rates.)  For each ledger in a non-US currency you copy spot rate information on to the non-US subsidiary ledger balance sheets to affect the conversion.  If you were using a database, the currency rates would be kept in a rate table.  Each time you needed to lookup an FX rate anywhere in your database, your query would reference the rate table to convert the transactions without having to physically keep the same data in two places.

Databases allow you to more efficiently reuse key pieces of data.  Because databases are more structured than a spreadsheet, it gives you’re a higher degree of control and accuracy over your financial data.  I’ll talk more about the advantages of databases in my upcoming series.

  • Spreadsheet cannot be edited by more than one user at a time
  • Formulas are at field level.  Changes propagate through the entire table.
  • No replication of data.  Multiple queries can access the same data
  • Forms can put data into multiple tables at the same time.
  • Data able to be queried.