Excel Conditional Formatting Icons: How to Use Them

How-to basics · Updated November 22, 2026

In Excel for Microsoft 365 on Windows, conditional formatting icons add a symbol to each cell based on its value: select the cells, then choose Home > Conditional Formatting > Icon Sets. You can keep the values visible or choose to show icons only, and you can adjust the thresholds that determine which icon appears.

What conditional formatting icons do

Icon sets are a type of conditional formatting: Excel compares each cell’s value with rule thresholds and displays a corresponding icon. Common sets use traffic lights, arrows, flags, circles, or rating bars to signal relative performance. The icon is an extra visual cue, not a replacement for the underlying value unless you specifically choose to hide the value with the Show Icon Only setting.

For example, a sales report might use green, yellow, and red indicators to distinguish strong, middle-range, and weak results. A project tracker could use arrows to indicate whether a metric is high or low. Because the value remains in the cell by default, a reader can see both the exact number and its visual category.

Icon sets work best when a quick visual scan is useful and the distinctions are meaningful. They are less useful when a sheet is crowded with symbols, when values are not comparable, or when a reader needs precise explanations that the icons cannot convey on their own.

How to add an icon set in Excel

To apply a standard icon set in Excel for Microsoft 365 on Windows:

  1. Select the numeric cells to format. Include the first data cell and the rest of the intended range, but usually exclude the column heading.
  2. Choose Home > Conditional Formatting > Icon Sets.
  3. Pick a set from the gallery. Excel applies a rule immediately, using default thresholds based on the selected values.
  4. Review the result, then adjust the rule if the default divisions do not match what the icons should mean.

To change an applied rule, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, select the relevant rule and choose Edit Rule. Depending on the dialog layout, you may need to set “Show formatting rules for” to the current selection or the worksheet to find it. In the Edit Formatting Rule dialog, choose the icon set and configure its thresholds and display options.

On Excel for Mac, icon sets are also available from the Home tab’s Conditional Formatting menu, though dialog labels and layout can differ from Windows. Excel for the web has conditional-formatting options too, but the available rule-editing controls can vary; use desktop Excel if you need the full set of customization choices.

How Excel decides which icon to show

A three-icon rule has two threshold values that divide the data into three groups. With a rule configured to use Number thresholds of 80 and 50, the top icon appears for values of 80 or higher, the middle icon appears for values of at least 50 but below 80, and the bottom icon appears below 50. The exact icon-to-meaning relationship depends on the selected icon set and whether its order has been reversed.

By default, Excel commonly sets icon thresholds using percentiles of the selected values. That makes the categories relative to the range’s distribution: a top icon may identify the upper portion of the data even if every value is low in absolute terms. For example, if all sales figures are disappointing, a percentile-based rule can still assign the best icon to some of them. That is not a calculation error; it is a relative comparison.

Threshold type matters. Percent thresholds divide the span between the minimum and maximum values, while Percentile thresholds use positions within the distribution. Number thresholds use explicit numeric cutoffs, which are often easier to interpret for goals such as a pass mark or service limit. Select the type deliberately rather than assuming a displayed percentage means a literal percentage score.

For example, if the goal is to mark a response rate of 80% or more as green, 50% to below 80% as yellow, and below 50% as red, use Number thresholds of 0.8 and 0.5 if the cells contain decimal percentages. If the cells contain whole numbers such as 80 and 50, enter 80 and 50 instead. Excel stores a percentage formatted as 80% as 0.8, so matching the threshold scale to the cell values is essential.

Choosing icons and setting useful thresholds

Choose a set whose direction is intuitive for the data. Up arrows often suggest improvement or a high result; traffic lights suggest status; rating symbols can indicate levels. But “high” is not always “good.” A high defect count or overdue balance should not automatically receive a positive-looking icon. Pick icons and thresholds that communicate the intended interpretation rather than simply decorating the cells.

When editing an icon rule, Excel lets you choose the icon set, change the threshold type and values, reverse the icon order, and select Show Icon Only. Some sets offer three, four, or five icons, so a larger set can distinguish more bands but may be harder to scan. For each boundary, choose a type such as Number, Percent, or Percentile where available, then enter a value suited to the data.

  • Use Number thresholds for fixed targets, limits, or service standards.
  • Use Percentile thresholds when the aim is to compare values by rank within the selected group.
  • Use Percent thresholds when dividing the interval between the group’s lowest and highest values is appropriate.
  • Reverse icon order when the default visual direction does not match the meaning of the metric.
  • Use Show Icon Only when the number is redundant or displayed elsewhere; otherwise, keeping the value visible helps readers verify what the symbol represents.

Make the legend or explanation clear if other people will use the workbook. A colored circle alone does not tell a reader whether green means “meets target,” “above average,” or “urgent.” For important reports, label the column or add a brief note describing the thresholds and their meaning.

Applying icons to a practical example

