Leading the way in Microsoft Office Development

Step 1 - What is a Single Cell Condition ?

This looks at the actual value in a cell. This is not necessarily the value that is displayed.

In the first drop-down box select "Cell Value is".

In the second drop-down box you can choose from a selection of different operators.

The remaining boxes are for your values. These can either be entered directly as constants or they can be cell references.

Step 2 - Using a Constant

We are going to apply conditional formatting to the range of cells "B2:E5" and we are going to shade all the cells which contain a number greater than 10.

Select the range of cells you want to apply the conditional formatting to, in this case cells "B2:E5".

Select (Format > Conditional Formatting) to display the Conditional Formatting dialog box.

Select "Cell Value Is" in the first drop-down box and "greater than" in the second dialog box.

Enter the constant value 10 in the third box.

Click the Format button to apply your specific formatting, in this case we are just applying a grey background.

This is actually the (Format > Cells) dialog box but it only contains the Font, Border and Patterns tabs.

Press OK to apply your conditional formatting.

Step 3 - Using a Cell Reference

This is almost identical to the above except that instead of entering the value 10 you can actually use a cell reference to a cell containing the value 10.

This approach can make it quick to change the condition which is used.

Select the range of cells you want to apply the conditional formatting to, in this case cells "B2:E5".

Select (Format > Conditional Formatting) to display the Conditional Formatting dialog box.

Select "Cell Value Is" in the first drop-down box and "greater than" in the second dialog box.

Instead of entering a constant you can also use a cell reference, select the cell "E7".

Press OK to apply your conditional formatting.

You can quickly test if this works by changing the value in cell "E7" to the number 5.

For really useful conditional formatting techniques you need to use formulas rather than single values to create your conditions.

Using formulas in your conditions is discussed on the next page.

Step 4 - Things to Remember

You can either enter cells directly of you can use a cell reference.

If you choose "between" or "not between" then you have the choice to enter both a lower and upper limit