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
Get started with IronXL
Start using IronXL in your project today with a free trial.
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.csusing IronXL;
using IronXL.Formatting.Enums;
// Load an existing Excel workbook
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Access the default worksheet within the workbook
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Create a conditional formatting rule where cells with values less than 8 will have a specific background color
// Using a threshold of 8 for the conditional formatting rule
var rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");
// Set style options for the conditional formatting rule
// In this case, set the background color to a shade of blue
rule.PatternFormatting.BackgroundColor = "#54BDD9";
// Apply the conditional formatting rule to a specific cell range
// Here, the rule is applied to the cells in the range A1 to A10
workSheet.ConditionalFormatting.AddConditionalFormatting("A1:A10", rule);
// Save the modified workbook to a new file
workBook.SaveAs("addConditionalFormatting.xlsx");Imports IronXL
Imports IronXL.Formatting.Enums
' Load an existing Excel workbook
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
' Access the default worksheet within the workbook
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
' Create a conditional formatting rule where cells with values less than 8 will have a specific background color
' Using a threshold of 8 for the conditional formatting rule
Private rule = workSheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")
' Set style options for the conditional formatting rule
' In this case, set the background color to a shade of blue
rule.PatternFormatting.BackgroundColor = "#54BDD9"
' Apply the conditional formatting rule to a specific cell range
' Here, the rule is applied to the cells in the range A1 to A10
workSheet.ConditionalFormatting.AddConditionalFormatting("A1:A10", rule)
' Save the modified workbook to a new file
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 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.csusing IronXL;
// Load the workbook and select the default worksheet
WorkBook workBook = WorkBook.Load("addConditionalFormatting.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Check if there are any conditional formatting rules
if (workSheet.ConditionalFormatting.Count > 0)
{
// Retrieve the first conditional formatting rule collection
var ruleCollection = workSheet.ConditionalFormatting.GetConditionalFormattingAt(0);
// Check if there are any rules in the rule collection
if (ruleCollection.GetRuleCount() > 0)
{
// Retrieve the first rule within the collection
var rule = ruleCollection.GetRule(0);
// Check if the rule supports pattern formatting before applying changes
if (rule.PatternFormatting != null)
{
// Edit the background color styling of the rule
rule.PatternFormatting.BackgroundColor = "#B6CFB6";
}
else
{
Console.WriteLine("Pattern formatting is not supported for this rule.");
}
}
else
{
Console.WriteLine("No conditional formatting rules found in the rule collection.");
}
}
else
{
Console.WriteLine("No conditional formatting applied to this worksheet.");
}
// Save the edited workbook with a new name
workBook.SaveAs("editedConditionalFormatting.xlsx");Imports IronXL
' Load the workbook and select the default worksheet
Private workBook As WorkBook = WorkBook.Load("addConditionalFormatting.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
' Check if there are any conditional formatting rules
If workSheet.ConditionalFormatting.Count > 0 Then
' Retrieve the first conditional formatting rule collection
Dim ruleCollection = workSheet.ConditionalFormatting.GetConditionalFormattingAt(0)
' Check if there are any rules in the rule collection
If ruleCollection.GetRuleCount() > 0 Then
' Retrieve the first rule within the collection
Dim rule = ruleCollection.GetRule(0)
' Check if the rule supports pattern formatting before applying changes
If rule.PatternFormatting IsNot Nothing Then
' Edit the background color styling of the rule
rule.PatternFormatting.BackgroundColor = "#B6CFB6"
Else
Console.WriteLine("Pattern formatting is not supported for this rule.")
End If
Else
Console.WriteLine("No conditional formatting rules found in the rule collection.")
End If
Else
Console.WriteLine("No conditional formatting applied to this worksheet.")
End If
' Save the edited workbook with a new name
workBook.SaveAs("editedConditionalFormatting.xlsx")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.csusing IronXL;
// Load an Excel workbook from the specified file path
WorkBook workBook = WorkBook.Load("addConditionalFormatting.xlsx");
// Select the default worksheet from the loaded workbook
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Check if there is at least one conditional formatting rule to remove
// Attempting to remove a rule when none exist will throw an error.
if (workSheet.ConditionalFormatting.Count > 0)
{
// Remove the first conditional formatting rule from the default worksheet
// Index 0 indicates the first conditional formatting rule applied to the worksheet
workSheet.ConditionalFormatting.Remove(0);
}
// Save the updated workbook to a new file with the specified file name
workBook.SaveAs("removedConditionalFormatting.xlsx");Imports IronXL
' Load an Excel workbook from the specified file path
Private workBook As WorkBook = WorkBook.Load("addConditionalFormatting.xlsx")
' Select the default worksheet from the loaded workbook
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
' Check if there is at least one conditional formatting rule to remove
' Attempting to remove a rule when none exist will throw an error.
If workSheet.ConditionalFormatting.Count > 0 Then
' Remove the first conditional formatting rule from the default worksheet
' Index 0 indicates the first conditional formatting rule applied to the worksheet
workSheet.ConditionalFormatting.Remove(0)
End If
' Save the updated workbook to a new file with the specified file name
workBook.SaveAs("removedConditionalFormatting.xlsx")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.
