Light Up the Cells with Conditional Formatting!

Conditional Formatting is a great tool for instant, visual results based on values, text or formulas in one or more cells. This is accomplished by creating rules for each desired result. It can be as simple as formatting all cells based on their values (the default), which could be applying a color in cell(s) that are above or below a certain value, contain specific text or fall within certain dates as well answer more complex questions.

Conditional Formatting is located on the Home tab | Styles group:

You can also choose to have the results displayed as Data Bars, Color Scales or Icon Sets (arrows or star ratings) instead of one solid color.

More than one condition can be applied to the same range of cells. This example below is returning two different results with two different cell colors based on two different questions (AND requires both conditions to be true but OR allows for either condition to be true to apply the rule).

The formulas in the last two columns have returned Yes or No based on True or False results. Range Names have been created from the header row text to make for easy identification of the cell references in the formulas. Then Conditional Formatting rules have been applied to designate the color(s) based on that answer.

=IF(AND(Years_Under_Contract<2,Number_of_Books_in_Print>4)=TRUE,”Yes”,”No”)

=IF(OR(Years_Under_Contract>5,Number_of_Books_in_Print>=10)=TRUE,”Yes”,”No”)

It is now very clear to see how many authors met none, one or both of the conditions.

Formatting Rules

There are four formatting rules applied here:

Rule Types

This is the Rule Type applied to the first rule for formatting only cells that contain “No” in the last two columns with Pink fill:

Each of the other three rules have their own Rule Description but all are based on Format only cells that contain (the second rule).

Note: You can include a formula directly in the condition by choosing the Rule Type: Use a formula to determine which cells to format and typing the formula in the Edit the Rule Description area.

Have you been using Conditional Formatting and, if so, which rule do you use the most? If not, I hope this post will encourage you to use this powerful feature. Thanks for reading!