Databases are Cool!

When it comes to maintaining the integrity and ease-of-use of reports over a length of time, nothing makes life for the Excel user more trouble-free and uncomplicated than using a well-constructed database and a few simple formulas. Creating a good database can solve a lot of miserable problems in Excel (I am sure most of us have seen how reports can build up to many, many pages, negatively impacting their usefulness).

Although setting up and maintaining a viable database is quite simple, there are a few basic principles by which you should abide. Let’s look at three simple rules:

1) When you first set up a database, be sure to include data in its most granular form. Each column should be characterized by conveniently small and independent parts. For instance, do not create a single column (or “Field” as it is commonly referred to) using a person’s full name. Use separate fields for first, middle, and last names. The same applies to locations, where it is best to separate city and state for instance.

By doing this, you simplify extracting specific data for your reports.

2) Eliminate any blank records, (a row is a record in a database), as well as any blank cells. Use “N/A” or similar notation for any cells in your database.

3) Finally, and very importantly, do not include any calculated fields in your database. The purpose of a database is to store data, not provide any computed information (that is what your linked reports are for).

Okay, you say, what do we do with this database? That is where the fun begins! Next week we will look at how to use some basic database formulas that will make your Excel life soooooo easy! (And that, as Martha Stewart would say, “Is a Good Thing”)