Icon Sets in Excel

Excel Icon Sets

Adding eye-catching formatting to the data is key while preparing dashboards and presentations. Adding graphical shapes to the data will definitely catch user attention and probably convey the message better than the numbers alone can tell. Adding graphical shapes to the data is possible through “Conditional Formatting” and in this article, we will show you how to work with Excel “Icon Sets” of conditional formatting.

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.

We can see 4 types of “Icon Sets” available under this category.

Type#1 – Directional

Type#2 – Shapes

Type#3 – Indicators

Type#4 – Ratings

We can use all the above 4 types of icon sets based on the data structure and you should choose based on the data you are showing to the user.

How to Use Icon Sets in Excel with Examples

Below are Examples and its types of excel icon sets.

#1 – Directional

This type of excel icon set is best suited to represent scores or ratings of employees and students. Look at the below data of employees rating for the appraisal cycle and rating is out of 10.

Let’s apply some conditional formatting to these rating numbers. We will apply the “Directional” icon set in excel.

Before we apply the Directional icon set we can see 4 different kinds of icon sets in this category.

Let’s use the first two sets now, for this we need to define the limit of rating points.

To show Green colored up arrow Rating should be >=9.

To show horizontal Yellow-colored arrow rating should be >=5

To show Red-colored down arrow rating should be <=4.

Step 1: Now select column C and click on Conditional Formatting.

Step 2: Under Conditional formatting >>> Icon Set >>> Directional.

Step 3: Select the first type and now we have data like the below.

This is automatically inserted icons, select the range of cells and click on “Manage Rule” under Conditional Formatting.

Now in the below window double click on the rule to edit the rule.

Now we will see “Edit Formatting Rule” window.

As you can see above first rule “Green Arrow” says when the value is >=67 and type says percentage, this should be number and value should be 9.

Now for “Yellow Arrow” change the value to 5 and type to number.

The third rule for Red Arrow is the remaining values will be applied with Red Arrow.

Click on Ok, to see the Directional Icon Set in the data.

Now if you want to apply other excel icon sets under Directional in the edit rule window you can change the icon set option and accordingly you need to define the rule.

#2 – Shapes

This type of icon is works the same as the previous one but this we will see different data and will use more than 3 types of shapes in this example.

Look at the below data for this example.

For this data set, we will apply “Four Traffic Lights”. Below is the rule we apply.

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.

As usual, select the data and first apply traffic light and then edit the rule as shown below.

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

Now by looking at Green colored areas, we can easily identify the good revenue generated locations for respective products.

Now in the edit rule, we can play around with other icon sets as well. From the drop-down list, we can choose any other excel icon sets and change the rule.

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

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

Now data look like this.

Like this, by defining rules we can insert any kind of Excel Icon Sets.

Things to Remember

• In case of more than 3 rules then use 5 icon set category.
• Henry balls are generally used to represent percentage data.
• Traffic lights are the eye-catching icon sets.
• Directional icon sets are used to show growth or decline between two periods.

Recommended Articles

This has been a guide to Icon Sets in Excel. Here we will learn how to use excel icon sets using conditional formatting along with examples and a downloadable excel template. You may learn more about excel from the following articles –

• Conditional Text Formatting in Excel
• New Line in Excel Cell
• Conditional Formatting with Formulas Examples
• Create KPI Dashboard in Excel
• Power View in Excel
• Conditional Formatting for Blank Cells in Excel