How to use Icon sets with conditional formatting

Icon sets are a unique kind of conditional formatting in which cells in a selection can be labelled with icons based on certain conditions.

Let's take a look.

Here we have the set of test scores we looked at previously. Let's explore using conditional formatting with icon sets to highlight values in the table.

Icon sets are another primary category in the Conditional Formatting menu. Here, you'll see a large number of icons divided into four main area: Directional, shapes, indicators, and ratings.

As with other conditional formats, Excel will build a live preview of each option on the worksheet

Let's choose a simple set of green, yellow, and red circles from the Shapes area.

Now let's edit this rule to see how it works. For icons in sets of three, Excel will assign icons by dividing values into thirds - the first icon is assigned to the top one third of values, the second icon is assigned to the second third of values, and the third icon is assigned to the lowest one third of values.

If we switch to a four-icon set, we see that icons are assigned at 25 percent increments, and if we switch to a five-icon set, the assignment is in 20 percent increments. Notice there are only two options for operators: greater than, or greater than or equals to.

Let's switch back to the three icon set and change to numbers instead of percentages. Let's assign green to 80 and above, and yellow to 70 and above. Red  is automatically assigned to values less than 70.

Now let's get rid of the yellow icon all together. To do this, we'll set green to greater than 70, and choose "No cell icon" for yellow.

Finally, let's update the rule to show only red icons. To make this change, we just need to choose "No cell icon" for green.

As a final step, let's change the red circle to a red X. Each icon used in a rule can be changed to any other icon. We simply select the red x from the list.