Leading the way in Microsoft Office Development

What is Conditional Formatting ?

Conditional Formatting is the automatic formatting of cells based on the values that the cells contain.

You can define conditions which when true will mean formatting is applied to the cells.

If the condition is False, then no formatting is applied.

This is very useful for quickly identifying cells that meet certain criteria.

For example conditional formatting could be used to quickly identify all the cells which contain a value greater than 10.

Advantages and uses for Conditional Formatting

This formatting is dynamic and is done automatically as the value changes.

1) Identifying all negative numbers in a range of cells.

2) Identifying all cells that contain a value in a specific range.

3) Identifying all the cells that contain an error.

4) Quickly shading alternate rows or columns.

Using Conditional Formatting

This can be found by selecting (Format > Conditional Formatting).

You can enter a maximum of three conditions per cell

The condition can either be based on the value in that particular cell or based on a formula.

Use the first drop-down list to select the type of condition you would like to use.

You can create a condition based either on the actual value in the cell or a formula that uses that cell to determine a condition.

Cell Value is

This is for simple conditions, for example:

Formula is

This is for more complicated conditions, for example:

Removing Conditions

You can remove your conditions by pressing the "Delete" button on the Conditional Formatting dialog box.

This will display the Delete dialog box.

Select which conditions you want to remove and press OK.

These changes are not made permanent until you press OK on the Conditional Formatting dialog box.

Things to Remember

If you copy a cell and paste it over a cell that has Conditional Formatting then the formatting rules will be lost without a prompt.

You can only have a maximum of 3 conditions

If you want to apply conditional formatting to a large block of cells, it is easier to apply the formatting to just one cell and then copy and paste the format.

Try to avoid applying conditional formatting to an entire column or row as this will increase the size of your workbook. Only apply it to the necessary range.

The conditional formatting will get copied automatically to any new rows or columns that get inserted into a range of cells that contains conditional formatting.