Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Add Icon Sets to Excel Cells with openpyxl

Add traffic lights, arrows, flags and rating icons to an Excel report with openpyxl's IconSetRule — choose the icon set, set thresholds as numbers, percents, percentiles or cells the reader can edit, control inclusive boundaries, show icons alone, and drive status icons from a calculated column.
Conditional formattingopenpyxl

A column of percentages tells a reader little at a glance; the same column with a green, amber or red light beside each value tells them where to look. Excel's icon sets are conditional formatting rules, so they stay live: when the numbers change, the icons follow. openpyxl writes them with IconSetRule. This guide, part of Applying Conditional Formatting with openpyxl, covers how icon thresholds really work — the part that makes icons appear in the wrong colour when misunderstood — and how to keep targets editable by the people who read the report.

Store attainment with traffic lights driven by target cells Five stores have attainment of 95, 80, 62, 40 and 110 percent. With amber from 70 percent and green from 90 percent, stored in cells D2 and E2, Leeds and Derby show green lights, York amber, and Hull and Bath red. A B C D E 1 Store Attainment Amber from Green from 2 Leeds ● 95% 70% 90% 3 York ● 80% 4 Hull ● 62% 5 Bath ● 40% 6 Derby ● 110% the icon rule reads its thresholds from D2 and E2

Prerequisites

Bash
pip install openpyxl

A small attainment report, with the targets in their own cells:

Python
from openpyxl import Workbook, load_workbook
from openpyxl.formatting.rule import IconSetRule

wb = Workbook()
ws = wb.active
ws.title = "Attainment"
ws.append(["Store", "Attainment", "", "Amber from", "Green from"])
for store, value in [("Leeds", 0.95), ("York", 0.80), ("Hull", 0.62), ("Bath", 0.40), ("Derby", 1.10)]:
    ws.append([store, value])
ws["D2"], ws["E2"] = 0.70, 0.90
for cell in ws["B"][1:]:
    cell.number_format = "0%"
for col in "DE":
    ws[f"{col}2"].number_format = "0%"
ws.column_dimensions["B"].width = 14

Step 1 — Pick an icon set

IconSetRule takes the name of one of Excel's built-in sets. The number in the name is the number of icons, and it must match the number of thresholds you pass:

Python
ICON_SETS = {
    3: ["3Arrows", "3ArrowsGray", "3Flags", "3TrafficLights1", "3TrafficLights2", "3Signs", "3Symbols", "3Symbols2"],
    4: ["4Arrows", "4ArrowsGray", "4RedToBlack", "4Rating", "4TrafficLights"],
    5: ["5Arrows", "5ArrowsGray", "5Quarters", "5Rating"],
}
print(sum(len(v) for v in ICON_SETS.values()), "built-in sets")      # 17 built-in sets

Traffic lights suit status against a target, arrows suit change (up, flat, down), and the rating and quarter sets suit a score. Pick the set that matches the question the column answers; three icons are easier to read than five. The newer sets Excel added later — stars, triangles, boxes — are stored in an extension that openpyxl does not write, so stay with the list above.

Step 2 — Thresholds are lower bounds

Each threshold is the lower bound of an icon's band, listed from the lowest icon (red) to the highest (green). The first value is the floor for the first icon and should be at or below the smallest possible value:

Python
rule = IconSetRule("3TrafficLights1", "num", [0, 0.70, 0.90])
print([(c.type, c.val) for c in rule.iconSet.cfvo])
# [('num', 0.0), ('num', 0.7), ('num', 0.9)]

Applied to the attainment column, values from 0.9 upwards get green, from 0.7 amber, and everything else red. By default each boundary is inclusive, so exactly 90% is green. The rule writes a cell's icon from its own value, which is why B2:B6 must hold the numbers being judged, not labels.

Step 3 — num, percent or percentile

The second argument decides what the thresholds mean:

Python
relative = IconSetRule("3Arrows", "percent", [0, 33, 67])
ranked = IconSetRule("4Rating", "percentile", [0, 25, 50, 75])
print(relative.iconSet.cfvo[1].type, ranked.iconSet.cfvo[3].val)     # percent 75.0

With num, a value always gets the same icon. With percent, the thresholds are positions between the range's minimum and maximum, so the icons describe where a value sits relative to the others — and change when one outlier arrives. With percentile, they split the values into groups of roughly equal size. For performance against a fixed target, num is almost always right; the relative types suit "how does this compare with the rest" displays, where a colour scale is often clearer anyway — see Add Data Bars and Colour Scales with openpyxl.

What each threshold type compares against num compares each value with fixed numbers, so the same value always gets the same icon. percent places thresholds between the minimum and maximum of the range. percentile splits the values into groups of equal size. formula-style cell references let readers change the thresholds in the sheet. Threshold means Stable when data changes num a fixed value num with "$D$2" a value in a cell percent position between min and max percentile rank among the values

Step 4 — Let readers edit the targets

Hard-coded thresholds hide the target inside the rule, where nobody can see or change it. Pass cell references instead, and the rule reads the target from the sheet:

Python
rule = IconSetRule("3TrafficLights1", "num", [0, "$D$2", "$E$2"])
ws.conditional_formatting.add("B2:B6", rule)
wb.save("attainment.xlsx")

