Database vs. Spreadsheet
by Karyn Stille
When do you use a spreadsheet, and when
do you need a database? Both application types are used for managing data. How
do you decide which would be more practical?
Before the computer,
bookkeepers, record keepers, and accountants used the paper and pencil method
along with a ledger or record book containing worksheets. Information and
records were stored by hand and financial records were calculated manually and
entered in to the worksheets. Ledgers used rows and columns that people learned
could be used not only for financial records, but also for things like
scheduling, inventory tracking, and employee information.
The dawn of the
computer age brought applications that could store information, perform complex
calculations, and provide a printed output. This concept virtually
revolutionized the use of the computer early on. The first application with any
real power was Visi-Calc, which later became Lotus 1-2-3. Visi-Calc alone gave
businesses a serious enough reason to justify investing in computers for the
office and is actually credited with keeping Apple computers in business. The
advantages of using a spreadsheet application rather than paper and pencil were
numerous - not the least of which was that when data was changed, totals and
other formulas were automatically recalculated, saving both time and headache.
However, Visi-Calc lacked functionality in the way of tools available to the
Currently, Microsoft Excel, along with Lotus 1-2-3, commands most of the
market for spreadsheet applications. Tools have evolved tremendously since that
first Visi-Calc program. Now users have help available at a click of their mouse
along with tools such as complex formula support, formula and function builders,
sorting and filtering, scenario managers (for "What-if" analysis),
charts and graphs, and extended data formatting tools.
organizes information on a particular subject for retrieval. Databases utilize
one or more tables of information entered by the user to retrieve data for a
variety of purposes. Data can be retrieved through methods such as asking
questions of the data (querying), sorting or filtering, and pulling information
into a formatted report, like an invoice, that can be printed. Although the
tables look similar to spreadsheets, the tables are used to store raw data. In
other words, there is no need to format the information in a database table.
Reports generated from the data in the tables are where you would want specific
formatting. Information in a spreadsheet is formatted in the actual spreadsheet,
and that makes data entry a bit more tedious.
Databases also involve the use of
records to structure the tables. A record can contain any number of fields.
Comparing this to a common phone book, a record would be an entire entry for one
individual, and a field would be each separate part of the entire entry - like
the individual's phone number. Reports organize the information in an
understandable way and can combine data by performing complex calculations.
Databases can also easily manage a large amount of information and better
maintain data integrity. For these reasons, databases are much more powerful and
manageable when handling a large amount of information related to a particular
The downfall? The downfall is that most database programs are not as easy
to learn and use as most spreadsheet applications and are not as easy to make
structural changes in once queries, forms, and reports are developed. One must
have knowledge of the best way to structure the information into one or more
tables before any tables are used to develop a means of retrieving the
information. The reason for this, is that once saved queries, forms, and reports
are based on the table(s), any changes in the table(s) structure (like
deleting/changing field names) may cause errors in all the objects based on the
changed table(s). So, it is important that the developer of the database has a
clear vision of all types of information that would need to be included and how
to organize it. This, combined with an interface that's not usually as intuitive
as a spreadsheet, sometimes intimidates would-be database users.
An Example of Database vs. Spreadsheet Use
Now that you are a bit more
familiar with the purposes of the two, how do you determine which is best for
your data? Most businesses find that using both works best. Take a look at the
following simple example:
Company ABC needed a method of storing data related to
customer sales where they could print invoices and be able to track orders and
customer contact information. They also needed to be able to quickly calculate
what an increase or decrease in product prices and/or sales would do to their
overall revenue generation along with a way to analyze trends.
developed an Access database to store all of their customer information and
ordering data. They included the following tables: Contact Information,
Products, and Orders. From this they used the tables and also created queries of
the table data on which to base reports, like invoices. They also created easy
to use forms for inputting data and a user-friendly switchboard for easy
navigation. This gave them an efficient way to enter data, store data, and
generate information for invoices, sales by product, sales by customer, and so
Second, they used Excel spreadsheets to quickly calculate what changes in
price and sales would do to their revenue by creating various scenarios. They
could also use their sales information in Excel to analyze trends by generating
charts and graphs. This gave them an easy way to analyze their data and trends
in a tool with understandable and meaningful formats.
What can we gain from this
example? As a general rule of thumb, databases should be used for data storage
and spreadsheets should be used to analyze data.
If you currently use a spreadsheet to store data, ask yourself the following questions:
- Do changes made in one spreadsheet force you to make changes in others?
- Is the sheer amount of data unmanageable or becoming unmanageable?
- Do you have several spreadsheets that contain related information (such as separate sheets with
sales for branches in Los Angeles, Chicago, and Houston)?
- Can you see all relevant data on one screen, or do you have to keep scrolling to find
- Are several people accessing the data at the same time?
- Do you have a difficult time viewing specific data sets that you want?
answered yes to at least two of the questions, you should think about moving
your information to a database application.
In a Nutshell Use a database if...
- the information is a large amount 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 (change
of address, pricing changes, etc.).
- you want to generate reports based on the
Use a spreadsheet if...
- you want to crunch numbers and perform
- you want to track a simple list of data.
- you want to easily create charts and graphs of your data.
- you want to create "What-if" scenarios.
In most cases, using the combination of a
database to store your business records and a spreadsheet to analyze selected
information works best.