Excel Conditional Formatting Icon Sets Based on Another Cell

Get FREE Advanced Excel Exercises with Solutions!

It is sometimes necessary to format a lot of data in Excel in certain ways for better readability when dealing with a large amount of data. If you are looking for some special tricks to insert conditional formatting icon sets based on another cell in Excel, you’ve come to the right place. There are numerous ways to insert conditional formatting icon sets based on another cell in Excel. This article will discuss four suitable examples of inserting conditional formatting icon sets based on another cell in Excel. Let’s follow the complete guide to learn all of this.


How to Insert Conditional Formatting Icon Sets Based on Another Cell in Excel: 4 Suitable Examples

We will use four effective and tricky examples to insert conditional formatting icon sets based on another cell in Excel. Here, we will illustrate four examples of inserting conditional formatting icon sets: shapes icon sets, directional icon sets, indicators icon sets, and rating icon sets. This section provides extensive details on the four ways.  You can use either one for your purpose, they have a wide range of flexibility when it comes to customization. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Inserting Shapes Icon Sets Based on Another Cell

Here, we are going to demonstrate how to insert conditional formatting shapes icon sets based on another cell in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing students’ names and math scores. We are now going to use shape icons to show whether the student passed or failed the exam. Here we will use the IF function. Let’s walk through the following steps to insert shape icon sets based on another cell in Excel.

Excel Conditional Formatting shapes Icon Sets Based on Another Cell

📌 Steps:

  • First of all, we will use the following formula in cell D5 to illustrate passed and failed scores in the Review column.

=IF(C5>40,0,1)

This formula will check whether the value of cell C5 is greater than 40 or not. If the condition is met, the function will return 0. Else it returns 1.

  • Then, press Enter.

apply if function

  • Next, drag the Fill Handle icon to fill the other cells with the formula.
  • As a result, you will get the following Review column.

show the output

  • Next, go to the Home tab and select Conditional Formatting.
  • Then, from the drop-down menu select Icon Sets.
  • Afterward, from the drop-down menu select More Rules.

  • Therefore, the New Formatting Rule window will appear.
  • Select Format all cells based on their values from the Select a Rule Type option.
  • Next, in the Icon Style box select Shapes Icon.
  • Afterward, check the Show Icon Only option.
  • Next, enter your preferred number in the Value box.
  • In order to change the icon color, you must first select the color from the Icon option, then click on the drop-down icon and select the color based on your preference.
  • Next, click on OK.

  • As a consequence, you will be able to insert conditional formatting shapes icon sets based on another cell.

getting Conditional Formatting shapes Icon Sets Based on Another Cell in Excel

Read More: Excel Conditional Formatting: Add Custom Icon Sets


2. Inserting Indicators Icon Sets Based on Another Cell

We will now show you how to insert conditional formatting indicators icon sets based on another cell in Excel. Here is our Excel dataset so you may understand what we are trying to achieve with this article. A dataset shows different tasks of competition and their completion conditions. We are now going to use indicator icons to show the completion status of each task. Here we will use the IF function. Let’s walk through the following steps to insert indicator icon sets based on another cell in Excel.

Inserting Excel Conditional Formatting Indicators Icon Sets Based on Another Cell

📌 Steps:

  • First of all, we will use the following formula in cell D5 to illustrate the status of each task in the Status column.

=IF(C5>=100%,4,3)

This formula will check whether the value of cell C5 is greater than 100% or not. If the condition is met, the function will return 4. Else it returns 3.

  • Then, press Enter.

apply if function

  • Next, drag the Fill Handle icon to fill the other cells with the formula.
  • As a result, you will get the following Status column.

get the output

  • Next, go to the Home tab and select Conditional Formatting.
  • Then, from the drop-down menu select Icon Sets.
  • Afterward, from the drop-down menu select More Rules.

  • Therefore, the New Formatting Rule window will appear.
  • Select Format all cells based on their values from the Select a Rule Type option.
  • Next, in the Icon Style box select Indicators Icon.
  • Afterward, check the Show Icon Only option.
  • Next, enter your preferred number in the Value box.
  • In order to change the icon color, you must first select the color from the Icon option, then click on the drop-down icon and select the color based on your preference.
  • Next, click on OK.

