Excel Conditional Formatting Icon Sets Explained

Conditional formatting icon sets are Excel rules that place symbols—such as arrows, traffic lights, or flags—beside cell values to show how those values compare with thresholds. In Excel for Microsoft 365 on Windows, select the cells and choose Home > Conditional Formatting > Icon Sets; Excel assigns an icon to each value automatically.

What icon sets show

An icon set adds a visual category to a cell without changing its stored value. A green arrow might indicate a high result, a yellow arrow a middle result, and a red arrow a low one. The cell still contains its original number, so it can continue to be used in calculations, charts, sorting, and other workbook operations.

Excel includes sets with different numbers and styles of symbols. Common choices include directional arrows, traffic lights, circles, flags, and rating-style symbols. Three-icon sets divide values into three bands; four- and five-icon sets provide more bands. The symbols are not a universal grade: a green icon does not inherently mean good, nor does a red one always mean bad. Their meaning depends on the thresholds and the context of the data.

Icon sets are useful when a reader needs to scan a table quickly. For example, a project tracker could use arrows to distinguish tasks that are ahead of schedule, near their target, or behind. A sales report could use traffic lights to flag performance bands. They work best when a symbol adds information that might be harder to spot in a column of numbers alone.

How Excel assigns the icons

Excel compares each cell’s value with the rule’s threshold values, then displays the icon for the band that contains it. In a typical three-icon rule, the highest band gets the top icon, the next band gets the middle icon, and values below the lower threshold get the bottom icon. Boundary comparisons use the threshold settings shown in the rule editor, so check those settings rather than assuming that a symbol represents a particular business target.

When you first apply a set, Excel creates thresholds automatically. A common default for a three-icon set uses percentage-based cutoffs, often around 67 and 33. This means the initial result is based on the range or distribution of the selected values, not necessarily on a meaningful target such as a sales goal or a passing score. If the data changes, the icons can change too, even when a particular value stays the same.

The rule applies to the selected range as a group. If one cell contains 100 and another contains 10,000, the range of values can affect the automatically chosen bands. An icon set does not automatically compare each value with a different target in the same row, such as actual sales against that row’s own budget. For that kind of analysis, choose thresholds that suit the data or use another conditional-formatting approach.

Apply an icon set

To add a basic rule in Excel for Microsoft 365 on Windows:

Select the numeric cells you want to format. Include the full range that should share the rule, but avoid unrelated values that would distort its thresholds.
Choose Home > Conditional Formatting > Icon Sets.
Choose a set from the gallery. Excel immediately applies a rule and displays icons in the selected cells.
Review the result against the actual values. If the categories do not reflect what the icons should communicate, edit the rule instead of relying on the automatic cutoffs.

To inspect or change the 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 several rules appear, check the Show formatting rules for setting so you are viewing the relevant worksheet or selection. The dialog’s labels and layout can vary slightly between Excel editions.

Excel for Mac has the same general conditional-formatting feature, though ribbon and dialog layouts can differ. Excel for the web also offers conditional formatting, but advanced rule editing can be more limited or arranged differently from the Windows desktop version. If you cannot find a particular threshold control in the web interface, open the workbook in desktop Excel to edit the rule.

Understand the threshold types

In Edit Formatting Rule, each icon boundary has a Type and a Value. The available choices determine how Excel interprets that value. The main types are:

Number: Uses a fixed numeric cutoff. This is usually the clearest choice for a specific target, such as marking values of 10 or higher with the top icon.
Percent: Uses a percentage of the range from the lowest to the highest value. It does not mean that a fixed percentage of cells will receive that icon.
Percentile: Uses a value’s position in the distribution. A percentile cutoff is about relative rank, so the number of cells in each band can depend on the data and on ties.
Formula: Uses a formula to supply a threshold. This can support rules that depend on worksheet values, but it requires careful setup and testing in the rule editor.

Percent and Percentile are easy to confuse. For instance, a 70 percent threshold under Percent is tied to the numeric span between the minimum and maximum; it is not simply the 70th percentile of the observations. If your question is whether a value meets a fixed requirement, Number is generally easier to explain. If the goal is to show relative standing within a group, a percentile-based threshold may be more appropriate.

Choose thresholds that fit the question

Start by deciding what each icon should mean, then choose the threshold type and values to match. A report that marks whether a measurement meets a known standard should use that standard as a numeric boundary. A ranking view may instead use percentile cutoffs. Avoid choosing a symbol set first and treating its default categories as a definition of good or poor performance.

