print

The pros and cons of spreadsheets and databases

Back to Library Back to Library
print

The pros and cons of spreadsheets and databases

Use a database if…

  • You have a large amount of information that would become unmanageable in spreadsheet form and is related to a particular subject
  • You want to maintain records for ongoing use
  • The information is subject to many changes
  • You want to generate reports from the information

Use a spreadsheet if…

  • You want to 'crunch numbers' and perform automatic calculations
  • You want to track a simple list of data
  • You want to easily create charts and graphs from your data
  • You want to create ‘what-if’ scenarios1

Hint: if your spreadsheet exceeds 20 columns or 100 rows, you are probably better off using a database.

More on the pros and cons of spreadsheets and databases …

 

 

Spreadsheets

Databases

pros
  • Quick to set up
  • Easy to use
  • Flexible
  • Inexpensive
  • Automatically recalculate formulas
  • You can easily produce stylish charts and graphs
  • You can produce tables of summarised data (‘pivot tables’)
  • Sorting and filtering
  • Cell formatting
  • Provide data integrity
  • Much more powerful and manageable when handling large amounts of information
  • Reduce duplication
  • Minimise irregularities
  • Easier for more than one person to access the file at once
  • Good for long-term storage of records that are subject to changes
  • Large storage capacity
  • Database and reporting features are separate – this means you can generate multiple reports from the same data (ask it lots of questions)
  • You can ask questions of the data ('querying’) and pull the information into a formatted report
  • Can contain text or numbers
  • Can provide complex reports

cons
  • Not ideal for long-term data storage
  • Only offer simple query options
  • Don’t guard data integrity
  • Offer little or no protection from data corruption
  • Can make some links between different pieces of information but it’s limited.
  • Can add text but only 255 characters in each cell
  • Needs a moderate to high level of skills to set up, use and maintain it
  • More rigid – not as easy to make structural changes once the database is set up
  • Not usually as intuitive as a spreadsheet

 

Hint: Databases can be created using software packages such as Microsoft Access or Filemaker Pro; they can be purchased off-the-shelf or they can be developed through ‘open source platforms’ or ‘cloud computing’. But remember that although ‘open source platforms’ sound like they’re free, they’re not always.