Suppose a worksheet has a “Completion rate” column with values from 0% to 100%, and the intended status bands are at least 90% for green, at least 70% for yellow, and below 70% for red. Select the rate cells, apply a three-icon set, open Edit Rule, and choose Number for both thresholds. If the worksheet stores the rates as percentages, enter 0.9 and 0.7; if it stores whole-number values from 0 to 100, enter 90 and 70.

Then check the boundary values: 90% should receive the top icon, 70% should receive the middle icon, and a value just below 70% should receive the lowest icon. This simple check catches a common setup mistake: entering 90 and 70 as thresholds when the underlying values are stored as 0.9 and 0.7. The cells may otherwise all fall into the same category or show results that seem inexplicable.

If the workbook includes new rows later, make sure the rule’s Applies to range includes them. You can inspect or edit that range through Home > Conditional Formatting > Manage Rules. A rule applied only to the original rows will not automatically format data entered outside its range.

Common problems and how to fix them

  • Every cell has the same icon: Check the threshold type and scale. Decimal percentages, whole-number percentages, and ordinary counts require different threshold values. Also confirm that the selected values actually span the thresholds.
  • The icons look backward: Open Edit Rule and check Reverse Icon Order. Also reconsider whether a high value is truly favorable for this measure.
  • The results change when values are added: Percentile-based thresholds depend on the distribution of the values in the rule’s range. Use fixed Number thresholds if categories should remain tied to stable targets.
  • Some cells show no useful icon: Icon sets are designed to evaluate numeric values. Text labels such as “High” and “Low” are not numeric measurements; use numeric helper values or a different conditional-formatting rule when the source data is text.
  • New rows are unformatted: Check the rule’s Applies to range in Manage Rules and extend it to cover the additional cells.
  • Icons and other formats conflict: Review all rules affecting the range in Manage Rules. Multiple conditional-formatting rules can apply to the same cells, so their order and settings may affect the final appearance.

Another frequent misunderstanding is that a green icon necessarily means a cell meets a fixed target. With default percentile settings, it may only mean the value is in a higher portion of the selected data. If the workbook needs a fixed business rule, set Number thresholds to the intended cutoffs and verify them against representative values.

Icon sets compared with color scales and data bars

Icon sets, color scales, and data bars are all conditional-formatting options, but they answer different visual questions. An icon set assigns a category symbol, such as high, middle, or low. A color scale shades cells along a gradient, making relative differences visible. A data bar fills part of each cell to show magnitude relative to other values or a configured scale.

Use icons when readers need to categorize or scan for status. Use a color scale when the shape of a distribution matters and there is no need to assign discrete bands. Use data bars when comparing magnitudes within one column is the priority. These formats can be combined in some cases, but too many visual signals can make a sheet harder to understand; choose the smallest number of cues that answers the reader’s question.

Icons also differ from manually typing symbols into cells. A conditional-formatting icon responds to changing values under a rule, whereas a typed symbol remains until someone edits it. The conditional approach is usually more suitable for live reports, but it depends on correct thresholds and a correctly scoped range.

Accessibility and readable spreadsheet design

Do not rely on color alone to communicate status. Some readers have color-vision deficiencies, and icons can be difficult to distinguish at small sizes or on certain displays. Choose recognizable shapes, keep the underlying values visible when practical, and include text labels or a written explanation for important categories. A symbol should reinforce information, not be the only way to understand it.

Check the sheet at its normal viewing size and consider printing or exporting it if those are common uses. A set that looks clear on a large monitor may be cramped in a narrow column or indistinct in a printout. Consistent meanings across worksheets also help: if green means “on target” in one table, avoid using it to mean “needs attention” in another without a clear explanation.

Frequently Asked Questions

Can I show only the icon and hide the number?
Yes. Edit the icon-set rule and select Show Icon Only. The cell’s underlying value remains in the worksheet; the option changes how it is displayed, not the stored data.

Can I use my own icons in an icon set?
Excel’s built-in icon-set rule offers its available symbol sets; it is not a general picker for inserting any image or custom icon. If you need custom symbols, consider a formula-driven text symbol or another visual approach, while checking that it remains clear and accessible.

Why do my percentages show the wrong icons?
Excel stores a percentage such as 75% as 0.75. If cells use percentage formatting, Number thresholds generally need decimal values such as 0.9 and 0.7 for 90% and 70%. If the cells store 75 as a whole number, use whole-number thresholds instead.

Do icon sets update when the cell values change?
Yes. Conditional formatting recalculates the icon according to the rule when the underlying value changes. If the rule uses percentile thresholds, adding or changing other values in the applied range can also change which icon a value receives.

Can an icon set flag text such as “Complete” or “Late”?
Icon sets are intended for numeric comparisons, so text entries do not behave like numeric scores. You can use numeric helper values to represent the categories, or use a separate conditional-formatting rule that applies formatting based on text.

How do I remove an icon set?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. Be careful: this removes conditional-formatting rules for the selection, not just the icons, if other rules are also applied 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.