Wednesday, February 11, 2015

Negative Space and CF

Making your data Come Alive in Excel is much more than mere esthetics.  Indeed, it is all about better communication and the dissemination of information.

We are all familiar with Conditional Formatting, and probably most of us have used it to highlight important information in our workbooks.  A unique method of doing this takes a Reverse (or Negative) approach to this however.  Let’s explore this cool way to use Conditional Formatting:

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 (this is a recommended practice for good database maintenance). Here is a truly different way to do this:

For sake of 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 gray is a good choice.)

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 gray Fill Color is Cleared! Bamm, it’s gone!

The cells that do NOT have data entered into them will retain the Original Fill Color.  Try this out and see if this does not make your worksheet Come Alive!

No comments: