Conditional formatting

Style cells according to the values they hold

Conditional formatting reacts to content. Where cell styles fix appearance by position, these rules apply wherever the data meets a condition.

Every rule takes the four range keys, and two optional keys that behave the same way across all four rule types.

start_row / start_col / end_row / end_col · int · required

The range the rule covers.

priority · int · default: "1"

Decides which rule wins when ranges overlap. Lower numbers take precedence.

style · dict

How matching cells look, in the same shape as cell styles. When you omit it, Jetxl applies bold red text.

Color scales and data bars generate their own coloring, so style has no effect on them.

Highlight cells that meet a condition

rule_type · "cell_value" · required

operator · str · required

One of greater_than, less_than, equal, not_equal, greater_than_or_equal, less_than_or_equal, or between.

value · str · required

The threshold, written as a string. For between, give two numbers separated by a comma, such as "10,100".

conditional_formats=[
    {
        "start_row": 2,
        "start_col": 2,
        "end_row": 100,
        "end_col": 2,
        "rule_type": "cell_value",
        "operator": "greater_than",
        "value": "50",
        "style": {
            "font": {"bold": True, "color": "FF0000"},
            "fill": {"pattern": "solid", "fg_color": "FFFF00"},
        },
    }
]

Check the spelling of operator. An unrecognized value falls back to greater_than without raising an error, so greater_then produces a working file with the wrong rule in it.

Grade a range by color

rule_type · "color_scale" · required

min_color · str · required

Color for the lowest value.

max_color · str · required

Color for the highest value.

mid_color · str

Adding a midpoint turns a two-color ramp into a three-color one.

conditional_formats=[
    {
        "start_row": 2,
        "start_col": 2,
        "end_row": 100,
        "end_col": 2,
        "rule_type": "color_scale",
        "min_color": "F8696B",
        "mid_color": "FFEB84",
        "max_color": "63BE7B",
    }
]

Two colors suit a simple low-to-high ramp. Three suit deviation from a middle, such as under target, on target, and over target.

Show magnitude with bars

rule_type · "data_bar" · required

color · str · required

Bar fill color.

show_value · bool · default: "True"

Keeps the number visible next to the bar. Set it to False for bars alone.

conditional_formats=[
    {
        "start_row": 2,
        "start_col": 2,
        "end_row": 100,
        "end_col": 2,
        "rule_type": "data_bar",
        "color": "638EC6",
    }
]

Highlight the highest or lowest values

rule_type · "top10" · required

The name comes from Excel’s dialog. The count isn’t fixed at ten.

rank · int · required

How many cells to highlight.

bottom · bool · default: "False"

Set it to True to highlight the lowest values instead of the highest.

conditional_formats=[
    {
        "start_row": 2,
        "start_col": 2,
        "end_row": 100,
        "end_col": 2,
        "rule_type": "top10",
        "rank": 5,
        "bottom": True,
        "style": {"font": {"bold": True, "color": "FF0000"}},
    }
]

Layering rules

Rules are a list, and priority resolves overlaps. If you want negative numbers in red and every value carrying a bar, give the red rule the lower priority so it wins on the cells it covers.

conditional_formats=[
    {
        "rule_type": "cell_value",
        "operator": "less_than",
        "value": "0",
        "priority": 1,
        "style": {"font": {"color": "FF0000"}},
        "start_row": 2, "start_col": 2, "end_row": 100, "end_col": 2,
    },
    {
        "rule_type": "data_bar",
        "color": "638EC6",
        "priority": 2,
        "start_row": 2, "start_col": 2, "end_row": 100, "end_col": 2,
    },
]

An unrecognized rule_type doesn’t raise. Jetxl drops the rule and writes the rest of the file normally, so a typo gives you a spreadsheet with no conditional formatting and no indication why. Open the output and confirm the rule is there.