select a rule type

  • As a consequence, you will be able to insert conditional formatting indicators icon sets based on another cell.

getting Conditional Formatting Indicators Icon Sets Based on Another Cell in Excel

Read More: Conditional Formatting Icon Sets Based on Text in Excel 


3. Inserting Directional Icon Sets Based on Another Cell

Now, we are going to demonstrate how to insert conditional formatting directional icon sets based on another cell in Excel. In order to understand what this article is about, let us first introduce you to our Excel dataset. A dataset shows different company’s stock price and their changes in stock prices in terms of percentage. We are now going to use directional icons to show the status of each company’s stock price. Here we will use the IF function. Let’s walk through the following steps to insert directional icon sets based on another cell in Excel.

Inserting Excel Conditional Formatting Directional Icon Sets Based on Another Cell

📌 Steps:

  • First of all, we will use the following formula in cell E5 to illustrate the status of each company’s stock in the Status column.

=IF(D5>0%,3,2)

This formula will check whether the value of cell D5 is greater than 0% or not. If the condition is met, the function will return 3. Else it returns 2.

  • Then, press Enter.

apply if function

  • Next, drag the Fill Handle icon to fill the other cells with the formula.
  • As a result, you will get the following Status column.

show the output

  • Next, go to the Home tab and select Conditional Formatting.
  • Then, from the drop-down menu select Icon Sets.
  • Afterward, from the drop-down menu select More Rules.

  • Therefore, the New Formatting Rule window will appear.
  • Select Format all cells based on their values from the Select a Rule Type option.
  • Next, in the Icon Style box select Directional Icon.
  • Afterward, check the Show Icon Only option.
  • Next, enter your preferred number in the Value box.
  • In order to change the icon color, you must first select the color from the Icon option, then click on the drop-down icon and select the color based on your preference.
  • Next, click on OK.

select a rule type

  • As a consequence, you will be able to insert conditional formatting directional icon sets based on another cell.

getting Conditional Formatting Directional Icon Sets Based on Another Cell in Excel

Read More: Excel Conditional Formatting Icon Sets Based on Percentage


4. Inserting Ratings Icon Sets Based on Another Cell

Now, we are going to demonstrate how to insert conditional formatting rating icon sets based on another cell in Excel. Our intention with this article is to first provide you with an overview of our Excel dataset as a means of understanding what we are trying to accomplish. A dataset shows different products ID no and total sales of each product. We are now going to use rating icons to show the ratings of the total sales of the products. Here we will use the IF function. Let’s walk through the following steps to insert rating icon sets based on another cell in Excel.

Inserting Excel Conditional Formatting Ratings Icon Sets Based on Another Cell

📌 Steps:

  • First of all, we will use the following formula to illustrate the ratings of the total sales of the products in the Ratings column.

=IF(D5>6000,3,2)

This formula will check whether the value of cell D5 is greater than 6000 or not. If the condition is met, the function will return 3. Else it returns 2.

  • Then, press Enter.

apply if function

  • Next, drag the Fill Handle icon to fill the other cells with the formula.
  • As a result, you will get the following Ratings column.

get the output

  • Next, go to the Home tab and select Conditional Formatting.
  • Then, from the drop-down menu select Icon Sets.
  • Afterward, from the drop-down menu select More Rules.

  • Therefore, the New Formatting Rule window will appear.
  • Select Format all cells based on their values from the Select a Rule Type option.
  • Next, in the Icon Style box select Ratings Icon.
  • Afterward, check the Show Icon Only option.
  • Next, enter your preferred number in the Value box.
  • In order to change the icon color, you must first select the color from the Icon option, then click on the drop-down icon and select the color based on your preference.
  • Next, click on OK.

  • As a consequence, you will be able to insert conditional formatting Ratings icon sets based on another cell.

getting Conditional Formatting ratings Icon Sets Based on Another Cell in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.


Conclusion

That’s the end of today’s session. I strongly believe that from now, you may be able to insert conditional formatting icon sets based on another cell in Excel. If you have any queries or recommendations, please share them in the comments section below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo