Fixing Python xlsxwriter That Silently Skips Conditional Formatting on Filtered Rows

You write a clean xlsxwriter script, apply conditional formatting to highlight overdue dates or out-of-range values, hand the file to a colleague, and the moment they use Excel's autofilter the colored cells go blank. No error, no warning — the formatting just stops working on filtered rows.

The root cause is a mismatch between how Excel stores conditional formatting ranges and how filtering interacts with visible cells at render time. This article walks you through exactly what goes wrong and four concrete fixes you can apply today.

What You'll Learn

• Why Excel's autofilter appears to break conditional formatting applied by xlsxwriter
• How to define the correct cell range so formatting survives filtering
• When to use formula-based rules instead of value-based rules
• How to avoid cell-level format conflicts that mask conditional rules
• Ordering gotchas — when add_format and autofilter call order matters

Prerequisites

You need Python 3.8+ and xlsxwriter installed (pip install XlsxWriter). The examples assume you are generating a .xlsx file programmatically; you do not need openpyxl or any Excel installation on the server. Basic familiarity with xlsxwriter's Workbook and Worksheet APIs is assumed.

How Excel Applies Conditional Formatting to Filtered Ranges

Conditional formatting in Excel is stored as a rule attached to a cell range, for example B2:B100. When you open the file, Excel evaluates every cell in that range against the rule and applies the fill, font, or border if the condition is met.

When you turn on autofilter and hide some rows, Excel does not remove those cells from the conditional format range. The rule still covers them — but the cells are not visible. What changes is whether the format renders to the screen. If a row is hidden by the filter, the conditional format is effectively invisible for that row until the row is shown again.

This is expected Excel behavior. The problem with xlsxwriter is almost never Excel itself — it is how the range or rule was written into the file.

Why xlsxwriter Seems to Skip Filtered Rows

There are four common coding mistakes that produce the "formatting disappears on filter" symptom:

1. The conditional format range is too narrow. You formatted B2:B10 but the autofilter covers A1:E50. Filtered rows outside B2:B10 were never covered.
2. The autofilter was added before the conditional format, and in some edge cases this affects how Excel resolves overlapping rules on load.
3. A value-based rule compares against a literal while your data contains the value as a formula result — Excel re-evaluates formulas after filtering, sometimes in an unexpected order.
4. A hard cell-level format overrides the conditional format. xlsxwriter's write() call with an explicit add_format() object sets a static format that can visually mask the conditional rule.

Each fix below addresses one of these causes. Start with Fix 1; it resolves the majority of cases.

Reproducing the Bug Step by Step

Here is a minimal script that triggers the problem. It writes 20 rows of sales data, applies a conditional format to highlight rows where the value is below 100, and adds an autofilter. When you filter by Region = "East" and then clear the filter, you will notice rows outside the first ten never highlight.

import xlsxwriter

workbook = xlsxwriter.Workbook("broken_example.xlsx")
worksheet = workbook.add_worksheet()

headers = ["Region", "Sales"]
rows = [
    ["East", 80], ["West", 120], ["East", 45], ["North", 200],
    ["West", 90],  ["South", 30], ["East", 150],["North", 70],
    ["West", 60],  ["South", 110],["East", 95], ["West", 40],
    ["North", 185],["South", 55], ["East", 77], ["West", 130],
    ["North", 20], ["South", 88], ["East", 160],["West", 50],
]

worksheet.write_row(0, 0, headers)
for i, row in enumerate(rows, start=1):
    worksheet.write_row(i, 0, row)

# BUG: range only covers first 10 data rows
red_fill = workbook.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
worksheet.conditional_format("B2:B11", {
    "type": "cell",
    "criteria": "less than",
    "value": 100,
    "format": red_fill,
})

# Autofilter covers all 20 rows but conditional format does not
worksheet.autofilter(0, 0, len(rows), len(headers) - 1)

workbook.close()

Open the file, filter for any region, then clear the filter. Rows 12–21 are never highlighted even though many have sales below 100.

Fix 1: Extend the Conditional Format Range to Cover All Data Rows

The simplest and most common fix is making the conditional format range match your actual data range. Compute it dynamically from the number of rows rather than hardcoding it.

import xlsxwriter

workbook = xlsxwriter.Workbook("fixed_example.xlsx")
worksheet = workbook.add_worksheet()

headers = ["Region", "Sales"]
rows = [
    ["East", 80], ["West", 120], ["East", 45], ["North", 200],
    ["West", 90],  ["South", 30], ["East", 150],["North", 70],
    ["West", 60],  ["South", 110],["East", 95], ["West", 40],
    ["North", 185],["South", 55], ["East", 77], ["West", 130],
    ["North", 20], ["South", 88], ["East", 160],["West", 50],
]

worksheet.write_row(0, 0, headers)
for i, row in enumerate(rows, start=1):
    worksheet.write_row(i, 0, row)

num_data_rows = len(rows)
last_row = num_data_rows  # row index 1-based: header=1, data=2 to num_data_rows+1

red_fill = workbook.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})

# FIX: range now covers all data rows
worksheet.conditional_format(
    1, 1, last_row, 1,  # first_row, first_col, last_row, last_col (0-indexed)
    {
        "type": "cell",
        "criteria": "less than",
        "value": 100,
        "format": red_fill,
    }
)

worksheet.autofilter(0, 0, last_row, len(headers) - 1)

workbook.close()

Notice the use of conditional_format(row1, col1, row2, col2, options) with numeric indices. This avoids the off-by-one errors that creep in when you construct A1-notation strings manually. The autofilter and the conditional format now cover the exact same row span.

Fix 2: Apply Formatting Before Adding the Autofilter

xlsxwriter writes XML sequentially, and the order in which worksheet-level features are written can affect how Excel's own parser resolves them. As a rule, call conditional_format() before autofilter() in your script.

