Excel 3-Flag Icon Set: Setup and Thresholds

How-to basics · Updated November 22, 2026

In Excel for Microsoft 365 on Windows, select the cells you want to mark, then choose Home > Conditional Formatting > Icon Sets > 3 Flags. Excel assigns green, yellow, and red flags according to the selected range’s values; use Manage Rules to change the cutoffs or show the flags without the underlying numbers.

What the Three Flags Mean

The 3 Flags icon set is a visual way to classify numeric values into three levels. By default, the highest values receive a green flag, the middle group a yellow flag, and the lowest values a red flag. The icons appear in the cells alongside their values, making it easier to scan a scorecard or status column than to read every number individually.

Conditional formatting does not change the cell’s contents. A cell that displays a red flag still contains its original number, so it remains available for calculations, sorting, filtering, and charts. The flag is an added visual cue, not a replacement value or a separate status field.

This set is useful for ratings, completion scores, service levels, test results, and other measures where three bands are meaningful. It does not decide what green, yellow, or red should mean for your business. You need to choose cutoffs that match the data and explain them to workbook users.

Apply the 3-Flag Icon Set

In the Windows desktop version of Excel, apply the preset this way:

  1. Select the numeric cells to format. Include the data cells, not the heading, unless you deliberately want the heading included in the rule’s range.
  2. On the ribbon, choose Home > Conditional Formatting > Icon Sets.
  3. In the three-icon group, choose 3 Flags. The icons appear immediately using Excel’s default thresholds.
  4. Review the result. If the split does not fit your criteria, choose Home > Conditional Formatting > Manage Rules, select the icon-set rule, and choose Edit Rule.

In Excel for Mac, the corresponding command is also on the Home tab under Conditional Formatting > Icon Sets; the rule-editing dialog may be arranged differently from Windows. Excel for the web supports many conditional-formatting tasks, but its editing options can differ from the desktop dialogs. If an advanced threshold control is unavailable in the browser, open the workbook in the desktop app.

There is no dedicated keyboard shortcut that directly applies the 3 Flags preset. The ribbon path is the most dependable method; Windows Key Tips can help navigate ribbon commands, but their key sequence can depend on the current Excel interface and language.

How Excel Chooses Each Flag

An icon-set rule has two thresholds, creating three bands. Excel compares each numeric value in the applied range with those thresholds and assigns an icon. With the usual descending arrangement, values at or above the upper threshold get the green flag, values below that but at or above the lower threshold get yellow, and values below the lower threshold get red. The rule dialog shows the actual operators and values, so check them rather than relying only on the colors.

The preset normally uses threshold types called Percent, with starting values of 67 and 33. These are not the same as saying that precisely 67 percent of the cells will be green and 33 percent will be red. Percent thresholds are based on the numeric span from the low end to the high end of the selected values, so a skewed range or a large outlier can affect where the bands fall.

For example, imagine a range containing 10, 20, 30, 40, and 100. A percentage-of-range cutoff is influenced by the distance between 10 and 100; it does not simply allocate the top two cells to green, the middle cell to yellow, and the bottom two to red. If you want bands based on ranking within the group, change the threshold type to Percentile. If you need exact business cutoffs, use Number and enter the actual limits.

Thresholds are evaluated against the values in the rule’s applied range. Changing that range, adding new values, or including an outlier can change the flags even though the rule itself has not changed. This is why a flag should not be treated as a permanent label unless the rule uses fixed numeric boundaries appropriate to the data.

Set Explicit Cutoffs

To make the colors correspond to known targets, edit the rule instead of accepting the preset. Open Home > Conditional Formatting > Manage Rules, set the display to show rules for the relevant worksheet if needed, select the 3 Flags rule, and choose Edit Rule. In the dialog, set each threshold’s type to Number and enter the limits you want.

For example, for a score where 80 or above is on target, 60 through 79 is a warning, and anything below 60 needs attention, set the upper threshold to 80 and the lower threshold to 60. Keep the icon order green, yellow, red. Check the comparison operators shown in the dialog: the upper row commonly uses a greater-than-or-equal comparison, and the next row uses a greater-than-or-equal comparison for the lower limit. This makes 80 green, 60 through 79 yellow, and values below 60 red.

Use the exact threshold type that matches your intention:

  • Number: Use for fixed limits such as 80 points, 60 days, or 1,000 units. It is usually easiest to explain and audit.
  • Percent: Use when the cutoffs should move in relation to the range of values being evaluated.
  • Percentile: Use when you want cutoffs tied to the relative position of values in the distribution rather than fixed units.
  • Formula: Use when a threshold must be calculated from a worksheet formula. Confirm the references and rule range carefully, since a misplaced reference can produce unexpected icons.

A percentile-based split does not guarantee perfectly equal groups when values are tied or when the sample is small. Likewise, fixed numeric cutoffs do not automatically adapt when targets change. Pick the behavior deliberately and document what the flags signify near the table or in a note.

