How to Change Conditional Formatting Icon Set Color in Excel

Excel does not provide a built-in option to modify the color of conditional formatting icon sets. However, you can achieve this by inserting identical icons and then adjusting their colors to suit your preferences. In this way, you can use customized colors while retaining the same functionality as the original icon sets.

In this Excel tutorial, you will learn how to replicate the Conditional Formatting Icon Sets in Excel using similar symbols with changed colors.

Look at the overview image below. You can see the different colors of the icons in the columns labeled “Before Changing” and “After Changing.”

4 Steps to Change the Color of Conditional Formatting Icon Sets in Excel

To change the color of Excel icon sets, we will manually insert similar symbols based on conditions. Then, use Conditional Formatting to change the color.

Here, you can see the Conditional Formatting Icon sets based on the percentage values using the Three Arrows (Colored) option. The Conditional Formatting Icon Sets divided the data range into three sections. A green up arrow appears when the relative cell value is above 67%, a yellow horizontal arrow for values between 33% and 67%, and a red down arrow for values below 33%.

We considered the following conditions to apply the conditional formatting icon sets.

Here are 4 steps to Change the Conditional Formatting Icon Set Color in Excel:

Step 1: Create a Percentile Table

Here, after creating a table we will insert three percentiles manually. Then calculate the values of these percentiles based on the current data range using the PERCENTILE function.

Steps to create a percentile table and calculate corresponding values:

1. Create a table with three columns including Percentile, Value, and Arrow.
2. In the Percentile column, insert 67%, 33%, and 0%.
3. Go to the first cell of the Value column.
4. Insert the following formula: =PERCENTILE($C$5:$C$9,B12)
5. Then, drag the Fill Handle icon to get the outputs rest of the cells.

Step 2: Insert Symbols

You can manually insert symbols identical to the icon sets from the Symbol dialog box.

1. Go to Insert tab > Symbols group > Symbol.
   The Symbol dialog box will appear.
2. In the Symbol dialog box:
   • Select Wingdings 3 in the Font box.
   • Select the up arrow symbol and click on Insert.

   
   The chosen symbol has been inserted into the first cell of the percentile table.
   

3. Repeat the same process to insert the horizontal and down arrows.

Read More: Conditional Formatting with More than 3 Icon Sets in Excel

Step 3: Use the IF Function to Set Icons

Now, apply the conditions to pick the perfect arrow from the symbols table using the IF function. And, change the font style to view the arrows.

To pick icons using the IF function follow the steps below:

1. Insert the following formula into the first cell of the target column: =IF(C5="","",IF(C5>=$C$12,$D$12,IF(AND(C5>=$C$13,C5<$C$12),$D$13,$D$14)))
2. Hit Enter and drag the Fill Handle icon if required. You can see fonts are showing instead of arrows.
3. Select the data range of the output column.
4. Go to Home tab > Font group.
5. Choose Wingdings 3 from the Font drop-down to see the arrows.

Step 4: Change the Color of Icon Sets

To change the color of icon sets apply the Conditional Formatting. For that, insert three distinct formulas as rules to set the colors of the corresponding icons.

To change the color of icon sets follow the steps below:

1. Select the data range containing icons.
2. Go to Home > Styles group > Conditional Formatting drop-down > New Rule. The New Formatting Rule window appears.
3. In the New Formatting Rule window:
   • Select a Rule Type section > Use a formula to determine which cells to format.
   • In the “Format values where this formula is true:” box, insert the following formula: =$C5>=$C$12
   • Click on the Format option.
     The Format Cells dialog box will appear.

   

4. In the Format Cells dialog box:
   • Go to the Font tab.
   • Choose the desired color from the Color drop-down.
   • Finally, click on OK.

   Again, the New Formatting Rule window will appear to show the preview.

5. Click OK in the New Formatting Rule window. You can see the color of the upward arrows has been changed.
6. Following a similar process again and insert the following formula: =AND($C5<$C$12,$C5>=$C$13)
7. Set another font color for this rule and see in the Preview.
8. Again, press OK.
9. Insert another formula as a rule: =$C5<$C$13
10. Set a different color for this rule and press OK.

You can see the color of the icon set has been changed.

Download Practice Book

You can download the practice book from here.

Conclusion

In this article, we have discussed step-by-step procedures to change the color of the conditional formatting icon sets in Excel. By changing these colors, you can make your spreadsheet visually appealing and easier to understand the information. This customization feature allows you to make a set of icons of your personal style and specific design choices. Furthermore, we have also added the practice book. 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.

Frequently Asked Questions

Does changing the Icon Set color affect the source data?

No, changing the Icon Set color doesn’t impact the source data. It enhances the presentation without changing the data.

Are there color limitations for Icon Sets?

There are no limitations. But it’s recommended to select colors from the broad spectrum to maintain clarity and ensure easy interpretation of the icons.

Will the custom Icon Set colors stay the same when I share the Excel file?

Yes, the customized Icon Set colors are saved within the Excel file, ensuring consistency when sharing the workbook with others.

Related Articles

• Conditional Formatting Icon Sets Based on Text in Excel 
• Excel Conditional Formatting Icon Sets Based on Another Cell
  
• Excel Conditional Formatting Icon Sets Relative Reference

<< Go Back to Icon Sets | Conditional Formatting | Learn Excel