Excel Conditional Formatting Icon Sets: A Practical Guide

How-to basics · Updated November 22, 2026

To apply an icon set in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Icon Sets and pick a design. Excel compares each cell’s value with rule thresholds and displays a symbol—such as an arrow, traffic light, or flag—to show how that value relates to the others or to set cutoffs.

What Excel icon sets do

Icon sets are a type of conditional formatting: they change how values appear without changing the values themselves. They are useful when a reader needs to spot status or direction quickly, especially in a compact table where adding a separate status column would take up space. A green, yellow, and red symbol, for example, can signal strong, middle, and low results at a glance.

Excel offers several families of icons, including arrows, traffic lights, flags, ratings, shapes, and symbols. The available designs depend on the icon-set rule you select. Most sets contain three icons, while some contain four or five. Excel assigns the icons according to ordered thresholds; the symbols do not automatically understand what a business status means. You decide what values count as high, middle, or low by changing the rule.

An icon set is not a data-validation control and does not prevent someone from entering a value. It is a visual layer over worksheet data. The underlying cell continues to contain its number, date, or formula result, so calculations and sorting still use the cell value rather than the picture.

How to apply an icon set

  1. Select the numeric cells you want to format. Include the data cells, not the heading, unless you deliberately want the heading included in the rule.
  2. On the Home tab, choose Conditional Formatting > Icon Sets.
  3. Choose a set from the gallery. Excel applies a rule to the selected range and displays an icon for each eligible value.
  4. Check the result against the actual numbers. If the default thresholds do not match the meaning of your data, edit the rule rather than assuming the colors or symbols reflect your preferred targets.

To change a rule, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rules list, select the icon-set rule and choose Edit Rule. If you do not see the rule, check the Show formatting rules for setting in the manager; it can display rules for the current selection or the whole worksheet. Confirm the Applies to range as well, since a rule can be correct but attached to the wrong cells.

On Mac, conditional formatting and icon sets are available in desktop Excel, though the dialog layout and some controls can differ from Windows. Excel for the web also has conditional formatting, but its editing interface may not expose every desktop rule option in the same place. If an advanced threshold setting is missing in the browser, open the workbook in the desktop app.

How icon-set thresholds work

An icon set divides values into ordered bands. In a common three-icon rule, the top icon appears for values at or above the upper cutoff, the middle icon appears below that cutoff but at or above the lower cutoff, and the bottom icon appears below the lower cutoff. A four- or five-icon rule adds more bands. The comparison is based on cell values, not on what the number looks like after currency, date, or percentage formatting.

When you edit an icon-set rule, the Type field controls how each threshold is interpreted. Excel commonly offers Number, Percent, Percentile, and Formula, with Lowest Value available for some threshold positions. These choices are not interchangeable:

  • Number uses a fixed numeric cutoff. This is generally the clearest choice when a target is defined, such as a minimum score of 70 or a service level of 0.9.
  • Percent sets a cutoff relative to the range from the lowest to the highest value. It does not mean that a cell must contain a percentage-formatted number.
  • Percentile uses the distribution’s rank. It is useful when the goal is to distinguish the upper or lower portion of the observations, even if the values are clustered together.
  • Formula lets a threshold be calculated from a formula. Use it when a cutoff must depend on a worksheet value rather than a fixed number, and verify the result against representative rows.

For example, a Percent threshold of 67 is not the same as a Number threshold of 67, nor is either automatically the same as a 67th-percentile cutoff. Confusing these settings is a common reason an icon appears inconsistent with a reader’s expectations. If you have a meaningful business target, set the threshold type to Number and enter the target in the scale Excel uses for those cells.

Choosing an icon set for your data

Choose symbols that reinforce the message rather than decorate the table. Arrows can suggest direction or relative performance, but they do not show whether a result is good unless the reader knows what upward or downward means. Traffic lights are familiar for status, while flags or shapes can be useful when color alone is not enough to distinguish categories. Avoid using a set whose visual order conflicts with the story—for instance, an upward arrow for a metric where a larger value is undesirable.

Before choosing a design, consider these factors:

  • Meaning: Decide whether the icons represent fixed status bands, relative rank, or change. A single icon set should not be expected to communicate all three.
  • Number of categories: Three categories are usually easier to scan than five. Add categories only when the extra distinction helps someone make a decision.
  • Audience and display: Small icons can be hard to distinguish on a printed page or a narrow screen. Check the worksheet at the size where people will actually read it.
  • Accessibility: Do not make color the only carrier of meaning. Keep the value visible, use clear headings, and choose distinguishable symbols. A legend or adjacent text label can explain what each category means.
  • Direction of good performance: For measures where lower is better, such as elapsed time or defect counts, reverse the icon order if necessary so the best values receive the intended symbol.

Customize thresholds and display options

In the Edit Formatting Rule dialog, select the icon set and set the Type and Value for each threshold. The order of the thresholds matters: for a typical three-icon rule, the upper cutoff must exceed the lower cutoff if the bands are to make sense. Use values in the same underlying scale as the cells. For example, a stored value of 0.9 displayed as 90% is numerically 0.9, not 90.

