Database Best Practices

Database Best Practices is always a worthy topic to review.  Assuring good database management includes several of these Best Practices and can they can take many forms.  One important Best Practice goal is to control and Eliminate Blanks in any of our database records.

Conditional Formatting is a familiar topic, and most of us have probably used it occasionally to highlight important information in our workbooks.  A unique method of using this tool takes a Reverse (or Negative) approach to this, however.  It is easy to see how this technique can significantly aid the goal of eliminating blanks.  Let’s look at how Reverse Conditional Formatting can be used to quickly highlight potential discrepancies.

For instance, let’s say you have a worksheet in which you are entering data (it may be numbers, text, or mixed) in a field, and you want it to be Very Apparent if a cell within that column is Blank). Here is a convenient way to use conditional formatting to do this:

In our simple example, let’s say you are going to be putting data in the short range of A1:A16.

1. Your first step is to apply a Fill Color to your range (A dark red, blue, or gray are good choices.)
2. Then select your range and go to Conditional Formatting / New Formatting Rule
3. Choose Use a formula to determine which cells to format and put the following:
4. =IF(NOT(A1=""), TRUE, FALSE)
5. Finally, for your Format, use Fill / No Color

Now whenever you place data (numbers, text, or mixed) in one of your cells in the range, the dark Fill Color is Cleared!

The cells that do NOT have data entered will retain the Original Dark Fill Color which will most certainly draw your attention to a possible problem.  Using this approach will help assure that you are maintaining a clean, viable database that will in turn enable you to extract accurate information, and that obviously, is what it is all about…