Use Conditional Formatting Rules That Reference Another Sheet

The most useful highlighting rules depend on information kept somewhere else. Orders should turn red when the customer is on the credit-hold list maintained on another tab; amounts should turn amber when they exceed a threshold that finance changes on a Settings sheet; a stock level should be flagged when it falls below the reorder point in a lookup table. Putting those lists and thresholds on their own sheets means people can update them without touching the rules — and the highlighting follows immediately. Conditional formatting formulas can reference other sheets directly in Excel 2010 and later, or through defined names, which also work in older versions and make the rules easier to read. This page writes such rules with openpyxl, explains the anchoring that makes them work on every row, and checks their logic by recalculating the same formulas in helper cells. It belongs to Applying Conditional Formatting with openpyxl.

Prerequisites

pip install openpyxl

The examples build a workbook with an Orders sheet, a Blocked sheet listing customers on credit hold, and a Settings sheet holding a review threshold. LibreOffice (soffice) is used in Step 5 to recalculate helper formulas and confirm which rows the rules will highlight; it is optional.

Step 1 — The workbook: data, a list and a setting

Put the reference data on its own sheets, each in a simple, labelled range:

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
orders = wb.active
orders.title = "Orders"
orders.append(["Order", "Customer", "Amount"])
rows = [("SO-1001", "Oakridge Farms", 1240), ("SO-1002", "Café Señor", 380), ("SO-1003", "Zenith Logistics", 5600),
        ("SO-1004", "Haddad & Sons", 920), ("SO-1005", "Café Señor", 2150), ("SO-1006", "Lindqvist AB", 7400),
        ("SO-1007", "Greenway Garden", 610)]
for r in rows:
    orders.append(list(r))

blocked = wb.create_sheet("Blocked")
blocked.append(["Customer on credit hold"])
for name in ["Café Señor", "Lindqvist AB"]:
    blocked.append([name])

settings = wb.create_sheet("Settings")
settings["A1"], settings["B1"] = "Review orders above", 5000
for ws in wb.worksheets:
    ws["A1"].font = Font(bold=True)
print([ws.title for ws in wb.worksheets])

The blocked list is a single column with a header, so it can grow downwards without changing any rule; the threshold is one labelled cell. Keeping reference data this simple is what makes it safe for other people to maintain.

Step 2 — Highlight rows whose customer is on another sheet's list

A formula rule is evaluated for each cell in its range, relative to the range's top-left cell. Write the formula for the first data row, with $ on the parts that must not move:

from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

red = PatternFill("solid", fgColor="FFC7CE")
amber = PatternFill("solid", fgColor="FFEB9C")
last = orders.max_row

orders.conditional_formatting.add(
    f"A2:C{last}",
    FormulaRule(formula=["COUNTIF(Blocked!$A$2:$A$500,$B2)>0"], fill=red, font=Font(color="9C0006")),
)
print("rule range:", f"A2:C{last}")

For each cell in A2:C8, Excel evaluates the formula as if it were written in that cell, shifting relative references. $B2 has a fixed column and a relative row, so on row 5 it becomes $B5 — every cell of a row looks at that row's customer, and the whole row is highlighted. Blocked!$A$2:$A$500 is fully anchored, so every row checks the same list; making it larger than the current list lets the list grow. The sheet name goes before the !; a name containing spaces must be quoted, as in 'Credit hold'!$A$2:$A$500. Row-highlighting rules in general are covered in Highlight an Entire Row Based on a Cell Value with openpyxl.

Step 3 — A threshold kept on a Settings sheet

The same idea makes a rule's threshold editable. Compare each amount with the Settings cell, fully anchored:

orders.conditional_formatting.add(
    f"C2:C{last}",
    FormulaRule(formula=["$C2>Settings!$B$1"], fill=amber, font=Font(bold=True)),
)
print("amount rule added; threshold cell Settings!B1 =", settings["B1"].value)

$C2 moves with the row; Settings!$B$1 never moves. When finance changes the threshold from 5,000 to 2,000, every amount above 2,000 is highlighted the next time the sheet recalculates — no one edits the rule. Rules apply in order, and a cell that matches both — a blocked customer with a large order — gets both formats where they do not conflict (red fill from the first rule, bold from the second). Controlling which rule wins is covered in Control Conditional Formatting Rule Order and Stop If True.

Step 4 — Defined names for readable, portable rules

Cross-sheet references inside conditional formatting have worked since Excel 2010. Older versions — and some other spreadsheet programs — only accept them through defined names. Names are worth using anyway, because BlockedCustomers explains itself where Blocked!$A$2:$A$500 does not:

from openpyxl.workbook.defined_name import DefinedName

wb.defined_names["BlockedCustomers"] = DefinedName("BlockedCustomers", attr_text="Blocked!$A$2:$A$500")
wb.defined_names["ReviewThreshold"] = DefinedName("ReviewThreshold", attr_text="Settings!$B$1")

