Database Best Practices Revisited

There are a few Excel Best Practices that separate accomplished Excel Gurus from the masses of business folk who struggle unnecessarily with spreadsheets.  It is, consequently, a good idea to occasionally review these gems.

The design, construction, and information-mining of Good Databases are the keystones of knowledge for any advanced Excel user.  It is, therefore, very important to follow some easily applied Database Best Practices.  Although, depending on your needs, there may be other worthwhile techniques, I have broken out the following Six Practices that will serve you well.

1.  Data on One Worksheet; Information on Another Worksheet
Whenever possible, put all of your Data in one worksheet, and your Reports in another worksheet. The fewer the worksheets you have, the easier it will be for your users to navigate and garner the information they are seeking.  It also looks much more professional.

2.  No Blank Rows or Columns
Avoid blank rows and columns in your data table. The formulas you create on a separate Report Page will have more integrity.

3.  Deconstruct Your Data 
Always try to divide your data down to its minimum components. You will be able to use more powerful functions and search your data much more effectively. For example, if you have a database of employees, create separate fields for the first, middle, and last names (you can always easily combine them later if you wish).

4.  Sort Your Data 
This is a good habit to adopt, and certain Lookup functions will work only if your data being sorted in a logical order.

5.  Columns are for Fields
Excel obviously has far fewer columns than rows, so keep things simple by using the columns for the fields and the rows for the individual records.

6.  Everything in Its Place

Make sure the data is entered in the proper field. If the data entry person (maybe you) cannot find the right place for a piece of data, perhaps the database needs some redesigning.

If you adopt these practices, you will be well on your way to being the Excel Guru everyone relies upon. Organization = Simplification = Information.