WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Icon Sets in Excel

Icon Sets in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj 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

We can see 4 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 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)

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. Look at the below data of employees rating for the appraisal cycle, and rating is out of 10.

Excel Icon Sets Example 1

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

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Icon Sets Types 1

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.

Excel Icon Sets Example 1-1

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

Excel Icon Sets Example 1-2

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

Excel Icon Sets Example 1-3

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

Excel Icon Sets Example 1-4

  • Step 5: Now, in the below window, double click on the rule to edit the rule.

Excel Icon Sets Example 1-5

  • Step 6: Now, we will see the “Edit Formatting Rule” window.

Excel Icon Sets Example 1-6

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

Excel Icon Sets Example 1-7

  • Step 8: Now for “Yellow Arrow,” change the value to 5 and type to number.

Excel Icon Sets Example 1-8

  • Step 9: 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.

Example 1-9

  • Step 10: 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.

Example 1-10

Example #2 – Shapes

This type of icon is works the same as the previous one, but 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.

Example 2

For this data set, we will apply the “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.

Example 2-1

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

Example 2-2

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 icon sets and change the rule.

Example 2-3

Now I 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 I will change the icon set to “Henry Balls.”

Example 2-5

Now data look like this.

Example 2-6

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

Things to Remember

  • In case of more than 3 rules, then use 5 icons 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 –

  • New Line in Excel Cell
  • Conditional Formatting with Formulas Examples
  • KPI Dashboard in Excel
  • Conditional Formatting for Blank Cells in Excel
  • Calculate IRR in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Icon Sets Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More