Mursalin Ibne Salehin
In this article, we will learn to change the conditional formatting icon set color in Excel. Unfortunately, you can’t change the conditional formatting icon set color. But we can insert some identical icons and change their colors according to our needs. These icons will serve the same purpose but with different colors. Today, we will demonstrate step-by-step procedures. Using these steps, you can easily change the icon set color. So, without any delay, let’s start the discussion.
How to Change Conditional Formatting Icon Set Color in Excel: Step-by-Step
To explain the steps, we will use a dataset that contains information about the Sales Amount of some sellers. Here, we will insert icons in the range D5:D9. We will use the arrow symbol. Depending on the Sales Amount, there will be 3 types of arrows. For good sales amounts, we will use the up arrow, the horizontal arrow for average sales, and the down arrow for poor sales. Generally, the colors of the arrows are green, yellow, and red. In this case, we will change their colors. Though we are showing the method for the arrow symbol, you can use other symbols also.
STEP 1: Create Percentile Table with Icon
When we use the Conditional Formatting Icon set, the whole dataset divides into three sections. The icon becomes a green up arrow when the relative value of the cell is greater than 67%. When the relative value is less than 67% but greater than 33%, then we have a yellow horizontal arrow. Lastly, we get a red down arrow for values less than 33%. Here, we will insert those symbols and change their colors. For that reason, we need to create a table that holds these percentiles. Let’s follow the steps below to see how we can do it.
- Firstly, create a table with three columns including Percentile, Value, and Arrow just below the dataset.
- In the Percentile column, we have 67%, 33%, and 0%.
Read More: Excel Conditional Formatting: Add Custom Icon Sets
STEP 2: Apply PERCENTILE Function
- Secondly, we need to use the PERCENTILE Function to find the k–th percentile of the range C5:C9.
- To do so, select Cell C12 and type the formula below:
=PERCENTILE($C$5:$C$9,B12)
This formula calculates the value line above 67%. It works on the specific range C5:C9. So, range C5:C9 is the value list here.
- Press Enter and drag the Fill Handle down.
- As a result, you will get the value line for each percentile.
Read More: Excel Conditional Formatting Icon Sets Based on Percentage
STEP 3: Insert Symbols
- Thirdly, you need to insert symbols to express these percentiles. For example, to express values over 67%, we will use the Up arrow.
- For that purpose, select Cell D12.
- Go to the Insert tab and click on the Symbol It will open the Symbol dialog box.
- In the Symbol dialog box, select Wingdings 3 in the Font box.
- Select the up arrow symbol and click on Insert.
- Also, you can use other symbols according to your preference.
- Then, click on Cancel to proceed.
- As a result, you will see the desired symbol in Cell D12.
- Lastly, repeat the same step to insert the horizontal and down arrows.
STEP 4: Use IF Function to Get Icon Set
- In step 4, we will use the IF function to get the correct icons for the relative values inside the icon set.
- So, select Cell D5 and type the formula below:
=IF(C5="","",IF(C5>=$C$12,$D$12,IF(C5>=$C$13,$D$13,$D$14)))- Hit Enter to see the result.
In this formula, the IF function checks if Cell C5 is empty or not. If it is empty then, it shows nothing in Cell D5. If it is not empty, then it compares the value with Cell C12 first. If the value of Cell C5 is greater than Cell C12, then it displays the up arrow. Otherwise, it moves to the second condition. And that is, if Cell C5 is less than Cell C12 and greater than Cell C13, it shows the horizontal arrow. If both conditions don’t work on Cell C5, then it prints the down arrow in Cell D5.
- Now, use the Fill Handle to copy the formula down.
- After dragging the Fill Handle down, select the range D5:D9.
- In the following step, navigate to the Home tab and change the font style to Wingdings 3 in the Font box.
- As a result, the icons will appear in the dataset.
STEP 5: Create New Rule to Apply Conditional Formatting
- After inserting the icons, we need to create new rules to apply conditional formatting.
- First of all, select the range D5:D9.
- Click on the Home tab in the ribbon and select Conditional Formatting. A drop-down menu will appear.
- Select New Rule from there. It will open the New Formatting Rule dialog box.
- Select the ‘Use a formula to determine which cells to format’ rule type inside the New Formatting Rule dialog box.
- Now, go to the ‘Format values where this formula is true’ field and type the formula below:
=$C5>$C$12- Then, click on Format.
- In the Format Cells box, select the Font tab and set the color to Red. You can choose other colors.
- Click OK to move forward.
- Instantly, the dataset will look like the picture below.
- Again, repeat the previous step and type the formula below:
=AND($C5<$C$12,$C5>$C$13)- Then, click on Format.
Here, we have used the AND function. If both conditions of the formula are valid, only then formatting will occur.
- Also, change the Font color to Green this time.
- Click OK to proceed.
- As a result, the horizontal arrows will be green. You can use other colors in place of green.
- Once again, select the range D5:D9 and apply a new rule.
- This time, apply the formula below:
=$C5<$C$13- Click on Format.
- In the Format Cells, change the font color. We have selected the gold color this time.
- Now, click OK.
- Finally, you will be able to change the colors of the icons.
Read More: Conditional Formatting with More than 3 Icon Sets in Excel
STEP 6: Change Text Color
- Moreover, to apply other colors, you can select the cell with the icon and change the Font color from the Home tab.
Read More: Conditional Formatting Icon Sets Based on Text in ExcelÂ
Download Practice Book
You can download the practice book from here.
Conclusion
In this article, we have discussed step-by-step procedures that Change Conditional Formatting Icon Set Color in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.
