Microsoft Excel 2007 tutorial: coditional formatting highlight cells rules


Conditional Formatting Highlight Cells Rules

I. Highlight Cells Rules

You use Highlight Cells Rules to highlight any cells that you have set the rule. In Highlight Cells Rules, you can use the following criteria:

- Greater Than

- Less Than

- Between

- Equal To

- Text that Contains

- A Date Occurring

- Duplicate Values and other rules.

To use Highlight Cells Rules, do the following:

1. Select the cells that you want to format.

2. On the Home tab, in the Styles, click Conditional Formatting, then point to Highlight Cells Rules, and then click the condition that you want.

Excel 2007 criterial of Highlight Cell Rules

For example, in the following range of cells, highlight students who get scores that are greater than 50.

Excel 2007 example data

To do this task, follow the  procedure below:

1. Select the range of cells in column D (D2:D8).

2. On the Home tab, in the Styles, click Conditional Formatting, then point to Highlight Cells Rules, and then click Greater Than.

3. In the Greater Than dialog box, under Format cells that are GREATER THAN, type 50 in the box, and then click drop-down arrow of the box next to the box that you just type the value to select the format for the cell criteria (e.g. you select Light Red Fill).

Excel 2007 Greater Than dialog box

Note: If you want to define the cell format other than the predefined cell format, click Custom Format, and then, in the Format Cells dialog box, select the format that you want.

Excel 2007 define cell format

4. Click Ok button.

The result will be as the figure below:

Excel 2007 result of Greater Than criteria

Another example, by using the range of cells above, highlight male students who get scores that are greater than or equal to 55. To do this task, do the following:

1. Select the range of cells (A2:D8) that you want to format.

2. On the Home tab, in the Styles, click Conditional Formatting, then point to Highlight Cells Rules, and then click More Rules.

Or on the Home tab, in the Styles, click Conditional Formatting, and then click New Rule.

The New Formatting Rule dialog box is displayed.

3. In the New Formatting Rule dialog box, select the last option: Use a formula to determine which cells to format.

Excel 2007 New Formatting Rule dialog box

4. Under Format values where this formula is true, enter the following formula:

Excel 2007 enter formula

5. Click Format button to format the cells. For example, you fill the cells background with pink color.

6. In the Format Cells dialog box, click Fill tab, and then select pink color on the color palette. If you cannot find this color on the color palette, click More Colors button, then in the Color dialog box, select your color, and then click Ok button three times.

See the result:

Excel 2007 result of using More Rule

Note: If you want to create new rule, edit or delete existing rule, and view all conditional formatting rules in the workbook, click the cell that is formatted, and then on the Home tab, in the Style group, click Conditional Formatting, and then click Manage Rules. In the Conditional Formatting Rules Manager dialog box, you can do what we have mentioned.

 


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.