Conditional Formats: Knowing when to attract attention to yourself

Everyday you pick out your clothes and chose a color. If your are like me, most of the time it is random and you put on the closest thing to you when you open the closet. Except, of course, on days when there is a Laker game on. On those days, there is a reason why I am wearing Purple and Gold.

If you know what you want your cells to be aware of, you can also have them put on a specific color or cell style when these conditions are met. These conditions are limited only by your imagination. You can have a format change because it is a particular day of the week, the number inside it is above (or below a threshold) or because a particular stock price continues to decline (even though you have repeatedly asked it, ever so kindly, to stop it). The trick to all these is identifying what the variable is and the formula that determines if the condition is either TRUE or FALSE. The rest of it is simply using the Conditional Format Wizard [ALT + H, L, N].

[Formulas that return TRUE or FALSE usually require some comparison operation like > (greater than), < (less than), = (equal to), <> (not equal to). Alternatively you can use an investigative IS formula such as ISTEXT (returns TRUE if cell has text), ISERROR (returns TRUE if formula has returned an error) etc... Full list available here. You can also manipulate the logic by using one of these formulas such as NOT (returns opposite of TRUE or FALSE), OR (returns true if any of the listed conditions is TRUE), AND (Returns TRUE if all conditions are true)]

Lets play with a simple example that checks to see if a date is on a weekend. To do this we will make use of the formula WEEKDAY, which returns a digit from 1-7 representing a day of the week. If Saturday is 6 & Sunday is 7 then we are checking if WEEKDAY > 5.

In Excel 2007, on your Home tab in your ribbon, you will see Conditional Format drop down, select New Rule in order to set a custom formula like we are describing above. (The other standard formats evaluate the values of the individual cell) Then select use a formula to determine which cells to format and type in the logic we described above as shown below:
Note that since I highlighted multiple columns to be formatted but only have the date listed in the first column in my formula, I added an absolute column reference so that the Weekday & Scheduled columns don't try to perform the WEEKDAY formula on the text that is displayed but rather check the column that holds the date.

Of course to customize the format click the format button and customize away.

Attached is the workbook that shows the below self-aware table of cells.

As always, feel free to leave me your questions comments and future blog ideas.

-Danny