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

Highlight Values Outside a Tolerance of Target with openpyxl

Colour each actual figure by how far it is from its own target — green within tolerance, amber close to the limit, red outside it — with openpyxl conditional formatting: percentage and absolute tolerances, a tolerance setting readers can change, per-row tolerances, targets of zero, and a recalculated check that the rules flag exactly the rows pandas expects.
Conditional formattingopenpyxl

Many operational reports compare an actual figure with a target that differs row by row: each machine's output against its rated capacity, each cost centre's spend against its budget, each measurement against its specification. What the reader needs is not "high" or "low" but "within tolerance or not": a line 2% under target is fine, one 12% under is not. Conditional formatting rules that compare cells with fixed thresholds cannot express that, but formula-based rules can, because each row's rule can look at that row's own target. This page builds a target report with openpyxl whose actual values turn green within tolerance, amber near the limit and red beyond it, with the tolerance held in a cell readers can change, an absolute-tolerance variant, per-row tolerances, safe handling of zero targets, and a recalculation check that the rules flag exactly the rows a pandas calculation says they should. It belongs to Applying Conditional Formatting with openpyxl.

Prerequisites

Bash
pip install openpyxl pandas

LibreOffice (soffice) recalculates the workbook for the check in Step 4. The example is a production report: each line's output for the week against its target, with a default tolerance of 5%.

Step 1 — The report with a tolerance setting

Put the tolerance in a cell with a name, so the rules can refer to it and a reader can change it:

Python
import pandas as pd
from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

lines = pd.DataFrame({"Line": ["L1", "L2", "L3", "L4", "L5", "L6", "L7", "L8"],
                      "Target": [1200, 950, 1500, 800, 0, 1100, 640, 2000],
                      "Actual": [1176, 1010, 1320, 812, 15, 1046, 702, 1990],
                      "Line tolerance": [None, None, None, 0.02, None, None, 0.15, None]})

wb = Workbook()
ws = wb.active
ws.title = "Output"
ws["A1"], ws["B1"] = "Tolerance", 0.05
ws["B1"].number_format = "0%"
wb.defined_names["Tolerance"] = DefinedName("Tolerance", attr_text="Output!$B$1")
ws.append([])
ws.append(list(lines.columns) + ["Variance %"])
for r, row in enumerate(lines.itertuples(index=False), start=4):
    ws.append([row.Line, row.Target, row.Actual, row._3])
    ws[f"E{r}"] = f'=IF(B{r}=0,"",C{r}/B{r}-1)'
    ws[f"E{r}"].number_format = "+0.0%;-0.0%;0.0%"
    ws[f"D{r}"].number_format = "0%"
last = ws.max_row
print(f"data in rows 4–{last}; tolerance cell B1 named 'Tolerance'")

The tolerance lives in B1 and is named Tolerance, so rules can use the name rather than $B$1 and keep working if someone inserts a row above the table. A variance column shows each line's percentage difference from target, guarded so a zero target gives an empty cell rather than #DIV/0!. Two lines carry their own tolerance in column D — L4 is a precision line held to 2%, L7 a new line allowed 15% — and the rest fall back to the sheet-wide 5%.

Step 2 — The tolerance rules

Each rule is a formula for the first data row; Excel shifts it down for every other row. The effective tolerance is the row's own value if it has one, otherwise the named default:

Python
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill

TOL = "IF(ISNUMBER($D4),$D4,Tolerance)"
DEV = "ABS($C4-$B4)/$B4"
rules = [
    ("zero target", f"AND($B4=0,$C4<>0)", "D9D9D9", "404040"),
    ("outside", f"AND($B4<>0,{DEV}>{TOL})", "FFC7CE", "9C0006"),
    ("near limit", f"AND($B4<>0,{DEV}>{TOL}*0.6)", "FFEB9C", "7F6000"),
    ("within", f"AND($B4<>0,{DEV}<={TOL}*0.6)", "C6EFCE", "006100"),
]
area = f"C4:C{last}"
for name, formula, fill, font in rules:
    ws.conditional_formatting.add(area, FormulaRule(formula=[formula], fill=PatternFill("solid", fgColor=fill),
                                                    font=Font(color=font), stopIfTrue=True))
print("\n".join(f"{n:<12} {f}" for n, f, _, _ in rules))

The rules are evaluated in order, and stopIfTrue makes the first match win, so they can be written from the most specific to the least. A zero target gets its own grey rule — any output on a line that was meant to be idle is worth a look, and a percentage deviation from zero is meaningless. Outside tolerance is red. The amber band starts at 60% of the tolerance, warning that a line is drifting before it fails; within that, green. Columns are locked with $ and rows left relative, so every row compares its own actual, target and tolerance. The rule order and stopIfTrue behaviour are covered in Control Conditional Formatting Rule Order and Stop If True.

The colour each actual value gets A non-zero actual against a zero target is grey. Otherwise the deviation from target is compared with the row's tolerance, or the sheet default of five percent. Beyond the tolerance is red, between sixty and one hundred percent of it is amber, and closer than that is green. Deviation from this row's target target is 0 grey check the line > tolerance red outside tolerance 60–100% of it amber drifting < 60% of it green within tolerance

Step 3 — An absolute tolerance instead

Percentages are right for most targets, but not for small ones: 5% of a target of 40 is two units, which may be within measurement noise. An absolute tolerance — "within 25 units" — is a different expression of the same idea. Add it as a second sheet so both can be compared:

Python
abs_ws = wb.create_sheet("Absolute")
abs_ws["A1"], abs_ws["B1"] = "Tolerance (units)", 25
wb.defined_names["UnitTolerance"] = DefinedName("UnitTolerance", attr_text="Absolute!$B$1")
abs_ws.append([])
abs_ws.append(["Line", "Target", "Actual"])
for row in lines.itertuples(index=False):
    abs_ws.append([row.Line, row.Target, row.Actual])
