Icon Sets in Excel

Updated on December 28, 2023
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel Icon Sets

Icon sets in Excel are part of conditional formatting graphics available for numerical data sets. By adding these icon graphics, we can design the numbers more beautifully.

Types of Icon Sets in Excel

There are four types of icon sets available under this category.

Type#1 – Directional

Icon Sets Types 1

Type#2 – Shapes

Icon Sets Types 2

Type#3 – Indicators

Icon Sets Types 3

Type#4 – Ratings

Icon Sets Types 4

We can use all four types of icon sets based on the data structure. We should choose based on the data we are showing to the user.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use Icon Sets in Excel? (with Examples)

You can download this Icon Sets Excel Template here – Icon Sets Excel Template

Example #1 – Directional Icon Set

This type of icon set is best suited to represent scores or ratings of employees and students. For example, look at the below data of employees’ rating for the appraisal cycle, out of 10.

Excel Icon Sets Example 1

Let us apply some conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more to these rating numbers. First, we will use the “directional” icon set.

Before we apply the “directional” icon set, we can see four different kinds of icon sets in this category.

Icon Sets Types 1

Let us use the first two sets now. But, first, we need to define the limit of rating points.

For showing the “green” colored up arrow, the rating should be >=9.

For showing the horizontal “yellow” colored arrow, the rating should be >=5.

For showing the “red” colored down arrow, the rating should be <=4.

The steps to use icon sets in Excel are as follows:

  1. First, select column C and click on “Conditional Formatting.”

    Excel Icon Sets Example 1-2

  2. Under “Conditional Formatting” >> “Icon Sets” >> “Directional.”

    Excel Icon Sets Example 1-1

  3. Now, we must choose the first type. We have the data below.

    Excel Icon Sets Example 1-3

  4. It automatically inserts icons, selects the range of cells, and clicks on “Manage Rules” under “Conditional Formatting.”

    Excel Icon Sets Example 1-4

  5. In the below window, double click on the “Edit Rule.”

    Excel Icon Sets Example 1-5

  6. Now, we can see the “Edit the Rule Descriptions” window.

    Excel Icon Sets Example 1-6

  7. As we can see above, the first rule, “green arrow,” is shown when the value is >=67, and the “Type” indicates the percentage. It should be a “Number,” and the “Value” should be 9.

    Excel Icon Sets Example 1-7

  8. For “yellow arrow,” we must change the “Value” to 5 and “Type” to “Number.”

    Excel Icon Sets Example 1-8

  9. The third rule for “red arrow” is the remaining values will be applied with the red arrow. Click on “OK” to see the directional icon set in the data.

    Excel Icon Sets Example 1-9

  10. Now, if we want to apply other Excel icon sets under directional in the “Edit The Description Rule” window, we can change the icon set option, and accordingly, we need to define the rule.

    Excel Icon Sets Example 1-10

Example #2 – Shapes

The shape icon works the same as the previous one. But, we will see different data and use more than three types of shapes in this example.

Look at the below data for this example.

Example 2

We will apply the four traffic lights for this data set. Below is the rule we use.

Green traffic light: If the number is >=50000.

Yellow traffic light: If the number is >=40000.

Red traffic light: If the number is >=30000.

Black traffic light: If the number is <30000.

Select the data, apply the traffic light first, and then edit the rule, as shown below.

Example 2-1

We will have a traffic light like the below image for our data.

Example 2-2

We can easily identify the good revenue-generated locations for respective products by looking at green-colored areas.

In the edit rule, we can play around with other icon sets as well. We can choose any different icon set from the drop-down list and change the rule.

Example 2-3

We have changed from shapes to the “5 Rating” icon set and changed the rule accordingly. Click on “OK” to see the new icon set for the same data.

Example 2-4

Now, we will change the icon set to “Henry Balls.”

Example 2-5

Now, the data looks like this.

Example 2-6

Like this, by defining rules, we can insert any icon sets.

Things to Remember

  • In more than three rules, we must use five icons set category.
  • Generally, “Henry balls” are used to represent percentage data.
  • The traffic lights are eye-catching icon sets.
  • The directional icon sets show growth or decline between two periods.

Recommended Articles

This article has been a guide to Icon Sets in Excel. We learned using Excel icon sets, conditional formatting, examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –