Microsoft Excel 2007 tutorial: coditional formatting icon sets


Conditional Formatting Icon Sets

V. Conditional Formatting Icon Sets

You use Icon Sets to display an icon from icon sets in each cell. Each icon  represents a value in the cell. When you format cells with Icon Sets, you can format them with predefined icon set rules or you can format them with your own icon set rules. To use Icon Sets, 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, point to Icon Sets, and then click the icon sets that you want.

Excel 2007 Icon Sets

To format cells with your own icon sets rules, do the following:

1. Follow step 1 and 2 above, and then click More Rules.

2. In the New Formatting Rule dialog box, under Format all cells based on their values, click the Format Style box, and then click Icon Sets. In the boxes, under Type, choose the type for the value that you set as condition. Under Value, enter values in the first and second boxes for the first and second conditions. In the Icon Style  box, select any icon sets that you want. If you want to reverse icon order, check the Reverse Icon Order check box. If you want to show only icon in the cells that you had  formatted, check the Show Icon Only check box.

Excel 2007 format cell with more icon set rules

3. Click Ok button.

For example, use the following range of cells to format it with Icon Sets.

Excel 2007 the ragne of cells

To do this task, do the following:

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

2. On the Home tab, in the Styles group, click Conditional Formatting, point to Icon Sets, and then click the icon sets that you want (e.g. 4 Traffic Lights).

Excel 2007 result of format cells with color scales

Another example, format the rang of cells above with the 3 Traffic Lights(Rimmed) and the following rules:

- The scores that are greater than or equal to 60 display green icon.

- The scores that are less than 60 but greater than or equal 50 display yellow icon

 - And the scores that are smaller than 50 display red icon.

To do this task, do the following:

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

2. On the Home tab, in the Styles group, click Conditional Formatting, point to Icon Sets, and then click More Rules.

3. In the New Formatting Rule dialog box, under Format all cells based on their values, click the Format Style box, and then click Icon Sets. In the boxes, under Type, select Number. Under Value, enter 60 in the first box and  type 50 in the second box. In the Icon Style  box, select 3 Traffic Lights(Rimmed).

4. Click Ok button.

To be clear, you can see the figure below:

Excel 2007 more rules icon sets

Notes:

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

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