How to Apply Conditional Formatting to Cells

Conditional formatting is a feature in spreadsheet and data processing software that allows you to apply specific formatting styles or rules to cells or data based on certain conditions or criteria. It enables you to visually highlight or emphasize data that meets particular conditions, making it easier to analyze and understand data in a spreadsheet or table.

Easily Add, Retrieve, and Remove Conditional Formatting with IronXL. When adding conditional formatting with styling, you can make font and size adjustments, set borders and alignment, and define background patterns and colors


Get started with IronXL

Start using IronXL in your project today with a free trial.

First Step:
green arrow pointer


Add Conditional Formatting Example

Conditional formatting consists of rules and styles that are applied when a cell meets the specified rule criteria. The styles can include font and size adjustments, borders and alignment settings, as well as background patterns and colors.

To define a rule, use the CreateConditionalFormattingRule method provided by IronXL. Assign the object returned by this method to a variable, and use it to apply the desired styling. Finally, utilize the AddConditionalFormatting method, providing both the created rule and the cell range to which it should be applied.

: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")
$vbLabelText   $csharpLabel
Before
After

Below are all the available rules:

  • NoComparison: The default value.
  • Between: 'Between' operator
  • NotBetween: 'Not between' operator
  • Equal: 'Equal to' operator
  • NotEqual: 'Not equal to' operator
  • GreaterThan: 'Greater than' operator
  • LessThan: 'Less than' operator
  • GreaterThanOrEqual: 'Greater than or equal to' operator
  • LessThanOrEqual: 'Less than or equal to' operator

Retrieve Conditional Formatting Example

To retrieve a conditional formatting rule, use the GetConditionalFormattingAt method. The rule object returned may contain multiple rules; use the GetRule method to access a specific one. Most properties of a retrieved rule cannot be modified, yet you can adjust the BackgroundColor via the PatternFormatting property. The following code demonstrates this:

: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")
$vbLabelText   $csharpLabel
Before
After

Remove Conditional Formatting Example

To remove a conditional formatting rule, use the RemoveConditionalFormatting method. Pass the index of the targeted rule to this method.

: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")
$vbLabelText   $csharpLabel

Frequently Asked Questions

What is conditional formatting?

Conditional formatting is a feature in spreadsheet and data processing software that allows you to apply specific formatting styles or rules to cells or data based on certain conditions or criteria. It helps in visually highlighting or emphasizing data that meets particular conditions.

How can I apply conditional formatting using IronXL?

To apply conditional formatting using IronXL, you need to create a rule using the `CreateConditionalFormattingRule` method, apply the desired styles, and then add the rule to a specific cell range using the `AddConditionalFormatting` method.

What are the available conditional formatting operators in IronXL?

IronXL provides several operators for conditional formatting including: NoComparison, Between, NotBetween, Equal, NotEqual, GreaterThan, LessThan, GreaterThanOrEqual, and LessThanOrEqual.

How can I retrieve an existing conditional formatting rule in IronXL?

To retrieve an existing conditional formatting rule, use the `GetConditionalFormattingAt` method. This method returns an object containing multiple rules, which you can access using the `GetRule` method.

Is it possible to modify properties of a retrieved conditional formatting rule?

Most properties of a retrieved conditional formatting rule cannot be modified; however, you can adjust the **BackgroundColor** through the **PatternFormatting** property.

How do I remove a conditional formatting rule using IronXL?

To remove a conditional formatting rule, use the `RemoveConditionalFormatting` method and pass the index of the rule you wish to remove.

Can I apply styling like font and size adjustments with conditional formatting in IronXL?

Yes, with IronXL, you can apply various styles such as font and size adjustments, borders, alignment settings, and background patterns and colors as part of conditional formatting rules.

What is the first step to apply conditional formatting using IronXL?

The first step is to download the IronXL.Excel library from NuGet, which provides the necessary methods to apply conditional formatting in Excel spreadsheets.

Chaknith related to Remove Conditional Formatting Example
Software Engineer
Chaknith is the Sherlock Holmes of developers. It first occurred to him he might have a future in software engineering, when he was doing code challenges for fun. His focus is on IronXL and IronBarcode, but he takes pride in helping customers with every product. Chaknith leverages his knowledge from talking directly with customers, to help further improve the products themselves. His anecdotal feedback goes beyond Jira tickets and supports product development, documentation and marketing, to improve customer’s overall experience.When he isn’t in the office, he can be found learning about machine learning, coding and hiking.