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.
📌 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.
- Next, drag the Fill Handle icon to fill the other cells with the formula.
- As a result, you will get the following Review column.
- 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.
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.
📌 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.
- Next, drag the Fill Handle icon to fill the other cells with the formula.
- As a result, you will get the following Status column.
- 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.
- As a consequence, you will be able to insert conditional formatting indicators icon sets based on another cell.
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.
📌 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.
- Next, drag the Fill Handle icon to fill the other cells with the formula.
- As a result, you will get the following Status column.
- 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.
- As a consequence, you will be able to insert conditional formatting directional icon sets based on another cell.
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.
📌 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.
- Next, drag the Fill Handle icon to fill the other cells with the formula.
- As a result, you will get the following Ratings column.
- 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.
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.