Consider a worksheet with weekly percentage changes in column B. If the goal is to mark gains of at least 10 percent as high, results from 0 up to but below 10 percent as middle, and losses as low, use numeric thresholds of 10 and 0 for a three-icon rule. In the rule editor, set both boundary types to Number and use the appropriate icon order. A value of 12 percent then falls in the top band, 5 percent in the middle band, and -3 percent in the bottom band. The example uses percentage-point values as stored in Excel; a cell displaying 12% typically stores 0.12, so the numeric threshold must match the underlying value scale. That distinction prevents a common mistake: entering 10 when the cells contain decimal percentages would mean 1,000 percent, not 10 percent.

Also think about whether the data can be negative, whether zero has a special meaning, and whether blank or exceptional values should be included. A threshold that works for one month may become misleading after the data range expands. Documenting the meaning of each icon in a nearby heading or note helps readers interpret the symbols consistently.

Customize the icons and their display

The Edit Formatting Rule dialog lets you change the boundary types and values and, depending on the set, choose different icons for the categories. You can reverse the icon order when the default direction does not match the meaning of the data. For example, lower delivery times might be better, so a rule whose visual progression implies that higher is better may need to be reversed or redesigned.

The Show Icon Only option hides the cell’s number while leaving the icon visible. This can make a compact status column easier to scan, but it also removes the value from view. Use it only when readers can still access the number when needed—for example, in a neighboring column, a formula bar, or a clearly documented detail view. Hiding values can make a table less useful for checking exact results.

After changing a rule, inspect the full range rather than just one cell. Look at values exactly on each boundary, values just above and below it, and the minimum and maximum. This is a simple way to catch an accidental threshold, reversed order, or unsuitable number scale before sharing the workbook.

Icon sets compared with other formatting

Icon sets are one of several conditional-formatting tools, and they answer a different visual question from color scales and data bars. A color scale shades cells according to their relative values, which can reveal patterns across a table but may be less precise for categories. Data bars show approximate magnitude inside each cell, making them useful for comparing sizes. Icon sets classify values into discrete bands and can be read as status categories.

For example, a list of monthly sales might use data bars when the reader needs to compare the size of each total. A three-icon rule may be clearer when the question is simply whether each result is below, near, or above a target. You can combine different conditional-formatting rules, but overlapping rules and visual clutter can make a worksheet harder to interpret. Prefer the smallest number of visual cues that answer the reader’s question.

Common problems and practical checks

If the icons look wrong, first check what the cells actually contain. A displayed value may be rounded, formatted as a percentage, or stored as text. The rule evaluates the underlying value, not just the appearance created by the number format. For example, a cell displaying 10% may contain 0.1, so a numeric cutoff of 10 would not mean 10 percent.

Next, check the rule’s Applies to range and the selected thresholds. An unintended extra row can change automatic thresholds; an incomplete range can leave new rows without icons. Use Manage Rules to confirm which cells the rule covers. If multiple conditional-formatting rules apply to the same cells, review their order and settings, because overlapping formats can make the final presentation confusing.

All cells show one icon: Check whether the values fall on the same side of a threshold, whether the thresholds use the correct scale, and whether numbers are stored as numbers.
Icons change after adding data: Automatic or relative cutoffs may be recalculated for the expanded range. Set fixed numeric boundaries if the categories should stay tied to fixed targets.
A new row has no icon: Confirm that the rule’s Applies to range includes it, or extend the range deliberately.
The colors or directions suggest the wrong result: Edit the icon choices or reverse their order, and label the intended meaning in the worksheet.

Finally, do not rely on color alone. Some readers may have difficulty distinguishing colors, and icons can be hard to interpret when viewed at small sizes or printed in grayscale. Use recognizable shapes, explain the categories, and retain text or numbers where the status needs to be understood precisely.

Frequently Asked Questions

Can I change the icon set thresholds in Excel?
Yes. Select a cell in the rule, open Home > Conditional Formatting > Manage Rules, choose the rule, and select Edit Rule. Change the threshold types and values there.

Do Excel icon sets compare values with the whole selected range?
They apply a rule to the chosen range, and automatic percentage-based thresholds depend on that range’s values. A basic icon-set rule does not automatically compare each row’s value with a separate target in another column.

Can I show only the icon and hide the number?
Yes. Open Edit Formatting Rule and select Show Icon Only. The value remains in the cell, but it is no longer displayed there, so make sure users have another way to see exact numbers when required.

Why do percentage values get the wrong icons?
Excel stores percentages as decimal values: 10% is usually stored as 0.1. If the rule uses Number, set its threshold on the same scale as the stored values, not the rounded or formatted display.

Can I use icon sets in Excel for the web?
Excel for the web supports conditional formatting, but some advanced editing controls may differ from the Windows desktop app. If a rule option is unavailable in the browser, edit the workbook in desktop Excel.

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.