abs_ws.conditional_formatting.add(f"C4:C{abs_ws.max_row}", FormulaRule(formula=["ABS($C4-$B4)>UnitTolerance"],
                                  fill=PatternFill("solid", fgColor="FFC7CE"), font=Font(color="9C0006")))
wb.save("output_tolerance.xlsx")

Here a line is flagged when it misses its target by more than 25 units, whatever the target's size, so L1's shortfall of 24 is fine while L2's overshoot of 60 is flagged, and the zero-target line L5 needs no special case because no division is involved. In practice the two are often combined — "within 5% or 25 units, whichever is larger" — which is one MAX() inside the rule. Comparing values against fixed thresholds instead of per-row targets is covered in Highlight Cells Above a Threshold with openpyxl.

Percentage or absolute tolerance A percentage tolerance scales with the target, so it suits targets of very different sizes but needs a guard for zero targets and is too strict for small ones. An absolute tolerance is the same number of units for every row, so it suits measurements with a fixed precision. Percentage (5%) scales with the target needs a zero-target guard strict on small targets typical for output and budgets Absolute (25 units) same limit for every row no division needed lenient on large targets typical for measurements

Step 4 — Check the rules against pandas

Rules that nobody can test tend to be subtly wrong. Write the same classification as a hidden helper formula, recalculate, and compare with pandas:

Python
import subprocess
from openpyxl import load_workbook

wb = load_workbook("output_tolerance.xlsx")
ws = wb["Output"]
for r in range(4, ws.max_row + 1):
    tol, dev = TOL.replace("4", str(r)), DEV.replace("4", str(r))
    ws[f"G{r}"] = (f'=IF(AND($B{r}=0,$C{r}<>0),"grey",IF($B{r}=0,"",IF({dev}>{tol},"red",'
                   f'IF({dev}>{tol}*0.6,"amber","green"))))')
ws.column_dimensions["G"].hidden = True
wb.save("output_tolerance.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "output_tolerance.xlsx"], check=True, capture_output=True)
excel = [c.value for c in load_workbook("calc/output_tolerance.xlsx", data_only=True)["Output"]["G"][3:]]

tol = lines["Line tolerance"].fillna(0.05)
dev = (lines["Actual"] - lines["Target"]).abs() / lines["Target"].where(lines["Target"] != 0)
expected = ["grey" if t == 0 else "red" if d > tl else "amber" if d > tl * 0.6 else "green"
            for t, d, tl in zip(lines["Target"], dev, tol)]
for line, e, x in zip(lines["Line"], expected, excel):
    print(f"{line}: pandas {e:<6} Excel {x:<6} {'ok' if e == x else 'MISMATCH'}")

Every line gets the same colour from the workbook formula as from pandas: L1 at −2% and L8 at −0.5% are green; L6 at −4.9% is amber, close to its 5% limit; L2 at +6.3% and L3 at −12% are red; L4, only 1.5% over but held to a 2% tolerance, is amber; L7, 9.7% over but allowed 15%, is amber too; and L5, producing on a zero target, is grey. The helper column uses the same expressions as the rules, so if it is right, the colours are right — and it stays in the file, hidden, as documentation of what the colours mean. Change the tolerance in B1 to 10% and the colours update without touching the rules: L2, at 6.3%, would then be amber, L6 would turn green, and only L3 would stay red — a quick way to show a manager what a looser standard would mean. Keeping the classification in one place also helps when the same logic is needed elsewhere — a summary count of red lines on a dashboard is simply COUNTIF over the hidden helper column.

The output report after recalculation L1 and L8 are green, close to target. L6 at minus 4.9 percent and L4 and L7, near their own tolerances, are amber. L2 at plus 6.3 percent and L3 at minus 12 percent are red. L5, producing against a zero target, is grey. A B C D E 1 Line Target Actual Tolerance Variance % 2 L1 1200 1176 5% -2.0% 3 L2 950 1010 5% +6.3% 4 L3 1500 1320 5% -12.0% 5 L4 800 812 2% +1.5% 6 L5 0 15 5% 7 L6 1100 1046 5% -4.9%

Common pitfalls

  • Fixed thresholds for per-row targets. Use formula rules that read each row's target.
  • Dividing by a zero target. Give zero targets their own rule before the percentage rules.
  • Rules in the wrong order. Order from specific to general and use stopIfTrue.
  • Tolerance hard-coded in rules. Put it in a named cell readers can change.
  • Untested rules. Mirror them in a helper formula and compare after recalculation.

Conclusion

To highlight values by their distance from a per-row target, write formula rules on the actual column that compare ABS(actual − target) / target with the row's tolerance — its own column if set, otherwise a named default cell — and order them zero target, outside, near limit, within, each with stopIfTrue. Use an absolute tolerance where percentages mislead, and verify the logic by mirroring it in a helper formula and comparing the recalculated results with pandas.

Frequently asked questions

How do I highlight values that miss their target by more than 5%? Add a FormulaRule on the actual column with a formula such as ABS($C4-$B4)/$B4>0.05, written for the first data row; Excel applies it to each row with that row's target.

How can readers change the tolerance used by conditional formatting? Put the tolerance in a cell, give it a name, and refer to the name in the rule formulas; changing the cell updates every highlight.

How do I handle a target of zero in a percentage rule? Add a rule for zero targets first, with stopIfTrue, and include $B4<>0 in the percentage rules so they never divide by zero.

Can each row have its own tolerance? Yes. Store it in a column and use IF(ISNUMBER($D4),$D4,Tolerance) in the rules so rows without one fall back to the default.

More on conditional formatting

More on conditional formatting →