Conditional Formatting

Find and format cells that meet the specified condition. Conditional Formatting makes it easy to identify data by highlighting cells or ranges of cells that meet certain conditions. You can also apply a data bar or icon and use the hue function to easily analyze your data.

Apply Cell Highlighting Rule

Perform the following to change the data display format.

Select a range of cells to apply the cell highlighting rule.
Click Format-Conditional Formatting-Highlight Cells Rules-Between.
In the Between dialog box, enter values in the Format Cells that are between input box.
Select a format from the Format to Apply list.
Click OK.
Values within the specified range are highlighted with the formatting.

Apply Top/Bottom Rule

Perform the following to apply the top rule.

Select a range of cells to apply the top rule.
Click Format-Conditional Formatting-Top/Bottom Rules-Top 10%.
In the Top 10% dialog box, select a format from the Format to Apply list.
Click OK.

Clear Rules from Selected Cell

Perform the following to clear conditional formatting applied to a cell.

Select a range of cells you want to clear the applied formatting.
Click Format-Conditional Formatting-Clear Rules-Clear Rules from Selected Cells.

Delete Conditional Formatting

Perform the following to modify conditional formatting.

Click Format-Conditional Formatting-Rules Management.
In the Conditional Formatting Rules Manager dialog box, select the Delete Rule checkbox.
Click OK.

More Information

The submenu of Format-Conditional Formatting are as follows:

Highlight Cells Rules

Highlight a cell that meets the condition. Please note that the preview images listed in Color Scales do not represent the actual effect.

Top/Bottom Rules

Use for a statistical condition in relation to other values.

Data Bars

Use for a value comparison using gradient-filled horizontal bars in different length.

Color Scales

Highlight cells with different fill colors to ranges of value data.

Icon Sets

Use to rank the cell contents into 3 to 5 categories.

Clear Rules

You can clear conditional formatting from cells or worksheet.

Rules Management

You can define and add new rules and you can delete them at any time. The rules you define can be modified or deleted as you need or want.

Notes

How conditional formatting colors are applied

The preview images listed in Conditional Formatting-Color Scales do not represent the actual effects and are applied differently depending on the size of the cell data. For example, if you have data in ascending or descending order, applying the same color will have different results as in the example below.