Icon Sets in Excel (Conditional Formatting)

In Excel Conditional Formatting, there are icon sets that you can apply to data. These icon sets help to measure which value is lowest, highest, or at the mid.

There is a total of 20 pre-made icon sets in four categories that you can apply to the data.

Keyboard Shortcut to Open Icons Sets: Alt ⇢ H ⇢ L ⇢ I

Apply Icon Sets to a Data

1. First, select the data and go to the home tab.
2. From the home tab, click on the conditional formatting drop-down.
3. After that, go to the “Icon Sets” option.
4. In the end, select the icon set that you want to apply to the selected data.

Custom Rules to Apply Icon Sets

When you apply icon sets to data, Excel automatically creates a rule to apply. It decides which are the values in the lowest and highest, and in the middle. But you can decide these by yourself if you want. Open the icon sets and click on more rules.

This will open the new formatting rule dialog box (Keyboard Shortcut to Open Icons Sets: Alt ⇢ H ⇢ L ⇢ I ⇢ M).

By default, you have the type of rules based on percentage.

But there are four different ways to apply a rule for an icon set.

Use a Custom Numbers Range to Apply Icon Sets

In the following example, we have used a custom range of numbers:

1. Green Up Arrow Icon for values above and equal to 1500.
2. Yellow Bar Icon for values between 1500 and 1000.
3. Red Bar Icon for values below 1000.

Apply Icon Sets Based on a Formula (value Depends on Another Cell)

In the following example, we have applied icon sets based on the values based on the cells C2 and C3. And in the type, we have used a formula.

And when you change any value from both cells the icon sets will also change on data.

Other Options with Icon Sets

1. Reverse Icon Order: While applying icon formatting, you can change the order of the icons in reverse. So, the icon for the highest value will move to the lowest value.
2. Show Icon Only: When you tick mark this option, this will hide the values from the range and show only the icons that you have.
3. Icon Style: You can change the icon style. As you know we have 20 different styles to apply.
4. Different Icons for Each Value: You can choose a different icon for each value. A different icon for middle, low, and high value.

Icon Sets in Excel Mac

If you use Excel for Mac, well, all the options are the same there, just like Windows. The above steps will help you apply Icon Sets there as well.