Excel-Style Conditional Formatting in .NET Applications

Conditional formatting is one of those Excel features users do not appreciate until it disappears. Color the negative numbers red. Highlight the row whose status is “Overdue.” Show a green data bar in the ”% complete” column. Add it to your .NET app and the screen suddenly tells the user what to look at.

This post walks through how conditional formatting actually works under the hood, the patterns that scale, and the ones that don’t.

The mental model

A conditional formatting rule has three parts:

1. A range it applies to — A1:A1000, the whole sheet, etc.
2. A condition — a formula or a comparison that evaluates to a boolean per cell
3. A style — what to apply when the condition is true (font color, fill, border, data bar, icon)

In Excel and in any library that round-trips to .xlsx, the rule itself is stored once on the sheet, not copied per cell. The renderer evaluates it on the fly when each cell is drawn. This matters for performance — more on that below.

A first rule

The classic case: highlight values above a threshold.

sheet.ConditionalStyles.Add(new Rule(
    "THIS > 100",                       // condition (THIS = the cell value)
    "A1:A1000",                          // range
    new WorksheetRangeStyle
    {
        Flag      = PlainStyleFlag.TextColor | PlainStyleFlag.BackColor,
        TextColor = SolidColor.White,
        BackColor = SolidColor.Red,
    }));

Now any cell in A1:A1000 whose value exceeds 100 is shown white-on-red. Set A37 = 250 and the cell repaints. Set it to 50 and the formatting goes away. No event wiring needed.

Whole-row highlighting

A common request: when column F says "Overdue", color the entire row, not just the F cell.

The trick is to use an absolute-column / relative-row reference inside the rule, the same way you would in Excel:

sheet.ConditionalStyles.Add(new Rule(
    "$F1 = \"Overdue\"",                 // $F = column locked, 1 = row varies
    "A1:Z1000",
    new WorksheetRangeStyle
    {
        Flag      = PlainStyleFlag.BackColor,
        BackColor = SolidColor.LightSalmon,
    }));

Apply the rule to the whole row range A1:Z1000. For each cell, the engine substitutes the cell’s row into the formula and evaluates $F<row> = "Overdue". One rule, every row covered.

Data bars and icon sets

Data bars and three-color scales are still rules — they just have a different style payload:

sheet.ConditionalStyles.Add(new DataBarRule(
    "B2:B500",
    new DataBarStyle
    {
        FillColor = SolidColor.SteelBlue,
        MinType   = DataBarValueType.Min,
        MaxType   = DataBarValueType.Max,
    }));

Icon sets work the same way: pick the icon set, pick the threshold values, scope it to a range.

Performance: where it goes wrong

The naïve implementation of “evaluate every rule against every cell on every paint” is fast enough for small ranges and disastrous for large ones. Some rules of thumb:

1. Apply rules to actual data ranges, not entire columns. A rule scoped to A:A evaluates against every cell Excel could potentially have (1,048,576 of them). Scope to A1:A1000 instead and the engine only looks where data lives.
2. Avoid rules whose condition is itself an expensive formula. A rule like VLOOKUP(...) evaluated per cell, per repaint, is not a rule — it’s a benchmark.
3. Watch out for bulk inserts under conditional formatting. Inserting 5,000 cells one-by-one when each insert triggers re-evaluation of overlapping rules is the worst case. ReoGrid 4.4 specifically optimized this path — bulk loading under one conditional rule is now around 11,700× faster than 4.3.13. See the v4.4 release notes for measured numbers.
4. Batch rule changes. If you are adding ten rules in a loop, suspend repaint, add them, then resume. Don’t repaint per rule.

The pattern that scales: scope rules tightly, keep conditions cheap, and use bulk insert APIs (SetRangeData) when populating data into a range that already has rules.

Round-tripping to .xlsx

If your users export to Excel, you want the formatting to survive the trip. Two compatibility points worth knowing:

• Stick to standard rule types. “Cell value compares X,” “formula is true,” “top/bottom N,” “duplicate values,” “data bars,” “color scales,” and “icon sets” are all in the OpenXML spec and round-trip cleanly.
• Custom one-off styles (gradients with specific stops, fonts you set programmatically) round-trip, but the rule semantics must be expressible in OpenXML. A condition that calls a user-defined .NET function will not survive the export — it has no Excel equivalent.

The safe heuristic: if you can describe the rule entirely in Excel’s “New Rule” dialog without writing custom VBA, it round-trips.

Removing rules

Two operations every implementation needs:

// Remove all rules from a sheet
sheet.ClearConditionalStyles();

// Check whether a specific cell is currently formatted by any rule
bool isFormatted = sheet.HasConditionalStyle(new CellPosition("A37"));

The HasConditionalStyle check is useful for “is this cell really red, or just looking red because of a rule?” — common when implementing copy/paste behavior or building a style inspector for users.

When to reach for it

Conditional formatting is usually the right tool when:

• Users need to scan the data, not analyze it
• The visual cue depends on the data itself, not on UI state
• You want behavior that is the same in Excel and in your app

It is the wrong tool when the highlighting depends on selection, hover, or app-level state — those are UI concerns and belong in your control’s drawing logic, not in the workbook.

Further reading

• ReoGrid documentation — Conditional styles
• ReoGrid 4.4 release notes (conditional formatting performance)
• Reading and Writing Excel Files in C#