How to change conditional formatting icon set color in Excel?
When working with Excel's conditional formatting icon sets, you may notice that each icon is associated with a specific color by default. Typically, if a value is greater than 67% of all values, the icon is displayed as an up arrow in green. If the value falls between 33% and 67%, a yellow horizontal arrow is shown, and values below 33% are indicated by a red down arrow. However, these default icon colors are fixed and cannot be easily modified using standard Excel options. Do you want to change these icon set colors to better match your preferences or reporting style, as depicted in the screenshots below?
 |  |  |
Change conditional formatting icon set
VBA custom conditional formatting icon set
Change conditional formatting icon set
Currently, Excel does not offer a built-in way to directly change the colors of conditional formatting icon sets. However, you can use a creative workaround by manually inserting symbols (such as arrows) and then apply conditional formatting to adjust their colors as needed. This flexible approach allows you to match icon colors to your own preferences or reporting standards. This method is especially useful in dashboards, visual reports, or when corporate branding requires color coordination.
Here's how you can implement this method, which not only enables you to customize the colors but also allows you to select specific icon shapes by using symbols from fonts like Wingdings3:
1. Select a blank cell next to your data list, for example, use F2. Enter 67% in F2, then enter 33% in F3, and 0% in F4. This creates your percentile thresholds for conditional icon display.
2. In cell G2, enter the following formula to calculate the value line above 67%. (in this case, C1:C8 represents your value list), then drag fill handle down to calculate evert value line.
=PERCENTILE($C$1:$C$8, F2)
3. Next, in H2, insert your first icon by clicking Insert > Symbol. In the Symbol dialog, select Wingdings3 in the Font dropdown, then select and insert an upward arrow symbol (e.g., character code232) into H2. This symbol represents high values. See screenshots:
 | ||
 | |  |
4. Repeat the above to insert a horizontal arrow in H3 and a down arrow in h2 for your intermediate and low values, respectively. Double-check the symbol selection to match your intended visual design.
5. Now, in column B (next to your value list starting with C1), enter this formula in B1 to assign an icon based on each value. After entering the formula, drag the fill handle down to fill the formula alongside your entire list of values
=IF(C1="","",IF(C1>=$G$2,$H$2,IF(C1>=$G$3,$H$3,$H$4)))Tip:
- C1: Reference to your value cell.
- G2: is the 67% value line
- H2: Symbol (arrow) shown for the highest values.
- G3: is the 33% value line.
- H3: Arrow for middle-range values.
- G4: is the value below 33%.
- h2: Arrow for low-range values.
6. Highlight the range containing the arrows (e.g., cells B1:B8), then set the font of these cells to Wingdings3. This step ensures the arrows display correctly as symbols instead of random letters or characters.
7. To color the arrows according to your desired color scheme, select the icons range, go to Home > Conditional Formatting > New Rule. This opens the dialog to create formatting conditions for your custom icons.
8. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format. Enter this formula to target your highest values: =$C1>=$G$2. Click Format, go to the Font tab, and select your desired color for the up arrows (for example, red for emphasis).
Tip: Double-check that your absolute and relative cell references are correct to ensure formatting applies as intended, especially if you copy conditions to other ranges.
9. Click OK twice to apply this new format. Your up arrows should now appear in the chosen color.
10. Repeat the process for the horizontal and down arrows, using these respective formulas in the New Rule dialog:
Format horizontal arrows:
=AND($C1<$G$2,$C1>=$G$3)Format down arrows:
=$C1<$G$3Add different colors for each type of arrow as desired.
 |
 |
