Tip of the Week #80 Tip Index

Go to the Prior Tip "America, the beautiful carbon sink" by Peter Huber
Go to the Next Tip AI Trilogy (software for predicting, classifying, and optimizing)
Return to MaxValue Home Page

Conditional Cell Formatting in Excel

A participant in one of my Applied Decision Analysis classes, last summer, shared this technique.

Microsoft^® Excel allows you to use conditional logic to format cells. This greatly helps draw attention to important conditions in your spreadsheets, such as when a negative net cash flow occurs. The formats can embolden, color, border, change background color, and/or pattern to distinguish the cells. Apparently the font name and size cannot be changed, though the font style can be regular, italic, bold or bold italic.

These features are available in the Format, Conditional Formatting pull-down menu.

Any cell may have multiple conditional formatting declarations. If no conditions are satisfied, then the cell displays the underlying cell format as usual. The first condition sets the formatting, in case there are multiple conditions satisfied.

There are two methods for condition formatting:

Cell value is

{Cell Value Is} {<, >, =, etc.} {a numeric value} then format a certain way.

Example: {Cell Value Is} {less than or equal to} {15} then format Red, Bold, Italic

I recently used this statement to flag stocks with P/E ratios less than or equal to 15.

Formula is

{Formula Is} {condition statement that is either True or False} then format a certain way.

Example: {Formula Is} { =AND(X2<$Z$5,K2<15) } then format background color yellow

This statement "fires" when both conditions in the AND statement are true.

Finding conditionally-formatted cells requires either:

Using the conditional formatting function and seeing whether conditions have been set for the selected cell or selected range of cells. If the cells have different conditional formatting , then the field blanks are blank and contain a dashed border.
Using the Edit, Go to, Special (pull-down menu) function and selecting the Conditional Formatting.

Excel's excellent on-line help functions describe these operations in greater detail.

Mr. Robert Faass, in The Netherlands, wrote that he is using conditional formatting to generate Gantt bar charts. He has a range of cells representing weeks, and if a formula condition is satisfied, Excel colors the background. The formula just tests for whether the week is inside two dates.

Robert also reminds that there are up to three conditional formatting tests, and these are done progressively: the first one to "fire" determines the format. This is equivalent to the If and ElseIf statements in some programming languages.

—John Schuyler, January 2001. Revised April 2002