# Correct order
worksheet.conditional_format(1, 1, last_row, 1, {"type": "cell", ...})
worksheet.autofilter(0, 0, last_row, len(headers) - 1)

# Avoid this order
# worksheet.autofilter(...)
# worksheet.conditional_format(...)

In practice this ordering fix rarely matters on its own, but combined with Fix 1 it eliminates a whole class of edge-case rendering differences between Excel versions. It costs you nothing and is worth making a habit.

Fix 3: Use a Formula-Based Rule Instead of a Value Rule

If your "Sales" column contains formula results rather than literal numbers — for example =SUM(C2:D2) — a type: cell rule may not evaluate correctly after filtering because Excel re-calculates in a different order. Switching to a type: formula rule forces Excel to re-evaluate the condition from scratch against the live cell value.

red_fill = workbook.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})

# Formula rule: $B2 anchors the column, lets the row reference be relative
worksheet.conditional_format(
    1, 1, last_row, 1,
    {
        "type": "formula",
        "criteria": "=$B2<100",
        "format": red_fill,
    }
)

The $B2 notation is important. The $ anchors the column to B so the rule always checks column B, while the row number remains relative so each row evaluates its own cell. If you write $B$2 with both anchors, every row evaluates only B2, and you get all-or-nothing formatting on the entire range.

This is also the approach you need when you want to highlight an entire row based on the value in one column — extend the conditional format range to cover all columns and use a formula rule with an anchored column reference.

Fix 4: Avoid Conflicting Cell-Level Formats

xlsxwriter lets you pass an add_format() object directly to write(). If that format includes a background color, it will visually sit on top of the conditional format — the conditional format is still in the file, but you cannot see it because the static fill covers it.

# This static background masks any conditional format applied to the same cell
static_format = workbook.add_format({"bg_color": "#DDEBF7"})
worksheet.write(2, 1, 80, static_format)  # row 3, Sales column

The fix is to separate concerns: use write() without a background fill for data cells, and let the conditional format handle all background coloring. If you need a default row color, apply it via a conditional format rule that always evaluates to true rather than as a static cell format.

# Default row color via a "always true" formula rule at low priority
default_fill = workbook.add_format({"bg_color": "#EBF1DE"})
worksheet.conditional_format(
    1, 0, last_row, len(headers) - 1,
    {
        "type": "formula",
        "criteria": "=TRUE()",
        "format": default_fill,
    }
)

# Higher-priority rule for the real condition (added after = lower priority in xlsxwriter)
red_fill = workbook.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
worksheet.conditional_format(
    1, 1, last_row, 1,
    {
        "type": "formula",
        "criteria": "=$B2<100",
        "format": red_fill,
    }
)

In xlsxwriter, rules added later in your code get a lower priority number in Excel's XML, which means they are evaluated first. Add your most specific rules last so they take precedence. This is the opposite of what many developers expect, so keep it in mind when stacking multiple rules. If you are working with a related issue in openpyxl, the openpyxl column width fix article covers how that library handles worksheet-level properties differently.

Common Pitfalls to Watch Out For

Off-by-one errors in row/column indices

xlsxwriter uses zero-based row and column indices. Row 1 in Python is the second row in Excel (the first data row if row 0 is your header). When you call conditional_format(1, 1, 20, 1, ...) you are covering rows 2–21 in Excel notation. Double-check your range by opening the file and inspecting the conditional format rule in Excel's Conditional Formatting Manager.

Using A1 notation with a hardcoded row count

Code like worksheet.conditional_format("B2:B21", ...) works fine until someone passes a dataset with a different number of rows. Always derive the last row from len(data) and either construct the A1 string dynamically with an f-string or switch to numeric indices.

Formatting the header row by accident

If your range starts at row 0 (Excel row 1) and that row contains headers, the conditional rule will try to apply to the header text. A numeric comparison against text returns false silently, but a formula rule referencing the header text may produce a #VALUE! error in Excel's rule evaluator. Always start your conditional format range at row 1 (the first data row in zero-based notation).

Multiple overlapping rules fighting each other

If you apply two rules to overlapping ranges and both evaluate to true, Excel applies the higher-priority one and stops (unless you tick "stop if true" off). Use the Conditional Formatting Manager in Excel to inspect the rule stack and their priority order when debugging a live file.

For reference, the same kind of silent-skip behavior can appear when you write merged cells — xlsxwriter's merge behavior and how it interacts with cell formats is covered in detail in the article on xlsxwriter ignoring cell formats on merged cells.

If your pipeline reads data back in before writing, make sure your DataFrame's column order is what you expect — a misaligned column can send your conditional format to the wrong data entirely. The article on Pandas exporting the wrong column order to Excel is worth a quick read if you suspect that issue.

And if you are building a report where the Excel sheet is assembled from multiple DataFrames written with to_excel, be aware that sheet-overwrite bugs can wipe formatting entirely — see the Pandas to_excel sheet overwrite fix to avoid that trap.

Wrapping Up: Next Steps

The "conditional formatting disappears on filter" bug almost always comes down to one of four causes: a range that is too narrow, rule ordering, value-vs-formula rule type, or a static cell format masking the conditional one. Here is what to do next:

1. Check your range first. Open the generated file, go to Home → Conditional Formatting → Manage Rules, select "This Worksheet", and verify the range shown matches your data range exactly.
2. Switch to numeric index arguments for conditional_format() and derive the last row from len(data) rather than hardcoding it.
3. Migrate value-based rules to formula-based rules if your data column contains formula results or if you need to highlight entire rows.
4. Remove background fills from static cell formats and use a low-priority always-true conditional format rule for default row coloring instead.
5. Add conditional formats before autofilter in your script as a consistent ordering convention.