Spreadsheet or Database?

Spreadsheets and databases although originally two very separate application programs, have both evolved to have a few functions that make them more alike. For example, spreadsheets can sort “records,” and do searches while databases offer some ready made formulas to calculate interest and other sums. Although an advanced user can usually go through gyrations that persuade either type of program to do many, but not all, of the things that the other one does, there are some things which are easier in one program than the other. In order to take full advantage of electronic data it is necessary to be familiar with the specialties of both types of programs.

The most important difference is how these two programs look at data. A spreadsheet makes it easy to access an individual cell, thus its data, although in table format, can have different types of information in the same column or row. A database, by contrast, is designed to derive information from "all" the data in a column either for that column alone, or any combination of the other columns. To fulfill this purpose, the data is structured; that is a column (field) contains the same type of information for each line. Each line is a record and contains information pertinent to the same overall entity, e.g. a patient. Many of the functions that each does best is based on this difference.

A spreadsheet is a good tool for work that involves mainly mathematical calculations. A database, however, is far superior for managing information. The main difficulty with using a spreadsheet in place of a database is that one has to use a flat file design (see page 175 in the text) with all the attendant problems that this creates.

Item	Spreadsheets	Databases
Use of memory	Data and calculations are RAM resident, if a file is not saved data is lost if power is lost to the computer.	Uses both RAM and hard drive memory, with most databases data is saved to the hard disk as soon as a the data enterer leaves the record.
Organization of Data	Data cells are independent, formulas can specify specific cells by name. Formulas access one cell at a time.	Data is organized by fields or records. Formulas or queries work on specified fields in a record, or on selected fields for all specified records.
Calculations	Fast Setup is easy Wide selection of financial and statistical functions	Limited to usual calculations but includes date arithmetic. Done using forms or reports.
Charts	Wide choice of charts and graphs. Easy to setup and easy to change.	Selection is good, but limited in comparison to spreadsheet. Data can be exported to spreadsheet to do a chart.
Reports	WYSIWYG, prints tables as are. It is possible to link multiple spreadsheets, but databases make this far easier.	A report does not need to look like the table, each field can be placed anywhere on the report, and used multiple times. It is easy to combine data from more than one table into one report.
Data Entry	Has limited ability to design forms and create “look up” tables. (A look up table provides options from which the data enterer can select.). Not as easily done, or as flexible in this area as a database. Some validity checks possible.	Possess excellent ability to design special forms that show only fields needed and include instructions on how to enter the data. Can also contain read only fields. Forms can allow data entry into one table or many on the same screen. It is easy to provide validity checks on data to prevent some erroneous data by using look-up tables, drop down menus and other data validation features.
Programming Languages	Terse - hard to document and hard to read Hard to debug Suitable for short subroutines or macros	Full featured programming language with excellent documentation and debugging capabilities.
Queries	Limited queries especially for multiple tables.	Can perform very complex data queries on one or multiple tables. Can use set theory.
Relational Tables	Can link spreadsheets, but has more limitations.	Links many different tables easily so appear to user as one table. Very useful in transforming complex data.

Some excellent resources, that although fairly old, remain very current.

Patten, B. (1998). Database vs. spreadsheet is no contest. http://www.bizjournals.com/phoenix/stories/1998/10/19/smallb4.html?page=1

Excel: Database vs. Spreadsheet. A discussion of when to use which in easily understood language.
http://tutorials.esmartweb.com/excel/databasevsspreadsheet.htm

Return to Chapter 10 Databases

Created June 1, 2009

 

In too many instances, a spreadsheet is used when a database would provide more information. Differences are explained here.

Copyright 2009 For errors or additions send to