Wednesday, April 11, 2012
Reverse Conditional Formatting
Let’s say you have a worksheet in which you are entering data in a column, and you want it to be Obvious if a cell within that column is Blank (a Best Practice for proper database maintenance). Here is a unique way to do this:
Let’s assume you are going to be putting data in the short range of A1:A10.
1. Your first step is to apply a Fill Color to your range (in this case, A1:A10)
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 Fill Color is Cleared! How Cool is That!
Whenever you have a cell that has NOT had data entered into it, the original Fill Color remains. Give it a try; I think you will find this to be another Great Trick in Your Excel Tool Belt!