Microsoft Excel :  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?

Spreadsheets

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 user.

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.

Databases

A database 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 topic.  

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.

First, they 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 on.

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 information?

• Are several people accessing the data at the same time?

• Do you have a difficult time viewing specific data sets that you want?

If you 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 information.

Free Microsoft Office Tutorials & MS Office Tips Newsletter
http://tutorials.esmartweb.com