We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Syncfusion Feedback

Trusted by the world’s leading companies

Syncfusion Trusted Companies

Overview

The Syncfusion .NET Excel Library (XlsIO) enables users to apply formatting to cells based on specific conditions dynamically. This feature improves the readability and automation of cell styles in the worksheet.

Conditional formatting Code Example

This example code shows how to apply conditional formatting to an Excel worksheet using the Syncfusion .NET Excel Library with just a few lines of code in C#.

// Initialize Excel engine and application
using ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
// Applying conditional formatting to "A1"
IConditionalFormats condition = worksheet.Range["A1"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
// Represents conditional format rule that the value in target range should be between 10 and 20
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Between;
condition1.FirstFormula = "10";
condition1.SecondFormula = "20";
worksheet.Range["A1"].Text = "Enter a number between 10 and 20";
// Setting back color and font style to be applied for target range
condition1.BackColor = ExcelKnownColors.Light_orange;
condition1.IsBold = true;
condition1.IsItalic = true;
// Applying conditional formatting to "A3"
condition = worksheet.Range["A3"].ConditionalFormats;
IConditionalFormat condition2 = condition.AddCondition();
// Represents conditional format rule that the cell value should be 1000
condition2.FormatType = ExcelCFType.CellValue;
condition2.Operator = ExcelComparisonOperator.Equal;
condition2.FirstFormula = "1000";
worksheet.Range["A3"].Text = "Enter the Number as 1000";
// Setting fill pattern and back color to target range
condition2.FillPattern = ExcelPattern.LightUpwardDiagonal;
condition2.BackColor = ExcelKnownColors.Yellow;
// Applying conditional formatting to "A5"
condition = worksheet.Range["A5"].ConditionalFormats;
IConditionalFormat condition3 = condition.AddCondition();
// Setting conditional format rule that the cell value for target range should be less than or equal to 1000
condition3.FormatType = ExcelCFType.CellValue;
condition3.Operator = ExcelComparisonOperator.LessOrEqual;
condition3.FirstFormula = "1000";
worksheet.Range["A5"].Text = "Enter a Number which is less than or equal to 1000";
// Setting back color to target range
condition3.BackColor = ExcelKnownColors.Light_green;
// Save the Excel document
using FileStream outputStream = new FileStream("ConditionalFormat.xlsx", FileMode.Create, FileAccess.Write);
workbook.SaveAs(outputStream);

Key features of conditional formatting

Discover how the conditional formatting features in the Syncfusion Excel Library enhance data visualization by applying dynamic formatting rules to highlight key data based on specific conditions.

Highlight cells

Apply conditional formatting to highlight specific cells based on criteria, enhancing data visibility by drawing attention to important values. This makes it easier to identify trends or outliers in your reports.

Top/Bottom

The top/bottom conditional formatting option emphasizes the highest and lowest values within a selected range, making it useful for quickly identifying top performers or areas that need attention, thereby aiding in data analysis.

Data bars

Data bars visually represent the magnitude of values in cells by adding colored bars directly within the cells. This provides a quick, at-a-glance understanding of data distribution and comparison across your dataset.

Color scales

Color scales allow you to apply a gradient color format to cells based on their values, enabling easy comparison and analysis. This helps users visualize data trends and variations effectively.

Icon sets

Icon sets enable you to add visual indicators, such as arrows or symbols, to cells based on their values. This enhances data presentation by providing immediate context and interpretation, making reports more intuitive.

Explore conditional formatting references

Discover valuable resources from our blog and knowledge base to enhance your efficiency in using conditional formatting.

Easiest Ways to Highlight Data in Excel Using C#

Blog

Easiest Ways to Highlight Data in Excel Using C#

Set criteria for icon set conditional formats in Excel using XlsIO

Knowledge base

How to set criteria for icon set conditional formats in Excel using XlsIO

Set conditional formatting for text in C#, VB.NET

Knowledge base

Set conditional formatting for text in C#, VB.NET;

Set conditional formatting for numbers

Knowledge base

How to set conditional formatting for numbers

Set multiple-condition formatting using C#, VB.NET

Knowledge base

How to set multiple-condition formatting using C#, VB.NET

Syncfusion .NET Excel Library Resources

Explore these resources for comprehensive guides, knowledge base articles, insightful blogs, and ebooks.

Frequently Asked Questions

You need to select the maximum number of rows and column values, which are 1,048,576 rows and 16,384 columns, while creating a conditional format using the Syncfusion Excel Library.

The Syncfusion Excel Library supports 5 arrows, 5 arrows gray, 5 boxes,5 quarters, 5 ratings, 4 arrows,4 arrows gray, 4 ratings, 4 red to black, 4 traffic lights, 3 arrows, 3 arrows gray, 3 flags, 3 signs, 3 stars, 3 symbols, 3 symbols 2, 3 traffic lights 1, 3 traffic lights 2, 3 triangles icon sets for creating or editing conditional formatting in Excel documents.

Yes. You can use negative data bar conditional formatting in the Syncfusion Excel Library.

You can add formulas in conditional formatting using FirstFormula and SecondFormula properties using the Syncfusion Excel Library?

The Syncfusion Excel Library supports 2-color scale and 3-color scale types in conditional formatting.

The Syncfusion Excel Library supports context, right to left, and left to right data bar conditional formatting.

Our Customers Love Us

Having an excellent set of tools and a great support team, Syncfusion® reduces customers’ development time.
Here are some of their experiences.

Rated by users across the globe

Want to create, View, and edit Excel files in C# or VB.NET?

Start a free 30-day evaluation today!
DOWNLOAD FREE TRIAL

No credit card required.

Mobile Free Evaluation Section

Awards

Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion® is proud to hold the following industry awards.

Scroll up icon