Two options are particularly useful. Reverse Icon Order changes which icon is associated with the high and low ends of the scale. Show Icon Only hides the cell values while leaving the icons visible. Hiding values can make a status column compact, but it also removes useful detail and makes auditing harder. Consider keeping the number visible or providing the exact value in a nearby column.

You can also adjust the Applies to range in Manage Rules. If new rows are added later, a rule limited to the original range will not automatically cover every possible new row. Expand the range when needed, or format the data as an Excel table so that the worksheet’s structured data range can grow with added rows. Review the rule after inserting or moving cells, because its range may not match your intended records.

Worked example: rating completion percentages

Suppose cells B2:B7 contain completion rates stored as percentages: 94%, 82%, 70%, 68%, 51%, and 35%. You want green for at least 90%, yellow for at least 70% but less than 90%, and red for anything below 70%. Select B2:B7, apply a three-icon set, and edit its rule. Set both threshold types to Number; enter 0.9 for the upper cutoff and 0.7 for the lower cutoff. Choose or reverse the icons so green is assigned to the highest band.

The resulting groups are 94% in the top band; 82% and 70% in the middle band; and 68%, 51%, and 35% in the bottom band. Notice that a value exactly equal to a cutoff belongs to the higher band because the icon-set comparisons use a greater-than-or-equal boundary. If the business definition instead requires a different boundary, revise the cutoffs or use a different rule design and check the exact-equality cases.

This example illustrates why the displayed percentage and the stored number both matter. Entering 90 as a Number cutoff would mean 90, which is far above a stored value of 0.94. Entering 90% as the threshold may be interpreted as 0.9 by Excel, but using 0.9 explicitly makes the intended scale clear when inspecting the rule.

Common problems and how to fix them

  • Every cell gets the same icon: The thresholds may be too high or too low for the data, or the Type may be Percent or Percentile when you intended a fixed number. Edit the rule and compare its cutoffs with the underlying values.
  • The colors seem backward: Use Reverse Icon Order, or select a different set. Also check whether a high value is actually favorable for this measure.
  • Icons do not appear for some entries: Icon sets are designed to evaluate values. Check whether those cells contain numbers or numeric formula results, rather than text that looks like a number, an empty string, or an unexpected error. Inspect the cell contents and the rule’s Applies to range.
  • New rows have no icons: The rule may not cover those rows. Review its Applies to range in Manage Rules and extend it to include the added data.
  • The rule appears to be ignored: Check for other conditional-formatting rules on the same cells and review their order in Manage Rules. Overlapping rules and their settings can affect the final appearance.
  • Icons are hard to interpret: Keep the values visible, add a short legend, and make sure the icons have a clear relationship to the metric. An icon without a defined threshold is decoration, not a reliable status system.

Another misconception is that an icon set automatically identifies whether a result is good or bad. It does not. Excel applies the rule’s ordered comparisons; a low value receives the lowest-band icon even if low is the desired outcome. The person building the rule must choose suitable thresholds and icon order.

Icon sets compared with other conditional formatting

Icon sets are most effective when the reader needs a quick category marker beside a value. Color scales are better for showing a continuous gradient across a range, such as the spread of scores from low to high. Data bars emphasize magnitude within each cell, making it easy to compare relative lengths. Highlight Cells Rules, such as Greater Than or Between, are more direct when the main task is to mark values meeting a specific condition.

These options can also be combined thoughtfully. A data bar and an icon may communicate different useful information, but multiple visual cues can make a table busy. If users need precise labels such as On track, At risk, and Late, a separate status column with text may be clearer than icons alone. Choose the simplest format that answers the reader’s question, and keep a visible numeric value when the exact measurement matters.

Frequently Asked Questions

Can I use an icon set with text?
Icon sets are intended to classify values, so they work best with numeric cells or formulas that return numbers. For text statuses such as Complete or Delayed, use a text-based conditional formatting rule or a separate status indicator rather than expecting the icon-set thresholds to interpret the words.

Can I show only the icon and hide the number?
Yes. Edit the icon-set rule and select Show Icon Only. The value remains in the cell for calculations, but it is not displayed there. Keep in mind that hiding the number makes exact values less visible to people reading or checking the worksheet.

Can I set my own thresholds?
Yes. Go to Home > Conditional Formatting > Manage Rules, select the rule, choose Edit Rule, and change its Type and Value settings. Number thresholds are appropriate for fixed targets; Percentile is appropriate when categories should reflect rank within the data.

Why does a cell at the threshold receive the higher icon?
Icon-set bands generally use greater-than-or-equal comparisons at their cutoffs. A value equal to a threshold therefore falls into the band beginning at that threshold. Test values exactly on the boundary if that distinction matters to your report.

Can I apply icon sets in Excel for the web?
Excel for the web supports conditional formatting, and available controls can depend on the editing task and interface. For detailed rule adjustments, especially if you cannot find a particular threshold or icon option in the browser, open the workbook in desktop Excel and edit it there.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.