Use absolute references with dollar signs, so every cell in the range compares with the same targets. Rendering the saved file with LibreOffice shows Leeds and Derby green, York amber and Hull and Bath red; change D2 to 50% and Hull turns amber without anyone touching the rule. Put the targets on the report sheet, labelled, or on a settings sheet that report owners know about.

Step 5 — Exclusive boundaries, reversed order and icon-only cells

Three switches cover most remaining needs. reverse=True swaps the icon order, for measures where low is good — defect rates, days overdue. showValue=False hides the number and shows only the icon. And an inclusive boundary can be made exclusive by setting gte=False on the threshold, so a value must be strictly greater than it:

Python
defects = IconSetRule("3TrafficLights1", "num", [0, 0.02, 0.05], reverse=True)
strict = IconSetRule("3TrafficLights1", "num", [0, 0.70, 0.90], showValue=False)
strict.iconSet.cfvo[2].gte = False            # green only above 90%, not at exactly 90%
for row in range(2, 7):
    ws[f"C{row}"] = f"=B{row}"                # an icon-only copy of the attainment
ws.conditional_formatting.add("C2:C6", strict)
print(strict.iconSet.cfvo[2].gte)             # False

IconSetRule has no argument for gte, but the threshold objects it creates are exposed in rule.iconSet.cfvo and can be adjusted before the rule is added. An icon-only column works well next to the labelled number, as in the sample: column C shows the verdict, column B the figure.

Step 6 — Icons from a status that needs a calculation

Icon sets judge a cell by its own value. When the status depends on several columns — behind plan and falling, say — compute a status number in a helper column and put the icons on that column with the value hidden:

Python
ws["G1"] = "Status"
for row in range(2, 7):
    ws[f"G{row}"] = f'=IF(B{row}>=$E$2,3,IF(B{row}>=$D$2,2,1))'
ws.conditional_formatting.add("G2:G6", IconSetRule("3Symbols", "num", [1, 2, 3], showValue=False))
wb.save("attainment.xlsx")
print(ws["G3"].value)          # =IF(B3>=$E$2,3,IF(B3>=$D$2,2,1))

The formula can combine anything Excel can calculate, and the icon rule simply maps 1, 2 and 3 to its three icons. Writing formulas with openpyxl is covered in Write a Formula to an Excel Cell with openpyxl.

Adding an icon set that stays correct Choose an icon set whose meaning matches the measure, list one lower-bound threshold per icon starting from the lowest, use num thresholds or cell references for targets, adjust gte, reverse or showValue if needed, then reload the saved file and check the rule. Choose the set lights, arrows, rating Lower bounds one per icon Targets in cells "$D$2" Fine-tune gte, reverse, showValue Reload and check after saving

Step 7 — Check the rules after saving

openpyxl does not validate icon rules: passing two thresholds to a three-icon set is accepted without complaint and produces a rule Excel may repair or drop. Reload the file and inspect what was written:

Python
for cf in load_workbook("attainment.xlsx")["Attainment"].conditional_formatting:
    for r in cf.rules:
        icons = int(r.iconSet.iconSet[0])
        vals = [(c.type, c.val, c.gte) for c in r.iconSet.cfvo]
        assert icons == len(vals), f"{cf.sqref}: {icons} icons but {len(vals)} thresholds"
        print(cf.sqref, r.iconSet.iconSet, vals)

Listing and removing existing rules is covered in List and Remove Conditional Formatting with openpyxl.

Common pitfalls

  • Thresholds in the wrong order. They go from the lowest icon to the highest. Listing them from green down gives every value the top icon.
  • Percent values as whole numbers. With num, 90% is 0.9, because the cell holds 0.9. With percent, the threshold is 90.
  • Icons on numbers stored as text. Icon sets ignore text, so a column of "95%" strings shows no icons at all. Convert the values to numbers before writing them.
  • Rules added on every run. Re-running a script against an existing workbook adds the rule again each time, and the duplicates pile up in Excel's rule manager. Clear the range's rules before adding, or build the workbook from scratch.
  • Colour-only meaning. Some readers cannot tell red from green. Prefer sets whose shapes differ, such as 3Symbols or arrows, or keep the value visible.

Conclusion

Icon sets turn numbers into verdicts that update with the data. Choose a built-in set whose meaning fits the measure, give one lower-bound threshold per icon from the lowest up, use num thresholds — ideally cell references readers can edit — and adjust gte, reverse and showValue through the rule's threshold objects. Compute complex statuses in a helper column, and reload the file to check every rule has as many thresholds as icons.

Frequently asked questions

How do I add traffic lights to Excel cells with Python? Create IconSetRule("3TrafficLights1", "num", 0, amber_from, green_from) and add it to the range with ws.conditional_formatting.add. Each value is the lower bound of an icon's band, starting with the lowest icon.

What is the difference between num, percent and percentile? num compares cell values with fixed numbers. percent places the thresholds between the range's minimum and maximum, and percentile uses the distribution of values. Only num gives the same icon for the same value regardless of the other cells.

Can the thresholds come from cells in the sheet? Yes. Pass a cell reference such as "$E$2" as a threshold value. Readers can then change the target in the cell and the icons update without editing the rule.

How do I show only the icon without the number? Pass showValue=False. The cell keeps its value for formulas and sorting, but only the icon is displayed.

More on conditional formatting

More on conditional formatting →