named = wb.copy_worksheet(orders)
named.title = "Orders (named)"
named.conditional_formatting.add(f"A2:C{last}", FormulaRule(formula=["COUNTIF(BlockedCustomers,$B2)>0"], fill=red))
named.conditional_formatting.add(f"C2:C{last}", FormulaRule(formula=["$C2>ReviewThreshold"], fill=amber))
wb.save("orders_flags.xlsx")
print(sorted(wb.defined_names.keys()))

The two names are workbook-level, so any sheet's rules can use them. If the blocked list moves to another sheet, only the name's definition changes and every rule using it keeps working. Creating names is covered in more depth in Create a Named Range in Excel with openpyxl. (Note that copy_worksheet copies cell values and styles but not conditional formatting, which is why the rules are added to the copy again.)

Step 5 — Check the rule logic by recalculating it

Conditional formatting is evaluated when the file is opened, so Python cannot see which cells are highlighted. But the rule formulas are ordinary formulas: write the same expressions into helper cells, recalculate the workbook, and read the results:

import subprocess
from openpyxl import load_workbook

check = load_workbook("orders_flags.xlsx")
ws = check["Orders"]
ws["E1"], ws["F1"] = "Blocked?", "Review?"
for r in range(2, last + 1):
    ws[f"E{r}"] = f"=COUNTIF(BlockedCustomers,$B{r})>0"
    ws[f"F{r}"] = f"=$C{r}>ReviewThreshold"
check.save("orders_check.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "orders_check.xlsx"],
               check=True, capture_output=True)
calc = load_workbook("calc/orders_check.xlsx", data_only=True)["Orders"]
for r in range(2, last + 1):
    print(calc[f"A{r}"].value, calc[f"B{r}"].value, calc[f"C{r}"].value,
          "| red" if calc[f"E{r}"].value else "", "| amber" if calc[f"F{r}"].value else "")

The recalculated helper cells show exactly which rows each rule will highlight: Café Señor's two orders and Lindqvist's order in red, and the two orders above 5,000 — Zenith and Lindqvist — in amber, with Lindqvist matching both. Checking against an independent expectation (the blocked names, the threshold) catches the classic mistakes: a missing $ that makes every row look at row 2, an unanchored list range that slides down, a misspelt sheet name. Delete the helper columns, or do this on a copy as here, before delivering the workbook.

Rendering the workbook itself confirms the rules are applied, not just that their logic is right: converted to PDF with LibreOffice, the Orders sheet showed the three red rows and the amber cell for Zenith. Lindqvist's amount cell was red rather than amber, because when two rules set a fill on the same cell, the first rule's fill wins — Excel then still applies the second rule's bold font, while LibreOffice applies only the first matching rule. If the combination matters, write a dedicated third rule for "blocked and above threshold" and put it first.

Common pitfalls

• Anchoring the wrong parts. The row of the data cell must be relative ($B2), the list and setting fully anchored.
• Unquoted sheet names with spaces. Use 'Credit hold'!$A$2:$A$500.
• List ranges that stop at the current last row. Make them larger, or use a table column, so the list can grow.
• Older Excel versions. Use defined names for cross-sheet references.
• Assuming the rule works because it was written. Recalculate the same formula in helper cells and check.

Conclusion

Conditional formatting rules can depend on other sheets: a COUNTIF against the list on the Blocked sheet highlights rows whose customer is on a list kept elsewhere, and $C2>Settings!$B$1 compares each value with an editable threshold. Anchor the shared references fully and keep the data row relative, quote sheet names with spaces, and prefer defined names such as BlockedCustomers for readability and compatibility. Verify the logic by writing the same formulas into helper cells and recalculating, since Python cannot see the highlighting itself.

Frequently asked questions

Can conditional formatting refer to another sheet in Excel? Yes, in Excel 2010 and later a conditional formatting formula can reference another sheet directly. In older versions, refer to the other sheet through a defined name.

How do I highlight rows if a value appears in a list on another sheet? Use a formula rule that counts the row's customer in the list on the other sheet, such as COUNTIF(BlockedCustomers,$B2)>0, applied to the whole data range, with the list range fully anchored and the row of the lookup cell relative.

How do I make a conditional formatting threshold editable? Put the threshold in a cell on a Settings sheet and compare against it with an absolute reference, such as $C2>Settings!$B$1.

How can I check which cells a conditional formatting rule will highlight? Write the same formula into helper cells for each row, recalculate the workbook, for example with LibreOffice, and read the TRUE or FALSE results.

Related

• Up one level: Applying Conditional Formatting with openpyxl — rule-based formatting.
• Highlight Cells Above a Threshold with openpyxl — thresholds within one sheet.
• Check References Between Excel Sheets — validating the same lists in Python.
• Write Formulas That Reference Other Sheets with openpyxl — cross-sheet references in cells.