How to Apply Conditional Formatting in Excel with C#

What comparison operators are available?

IronXL supports a comprehensive set of comparison operators that allow you to create sophisticated conditional formatting rules. These operators work seamlessly with numeric values, dates, and even text comparisons when appropriate. The available rules are:

• NoComparison: Default value, used when applying formatting without comparison
• Between: Highlights values within a specific range
• NotBetween: Highlights values outside a specified range
• Equal: Matches exact values
• NotEqual: Excludes specific values
• GreaterThan: Highlights values above a threshold
• LessThan: Highlights values below a threshold
• GreaterThanOrEqual: Includes the threshold value
• LessThanOrEqual: Includes the threshold value

These operators can be combined with various data types and work particularly well when analyzing data that you've imported from CSV files or other sources.

How do I style the conditional formatting?

When creating conditional formatting rules, IronXL provides extensive styling options. You can customize the appearance of cells that meet your conditions by modifying various visual properties. The following example demonstrates how to create a rule and apply background color styling:

:path=/static-assets/excel/content-code-examples/how-to/conditional-formatting-add.cs

using IronXL;
using IronXL.Formatting.Enums;

WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Create conditional formatting rule
var rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");

// Set style options
rule.PatternFormatting.BackgroundColor = "#54BDD9";

// Add conditional formatting rule
workSheet.ConditionalFormatting.AddConditionalFormatting("A1:A10", rule);

workBook.SaveAs("addConditionalFormatting.xlsx");

Imports IronXL
Imports IronXL.Formatting.Enums

Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Create conditional formatting rule
Private rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")

' Set style options
rule.PatternFormatting.BackgroundColor = "#54BDD9"

' Add conditional formatting rule
workSheet.ConditionalFormatting.AddConditionalFormatting("A1:A10", rule)

workBook.SaveAs("addConditionalFormatting.xlsx")

The PatternFormatting property provides access to various styling options beyond just background color. You can also modify pattern styles, foreground colors, and pattern fills to create more complex visual indicators. This flexibility allows you to create formatting that matches your organization's branding or makes specific data patterns immediately recognizable.

What does the formatting look like when applied?

Before

After

What properties can I modify on existing rules?

While working with retrieved conditional formatting rules, understand which properties can be modified. The primary modifiable property is the background color, which allows you to update the visual appearance without changing the underlying condition logic. Here's an example that demonstrates retrieving and modifying an existing rule:

:path=/static-assets/excel/content-code-examples/how-to/conditional-formatting-retrieve.cs

using IronXL;

WorkBook workBook = WorkBook.Load("addConditionalFormatting.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Create conditional formatting rule
var ruleCollection = workSheet.ConditionalFormatting.GetConditionalFormattingAt(0);
var rule = ruleCollection.GetRule(0);

// Edit styling
rule.PatternFormatting.BackgroundColor = "#B6CFB6";

workBook.SaveAs("editedConditionalFormatting.xlsx");

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addConditionalFormatting.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Create conditional formatting rule
Private ruleCollection = workSheet.ConditionalFormatting.GetConditionalFormattingAt(0)
Private rule = ruleCollection.GetRule(0)

' Edit styling
rule.PatternFormatting.BackgroundColor = "#B6CFB6"

workBook.SaveAs("editedConditionalFormatting.xlsx")

This approach is particularly useful when you need to update the visual theme of a spreadsheet while preserving the business logic embedded in the conditional formatting rules.

How do I access multiple rules in a collection?

When working with complex spreadsheets, you may encounter multiple conditional formatting rules applied to the same range or overlapping ranges. The GetConditionalFormattingAt method returns a collection that can contain multiple rules. You can iterate through these rules using standard collection methods or access specific rules by their index using GetRule(index). This functionality is especially valuable when working with data from various sources that may have different formatting requirements.

Before

After

When should I remove conditional formatting rules?

Consider removing conditional formatting rules in these situations:

• Data Export: When exporting to different formats without formatting support
• Performance Optimization: Complex rules can impact performance with large datasets
• Rule Conflicts: Multiple overlapping rules create confusion or unexpected results
• Simplified Analysis: Preparing data for automated processing where formatting is unnecessary
• Template Creation: Creating clean templates for others to use

:path=/static-assets/excel/content-code-examples/how-to/conditional-formatting-remove.cs

using IronXL;

WorkBook workBook = WorkBook.Load("addConditionalFormatting.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Remove conditional formatting rule
workSheet.ConditionalFormatting.RemoveConditionalFormatting(0);

workBook.SaveAs("removedConditionalFormatting.xlsx");

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("addConditionalFormatting.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Remove conditional formatting rule
workSheet.ConditionalFormatting.RemoveConditionalFormatting(0)

workBook.SaveAs("removedConditionalFormatting.xlsx")

What happens to cell styles after removal?

When you remove conditional formatting rules, only the conditional formatting is removed—the underlying cell data and any direct formatting remain unchanged. The cells revert to their base formatting, which includes any styles that were applied directly to the cells rather than through conditional rules. If a cell had a specific font, border, or fill color applied directly (not through conditional formatting), those styles remain after the conditional formatting is removed.

This behavior ensures that your data integrity is maintained while giving you complete control over the visual presentation of your spreadsheets. For more advanced formatting needs, explore IronXL's comprehensive API reference to understand all available formatting options.