Control the Display and Rule Range

The Edit Formatting Rule dialog includes options for adjusting the icons. Reverse Icon Order reverses which end of the scale gets each icon. This can be useful when a larger number represents a worse outcome, such as days overdue or defects, rather than better performance. Always verify the resulting meaning: in that case, you may want red for high values and green for low values.

Show Icon Only hides the cell value while leaving the icon visible. This can make a compact dashboard easier to scan, but the numbers still exist in the cells. Users can select a cell and inspect its value in the formula bar, and formulas elsewhere can still refer to it. If people need to understand why a flag appeared, showing both icon and value is often clearer.

In Manage Rules, check Applies to. The range should cover the intended data rows and no unrelated cells. If the rule should cover a whole column of future entries, adjust the range with care; applying it to an unnecessarily large area can make the sheet harder to manage. If multiple conditional-formatting rules apply to the same cells, review their priority and any Stop If True settings because other rules can affect what appears.

Practical Example: A Three-Level Scorecard

Suppose a team tracks a quality score from 0 to 100 in column C, with one score per person. Select only the score cells, such as C2:C31, and apply the 3 Flags preset. For a simple relative overview, the default thresholds may be adequate: the flags show how the scores compare within that selected range. That can help identify the low and high ends without implying that any score meets a formal target.

If the organization has defined standards of 80 and 60, edit the rule and use those as numeric thresholds. Now each flag has a stable interpretation: green meets or exceeds 80, yellow is at least 60 but below 80, and red is below 60. A score of exactly 80 should be green and a score of exactly 60 should be yellow if the dialog’s operators include the boundary values. Confirm the displayed operators to avoid ambiguity at the cutoffs.

Keep the column heading descriptive, such as Quality score, and add a nearby note such as Green: 80+, Yellow: 60–79, Red: below 60. That context matters when the workbook is shared, especially if users might assume the preset divides the data into fixed proportions. If scores are later added, check that the Applies to range includes them and decide whether the fixed standards or a relative comparison should govern the new rows.

Common Problems and Misconceptions

  • Every value has the same flag: The values may fall into one band under the current thresholds, or the selected range may include an outlier. Edit the rule and inspect the type, values, and applied range.
  • The icons do not represent equal groups: The default Percent thresholds are not a promise of equal counts. Use Percentile for rank-based boundaries, while remembering that ties can still make group sizes uneven.
  • A high number gets a green flag even though high is bad: Reverse the icon order or choose icons that fit the meaning, then test with known high and low examples.
  • The heading receives an icon or affects the result: The rule may include the heading cell. Edit Applies to so the rule covers the intended data only.
  • Some cells have no useful icon: Icon sets are intended for numeric comparisons. Text labels and blank cells do not behave like ordinary numeric scores, so check for imported numbers stored as text, blanks, and unexpected values.

A frequent misconception is that changing a cell’s displayed number format changes the icon thresholds. Number formats control how values appear, such as showing a decimal as a percentage; the underlying numeric value is what the conditional-formatting rule evaluates. If a score is stored as 0.8 but displayed as 80%, a fixed threshold of 80 is not equivalent to a threshold of 0.8.

When to Use a Different Visual

Three flags work best when the reader needs a quick category, not a precise magnitude. For a trend across time, a line chart may communicate direction better. For comparing values across a broad range, data bars show relative size more continuously. A color scale can show gradual differences, while a simple text status such as On track, Watch, or Behind can be more accessible when the category itself matters more than the number.

Icons also rely on visual interpretation, so do not make the flag the only way to identify an important status. Keep the number visible when it is useful, use clear labels, and consider accessibility for readers who may not distinguish colors easily. A flag shape is an additional cue, but a written legend and meaningful thresholds make the workbook clearer for everyone.

Frequently Asked Questions

Can I use the 3 Flags icon set with text?
Icon sets are designed to compare values, typically numbers. For text categories such as High, Medium, and Low, use a text-based conditional-formatting rule or add a separate status column rather than expecting the icon set to rank the words.

Can I change the flag colors?
The built-in icon set offers preset icons and options such as reversing their order, but it is not a general color editor for each flag. If the preset does not fit, choose another icon set or build a different visual approach using conditional-formatting rules that meet your version’s available options.

How do I show only the flags and hide the numbers?
Edit the icon-set rule through Home > Conditional Formatting > Manage Rules > Edit Rule, then select Show Icon Only. The cell values remain in the worksheet and can still be used in calculations.

Why does the same score get a different flag after I add data?
If the rule uses Percent or Percentile thresholds, adding values can change the distribution or range used to determine the cutoffs. Use Number thresholds for fixed standards, and confirm that the new cells are included in the intended rule range.

Can I use a 3-flag rule for negative numbers?
Yes. Excel compares the numeric values with the rule’s thresholds, and those values can be negative. Choose limits that make sense for the measure and test representative negative, zero, and positive values to confirm the icons communicate the intended status.

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.