You cannot directly type a formula into the icon set rule dialog box. Instead, you use a formula to create a new conditional formatting rule that applies an icon set, or you use a formula to control which cells get an icon set by combining it with a helper column. The most direct method is: select your data range, go to Home > Conditional Formatting > New Rule, choose “Use a formula to determine which cells to format,” enter a formula that returns TRUE for cells you want icons in, then click Format and select the Icon Sets tab under Number Format.
What Icon Sets Are and Why Formulas Matter
Icon sets are one of the four built-in conditional formatting categories in Excel, alongside data bars, color scales, and highlight rules. They display small graphical icons—arrows, traffic lights, flags, ratings—inside or next to cells based on the cell’s value relative to other selected cells or fixed thresholds. The built-in icon set rules let you choose a style (like 3 Arrows or 5 Quarters) and set thresholds as percentages, numbers, or formulas. However, the standard interface only lets you type a number or percent directly into the threshold boxes; it does not accept a formula like =A1>100 in those boxes. That limitation is why users search for a “formula” approach—they want dynamic thresholds or conditional icon display based on other cells.
How the Built-In Icon Set Rule Works
When you apply an icon set from Home > Conditional Formatting > Icon Sets, Excel creates a rule that evaluates each cell in the selected range. For a 3-icon set (e.g., green up arrow, yellow sideways, red down), the default thresholds are: green when value >= 67th percentile, yellow when >= 33rd percentile, red otherwise. You can edit the rule by going to Home > Conditional Formatting > Manage Rules, selecting the rule, and clicking Edit Rule. In the Edit Formatting Rule dialog, you can change the “Type” from Percent to Number, Percentile, or Formula. If you choose Formula, you must enter a formula that returns a numeric threshold—for example, =AVERAGE($A$1:$A$10)—and that threshold applies to all cells in the range. But you cannot write a per-cell formula like =IF(A1>B1,1,0) directly in that box; the formula must return a single number, not a condition.
Using a Helper Column with a Formula to Apply Icons
The most flexible way to use a formula with icon sets is to create a helper column that converts your condition into a number, then apply an icon set to that helper column. For example, suppose you have sales targets in column B and actual sales in column C, with data in rows 2 to 20. In column D (helper), enter this formula in D2 and copy down: =IF(C2>=B2,3,IF(C2>=B2*0.9,2,1)). This returns 3 for met target, 2 for within 90% of target, 1 for below. Then select D2:D20, go to Home > Conditional Formatting > Icon Sets > 3 Symbols (Circled). Edit the rule so that green shows when value >= 3, yellow when >= 2, red otherwise (set Type to Number). The icons appear in column D, but you can hide column D or use it as a visual indicator. To show icons in the original column C, you would need a different approach—either use the built-in rule with thresholds based on a formula in the “Value” box (but that only allows a single formula), or use a custom number format with icons, which is limited.
Creating a Formula-Based Rule That Applies an Icon Set to Specific Cells
Another method is to use a formula to conditionally format cells with an icon set, but this only works if you apply the icon set to a range and then use a separate rule to hide icons for non-matching cells—a trick using a white font and a custom number format. A cleaner approach: use the “Use a formula to determine which cells to format” rule to apply a fill color, not an icon, because icon sets cannot be applied through the Format button in that dialog. The Format button only allows font, border, and fill—not icon sets. So if you want icons only on rows where a condition is true, you must either apply the icon set to the whole range and then use a formula-based rule to set the font color to match the background (hiding the icon) for false rows, or use a helper column as described above.
Common Problems and How to Fix Them
- Icon set doesn’t update when source data changes: If you used a formula in the threshold box (e.g.,
=AVERAGE(A1:A10)), Excel recalculates automatically. If it doesn’t, press F9 to recalculate or check that calculation is set to Automatic (Formulas > Calculation Options). - Icons appear for empty cells: By default, icon sets treat empty cells as 0 and may show an icon. To avoid this, edit the rule and check “Stop If True” on a preceding rule that formats empty cells with no fill, or use a helper column that returns a blank for empty cells:
=IF(C2="","",IF(C2>=B2,3,...)). - Threshold formulas return errors: If your formula references a range that includes text, use
IFERRORto return a safe number, e.g.,=IFERROR(AVERAGE(A1:A10),0). - Icons are reversed (green for low values): In the Edit Rule dialog, you can reverse the icon order by clicking the “Reverse Icon Order” button.
- Formula in threshold box not accepted: As noted, the threshold box only accepts a single numeric formula, not a per-cell condition. Use a helper column instead.
Comparing Icon Sets with Data Bars and Color Scales
Icon sets are best for categorical status (pass/fail, up/down), while data bars show magnitude proportionally and color scales show gradient intensity. If you need to combine a formula condition with a visual bar, you can use a formula to create a helper value and apply a data bar to that helper. For example, =IF(C2>=B2,1,0) gives a bar that is full or empty. Icon sets are more readable when you have discrete states. A common misconception is that icon sets can be based on text values directly—they cannot; you must convert text to numbers via a formula like =IF(A1="Yes",1,0).
Performance and File Size Considerations
Using multiple icon set rules with complex formulas can slow down large workbooks. Each conditional formatting rule is recalculated on every change. To improve performance, limit the range to the actual data (e.g., A2:A1000 instead of entire columns), avoid volatile functions like INDIRECT or OFFSET in thresholds, and use helper columns with static formulas instead of array formulas. Also, when you copy a range with icon sets, the rules copy along—ensure the “Applies to” range is correct after pasting.
Frequently Asked Questions
Can I use an IF formula directly in the icon set rule?
No. The icon set rule’s threshold boxes accept a constant or a single formula that returns a number, but not a per-cell IF condition. To apply an icon based on a condition, use a helper column that returns a number (e.g., 1, 2, 3) and then apply an icon set to that helper column.
How do I make icon thresholds dynamic based on other cells?
In the Edit Formatting Rule dialog, set the Type to “Formula” and enter a formula that returns a single number, like =MAX($B$2:$B$20)*0.8. This threshold will update when the source data changes. Remember to use absolute references if you want the same threshold for all cells.
Why do my icons show for blank cells?
Excel treats blank cells as 0. To prevent icons on blanks, add a separate conditional formatting rule that formats blank cells with no fill and check “Stop If True” before the icon set rule, or modify your helper formula to return a blank string for empty cells, but note that icon sets only work with numeric values—so you might need to use a number like 0 and then hide it with a custom format.
Can I apply an icon set to a cell based on another cell’s value?
Yes, using a helper column. For example, if A1 is the condition and you want an icon in B1, put a formula in B1 like =IF(A1>100,1,0) and apply an icon set to B1. The icon will reflect the condition in A1.
Is there a way to use icon sets in Excel for the web?
Yes, Excel for the web supports icon sets, but the Edit Rule dialog is more limited. You can apply preset icon sets and change thresholds using numbers or percentages, but the “Formula” type may not be available. In that case, use a helper column with a formula to compute a score, then apply an icon set to that column.
How do I remove only the icons but keep the cell formatting?
Select the range, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes all conditional formatting, including icons. To remove only the icon set rule while keeping other rules, use Manage Rules, select the icon set rule, and click Delete Rule.
Related
- Java String.join(): How It Works and When to Use It
- C++ String Count: How to Count Characters
- Oracle String to Value: Converting Text in SQL
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Power Query Filter Rows: Practical Steps and ExamplesTo filter rows in Excel Power Query, open a query in the Power Query Editor, select the filter…
- XLOOKUP Multiple Criteria: Examples and Common FixesTo use XLOOKUP with multiple criteria in Excel, multiply the criteria tests and look for 1: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not found").…
- COUNTIF in Excel: How It Works and ExamplesIn Excel, COUNTIF counts cells in a range that meet one condition. Its syntax is =COUNTIF(range, criteria); for…
- DAX SUMPRODUCT: Multiply and Sum Values in Power BIDAX does not have a built-in SUMPRODUCT function. To multiply values row by row and add the results,…
- Excel LEFT Function: How to Extract Text from the StartTo return characters from the beginning of text in Excel, enter =LEFT(A2,3) in a cell, replacing A2 with…
- Excel Formula List: Essential Functions and ExamplesAn Excel formula list is a reference to the calculations and functions you can use in a worksheet.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.