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
How to Apply Conditional Formatting to Cells
- Download the C# library for conditional formatting
- Load an existing Excel spreadsheet or create a new one
- Create a conditional formatting rule using the
CreateConditionalFormattingRulemethod - Add the created rule using the
AddConditionalFormattingmethod - Retrieve existing rules using the
GetConditionalFormattingAtmethod
Install with NuGet
Install-Package IronXL.Excel
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 ConditionalFormatting. Assign the object returned by this method to a variable, and use this variable to apply the desired styling. Finally, use the AddConditionalFormatting method and provide 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.csusing 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")
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 returned by this method may contain multiple rules, and you can use the GetRule method to select a specific one. While most properties of the retrieved rule cannot be modified, you can adjust the BackgroundColor by accessing it through the PatternFormatting property. The following code demonstrates this concept.
:path=/static-assets/excel/content-code-examples/how-to/conditional-formatting-retrieve.csusing 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")Before
After
Remove Conditional Formatting Example
Similarly, to remove a conditional formatting rule, use the RemoveConditionalFormatting method. Pass the index of the targeted conditional formatting rule to the method.
:path=/static-assets/excel/content-code-examples/how-to/conditional-formatting-remove.csusing 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")
