Microsoft Excel 2007 tutorial: coditional formatting top bottom rules


Conditional Formatting Top/Bottom Rules

II. Conditional Formatting Top/Bottom Rules

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.

Excel 2007 top bottom rules

For example, in the following range of cells, highlight students who get top 5 scores.

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 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).

Excel 2007 top 5 items

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 Custom Format of Top 10 Items

4. Click Ok button.

The result will be as the figure below:

Excel 2007 result of top 5 items

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.

Excel 2007 New Formatting Rule dialog box

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

Excel type the 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:

Excel 2007 highlight female students pass exam

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.