Formatting cells based on criteria

You can format cells in columns based on a variety of criteria. For example, you can turn the cells in a column red if they equal a certain value or occur between two dates.

In This Topic

Missing Observations
Greater Than
Less Than
Between
Not Between
Equal To
Match from List
Text that Contains
Date that Occurs

Missing Observations

Complete the following steps to format the cells that contain missing values.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Missing Observations.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Missing Observations.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Missing Observations.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Greater Than

Complete the following steps to format the cells that contain values that are greater than the value that you specify.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Greater Than.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Greater Than.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Greater Than.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
In Format cells that are greater than, enter a value.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Less Than

Complete the following steps to format the cells that contain values that are less than the value that you specify.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Less Than.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Less Than.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Less Than.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
In Format cells that are less than, enter a value.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Between

Complete the following steps to format the cells that contain values that are between the values that you specify. The specified values are included.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Between.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Between.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Between.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
Under Format cells that are between, enter a lower value and an upper value.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Not Between

Complete the following steps to format the cells that contain values that are not between the values that you specify. The specified values are not included.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Not Between.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Not Between.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Not Between.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
Under Format cells that are not between, enter a lower value and an upper value.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Equal To

Complete the following steps to format the cells that contain the value that you specify.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Equal To.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Equal To.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Equal To.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
Under Format cells that are equal to, enter a value.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Match from List

Complete the following steps to format the cells that contain or do not contain the exact values that you specify.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Match from List.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Match from List.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Match from List.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
From the first drop-down list, select one of the following options:
- Format cells that match values in the list
- Format cells that do not match values in the list
From List source, select one of the following options:
- Use values from selected column: Select the values from the list.
- Enter individual values: Type the values.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Text that Contains

This option is available only for text columns. Complete the following steps to format the cells that contain the text that you specify.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Text that Contains.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Text that Contains.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Text that Contains.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
Under Format cells that contain, enter the text (case sensitive).
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Date that Occurs

This option is available only for date columns. Complete the following steps to format the cells that contain dates that occur within the time period that you specify.

Tip

To find dates that fall between two specific dates, choose Data > Conditional Formatting > Highlight Cell > Between.

Do one of the following to open the dialog box:
- Choose Data > Conditional Formatting > Highlight Cell > Date that Occurs.
- Click in the column, then choose Editor > Conditional Formatting > Highlight Cell > Date that Occurs.
- Click in the column, right-click, then choose Conditional Formatting > Highlight Cell > Date that Occurs.
In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
From Format cells containing a date that occurs, select the time period.
From Style, select the format for the cells that meet the condition.
If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.