Microsoft Excel 2007 tutorial: conditional formatting new rule


Conditional Formatting New Rule

VI. Conditional Formatting New Rule

 If you wish to format cell with new rules that are different from the predefined rules of Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets, use New Rule. To use New Rule, do the following:

1. Select the range of cells that you want to format.

2. On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.

Excel 2007 New Rule

3. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format. Under Format values where this formula is true, enter your rule in the box (e.g. A2="Male"). Click Format button to format cells with the Format Cell dialog box. In the Format Cell dialog box, you can format the selected cells with Font tab( font style, size, color, effect ), Border tab(line style, color), or Fill tab (color palette, fill effect, standard color, custom color).

Excel 2007 New Formatting Rule dialog box

4. Click Ok button twice.

Example1: By using the following range of cells, highlight the customers who buy the highest brick with blue background.

Excel 2007 example data of new rule

To do this task, do the following:

1. Select the range of cells (A2:E8) if you want to highlight a row that contains maximum brick or you can select (C2:C8) if you want to highlight only the cell that contains the highest brick.

2. On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.

3. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format. Under Format values where this formula is true, enter $C2=max($C$2:$C$8) in the box.

Excel 2007 enter the rule

4. Click Format button, in the Format Cell dialog box, click Fill tab, and then select the blue color on the color palette.

Note: If you can't see the blue color on the color palette, click More Colors button, and then select that color from standard or custom color of the Colors dialog box.

5. Click Ok button twice.

The result will be as the figure below:

Excel 2007 result of highligh maximun brick

Example2: Use the data of example1 to highlight the customers who buy cement less than 300 sacks but more than or equal to 60 sacks.

To do this task, do the following:

1. Select the range of cells (A2:E8) or (D2:D8).

2. On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.

3. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format. Under Format values where this formula is true, enter =and($d2>=60,$d2<300) in the box.

Excel 2007 enter rule of example2

4. Click Format button, in the Format Cell dialog box, click Fill tab, and then select any color that you like (e.g. yellow).

5. Click Ok button twice.

The result of the example, see the figure below:

Excel 2007 result of example2

Example3: Use the example data above to highlight the customers who buy sand less than 10 and the lowest cement. to do this task, do the following:

1. Select the range of cells (A2:E8).

2. On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.

3. In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format. Under Format values where this formula is true, enter =and($e2<10,$d2=min($d$2;$D$8)) in the box.

4. Click Format button, in the Format Cell dialog box, click Fill tab, and then select any color that you like (e.g. green).

5. Click Ok button twice.

The result of the example, see the figure below:

Excel 2007 result of example3

Notes:

- If you want to create, edit, delete 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.

- If you only want to clear format in the cell, you can use Clear Rules command. To use this command, do the following:

1. Select the cell that you want to clear.

2. On the Home tab, in the Styles group, click Conditional Formatting, point to Clear Rules, and then click Clear Rules from selected Cells. If you click Clear Rules from Entire Sheet, all formats in the worksheet will be cleared.

Excel 2007 clear formatting in the cell

 


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.