Additionally, you can further fine-tune the appearance by manually changing the font color of arrows at any time via the Font Color button under the Home tab. This is useful for making quick color adjustments without altering the underlying conditional rules.
By following these steps, you can simulate icon sets with customized colors, helping your reports become more visually distinct and better suited to your needs. This workaround is most suitable when you require full control over icon colors or need to match a report's aesthetic requirements. However, keep in mind that this approach requires additional columns and careful formula management; icon alignment may differ from the default built-in icon sets.
If you need a solution that works more seamlessly with built-in icons, or if you want more advanced icon customization (such as using images or entirely new icons), please refer to the VBA alternative described below.
VBA Code - Create a custom conditional formatting icon set (user-defined images or colors)
A limitation of standard Excel is that you cannot directly change the color of built-in icon sets in conditional formatting. To overcome this, you can use VBA to programmatically insert shapes or pictures (such as colored icons or custom-made images) into cells based on cell values. This technique is particularly effective when you want to display icons in any color or style, going beyond the built-in Excel options.
1. Click Developer Tools > Visual Basic to open the VBA editor. In the new Microsoft Visual Basic for Applications window, click Insert > Module, then copy and paste the following code into the new module:
Sub CustomConditionalIcons()
Dim rng As Range
Dim cell As Range
Dim ws As Worksheet
Dim upIcon As String, midIcon As String, downIcon As String
Dim upColor As Long, midColor As Long, downColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng = Application.InputBox("Select data range for custom icons", xTitleId, Selection.Address, Type:=8)
' Define Unicode symbols to use as icons (can replace with other symbols or images if desired)
upIcon = ChrW(9650) ' ▲ Up triangle
midIcon = ChrW(9651) ' △ Mid triangle
downIcon = ChrW(9660) ' ▼ Down triangle
' Define colors (RGB)
upColor = RGB(0, 176, 80) ' Green
midColor = RGB(255, 192, 0) ' Yellow
downColor = RGB(255, 0, 0) ' Red
' Remove existing shapes/icons from the range
Dim sh As Shape
For Each sh In ws.Shapes
If Not Intersect(sh.TopLeftCell, rng) Is Nothing Then
If Left(sh.Name, 13) = "CustomIconSet" Then
sh.Delete
End If
End If
Next
' Loop through cells and add custom icons
For Each cell In rng
If IsNumeric(cell.Value) Then
Dim percentile67 As Double, percentile33 As Double
percentile67 = Application.WorksheetFunction.Percentile(rng, 0.67)
percentile33 = Application.WorksheetFunction.Percentile(rng, 0.33)
Dim iconText As String
Dim iconColor As Long
If cell.Value >= percentile67 Then
iconText = upIcon
iconColor = upColor
ElseIf cell.Value >= percentile33 Then
iconText = midIcon
iconColor = midColor
Else
iconText = downIcon
iconColor = downColor
End If
' Insert icon as a TextBox shape
Dim iconShape As Shape
Set iconShape = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, cell.Left + 2, cell.Top + 2, cell.Width - 4, cell.Height - 4)
iconShape.TextFrame.Characters.Text = iconText
With iconShape.TextFrame2.TextRange.Font
.Size = cell.Font.Size
.Fill.ForeColor.RGB = iconColor
.Name = cell.Font.Name
End With
iconShape.Name = "CustomIconSet" & cell.Address(0, 0)
iconShape.Line.Visible = msoFalse
iconShape.TextFrame.HorizontalAlignment = xlHAlignCenter
iconShape.TextFrame.VerticalAlignment = xlVAlignCenter
iconShape.Placement = xlMoveAndSize
End If
Next
End Sub2. Then, run the macro by pressing F5 (or click the 
button). When prompted, select the range where you want to display your custom icons. The macro will insert colored icons, using text boxes, according to each cell’s value relative to the group’s percentiles.
- If your data changes, re-run the macro to update or refresh the icons. Users must enable macros for this feature to work. Shapes can be deleted manually or with macro code for re-applying.
- Be aware that running this code will overwrite your values with icons—be sure to back up your data before proceeding.
This VBA approach is extremely versatile—icons, colors, and shapes are fully customizable, making it suitable for expert-level dashboards where brand guidelines or audiences require highly specific visual cues.
In summary, while built-in Excel icon sets have fixed colors, several alternative techniques allow you to apply customized, colorful icons to your spreadsheets. Choose the manual symbol and conditional formatting workaround for complete layout control; harness VBA to place any image or symbol with programmable colors; Each approach has its own advantages and considerations, empowering you to find the best fit for your specific data visualization needs.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in