You use Top/Bottom Rules to highlight any cells that you have set the rule. In Top/Bottom Rules, you can use the following rules:
- Top 10 items
- Top 10%
- Bottom 10 items
- Bottom 10%
- Above Average
- Below Average and More Rules
To use Top/Bottom 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 Top/Bottom Rules, and then click the rule that you want.
For example, in the following range of cells, highlight students who get top 5 scores.
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 Top/Bottom Rules, and then click Top 10 Items.
3. In the Top 10 Items dialog box, under Format cells that rank in the Top, type 5 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 Yellow Fill with Dark Yellow Text).
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.
4. Click Ok button.
The result will be as the figure below:
Another example, by using the range of cells above, highlight female students who pass the example. 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 Top/Bottom 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.
4. Under Format values where this formula is true, enter the following formula:
5. Click Format button to format the cells. For example, you fill the cells background with orange color.
6. In the Format Cells dialog box, click Fill tab, and then select orange 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:
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.
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: