Master Excel Conditional Formatting Techniques with DsExcel in C#
Tags: webdev, devops, csharp, dotnet
Published: 2024-04-09 14:52:15

---
canonical_url: https://developer.mescius.com/blogs/master-excel-conditional-formatting-techniques-with-dsexcel-in-c-sharp
description: Learn about master Excel conditional formatting techniques in your C# applications. See more from Document Solutions today.
---
**What You Will Need**
- Documents for Excel API
- Visual Studio 2022

**Controls Referenced**
- [.NET Excel Spreadsheet API Library](https://developer.mescius.com/document-solutions/dot-net-excel-api)
- [Documentation](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ApplyConditionalFormatting.html)

**Tutorial Concept**
Apply Excel conditional formatting techniques programmatically in C#.

---

Conditionally formatting data turns numbers into narratives.

When you look at a large amount of data in Excel, understanding the significance of each record is difficult. But what if you could customize the cells to grab your attention and make your analysis more compelling?

This is where Excel's conditional formatting feature comes into play, which allows you to highlight cells based on specific criteria. In this blog, we will see how to use the [<span>Document Solutions for Excel (DsExcel) API</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api "https://developer.mescius.com/document-solutions/dot-net-excel-api") to apply conditional formatting to Excel in .NET app. DsExcel offers various formatting rules, like the cell value rule, the top-bottom rule, the data bar rule, and [so on](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ApplyConditionalFormatting.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ApplyConditionalFormatting.html"). 

Let’s see how we can use some of these rules to format the following healthcare data to make it visually more attractive.

![Excel Conditional Formatting C#](//cdn.mescius.io/umb/media/wwzlkhmi/01-formatting.png?rmode=max&width=757&height=191)

Here is the list of conditional formatting rules that we are going to cover in this blog:

*   [Cell Value Rule](#cell)
*   [Data Bar Rule](#data)
*   [Icon Sets Rule](#icon)
*   [Three Color Scale Rule](#three)

## <a id="cell"></a>Cell Value Rule

The cell value rule styles the cells based on their values that fall within a certain range or match specific conditions like greater than, less than, equal to, and not equal. To apply this rule using DsExcel, just create a new [<span>FormatCondition</span>](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatCondition.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatCondition.html") object with [FormatConditionType](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.FormatConditionType.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.FormatConditionType.html") _CellValue and_ add it to the [FormatConditions](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~FormatConditions.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IRange~FormatConditions.html") collection.

Let’s understand this with an example to highlight all the patient's ages who are greater than or equal to 60 years. For this, you need to set the [FormatConditionOperator](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.FormatConditionOperator.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.FormatConditionOperator.html") property of your _FormatCondition_ object to _GreaterEqual_.

After setting all the above-said properties, the final DsExcel code and Excel output look like below: 

```
    //Age greater than 60, set its interior color
    IFormatCondition? ageFormatCondition = worksheet.Range["B2:B11"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.GreaterEqual, 60) as IFormatCondition;
    ageFormatCondition!.Interior.Color = Color.FromArgb(255,251, 226, 213);
```

![Excel Conditional Formatting C#](//cdn.mescius.io/umb/media/yfbbvzkc/02-formatting.png?rmode=max&width=755&height=169)

Let’s try another example to mark patients who need a follow-up. Since the FollowUpRequired column uses **"Yes"** and **"No**" answers only, so just add separate rules for each answer using _Equal_ as _FormatConditionOperator_.

Use the following DsExcel code to change all the "Yes" entries to green and all the "No" entries to red:

```
    //Cell Value rule on FollowUpRequired column
    IFormatCondition? followUpRequiredCellValueRule = worksheet.Range["H2:H11"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "Yes") as IFormatCondition;
    followUpRequiredCellValueRule!.Font.Color = Color.DarkGreen;
    followUpRequiredCellValueRule = worksheet.Range["H2:H11"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "No") as IFormatCondition;
    followUpRequiredCellValueRule!.Font.Color = Color.DarkRed;
```

![Excel Conditional Formatting C#](//cdn.mescius.io/umb/media/aa2l51qc/03-formatting.png?rmode=max&width=761&height=166)

## <a id="data"></a>Data Bar Rule

The data bar rule adds color bars to the cells based on their values. These bars make it simpler to determine the highest and lowest values in your data. To implement this rule in DsExcel, create a new [IDataBar](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IDataBar.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IDataBar.html") object and add it to the FormatConditions collection. Once added, you have the flexibility to customize the bar color, type, border, and maximum/minimum values.

Let’s apply this rule to the _Treatment Cost_ column to easily identify which patient has the highest treatment cost. Use the [AddDatabar](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatConditions~AddDatabar.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatConditions~AddDatabar.html")() function to add a new IDataBar object to the FormatConditions collection. To enhance the look of these bars, we're painting them a gradient blue and adjusting their max/min length based on their range values.

Below is the DsExcel code to show data bars in the "Treatment Cost" column:

```
    //Data bar rule on Treatment Cost column
    IDataBar treatmentCostDataBarCondition = worksheet.Range["F2:F11"].FormatConditions.AddDatabar();
    treatmentCostDataBarCondition.MinPoint.Type = ConditionValueTypes.AutomaticMin;
    treatmentCostDataBarCondition.MaxPoint.Type = ConditionValueTypes.AutomaticMax;
    treatmentCostDataBarCondition.Direction = DataBarDirection.Context;
    treatmentCostDataBarCondition.BarFillType = DataBarFillType.Gradient;
    treatmentCostDataBarCondition.BarColor.Color = Color.FromArgb(255,166,201,236);
    treatmentCostDataBarCondition.BarBorder.Type = DataBarBorderType.Solid;
    treatmentCostDataBarCondition.BarBorder.Color.Color = Color.FromArgb(255,77,147,217);
    treatmentCostDataBarCondition.ShowValue = true;
```

![Excel Conditional Formatting C#](//cdn.mescius.io/umb/media/yzcfcclw/04-formatting.png?rmode=max&width=746&height=167)

## <a id="icon"></a>Icon Sets Rule

The icon sets rule displays graphical icons in the cells if they meet certain conditions. In this rule, you can define criteria for each icon and display it in the cell based on its value. This rule is designed for numerical data only. You can choose from 20 different sets of icons, including arrows, traffic lights, stars, and so on.

To use this rule in DsExcel, add the [IIconSetCondition](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IIconSetCondition.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IIconSetCondition.html") object to the FormatConditions collection. Once you've done that, set up different criteria for each icon within your IIconSetCondition object using its [IconCriteria](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IIconSetCondition~IconCriteria.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IIconSetCondition~IconCriteria.html") collection.

Let’s see how to show icons in the _Severity_ column to identify the patients that are critical and need urgent inspection. This column contains text values, and this rule applies to numbers only, so we first must convert this into numbers. You can use the VLOOKUP Excel function and dummy data to convert the text to a number. Then, with a custom format, you can make these numbers appear just like the original text.

Here is the code given below for implementing the same:

```
    worksheet.Range["J7:K9"].Value = new object[3, 2]
    {
         {"High",1 },
         {"Medium",0 },
         {"Low",-1}
    };
    worksheet.Range["I2:I11"].Formula2 = "=VLOOKUP(D2,$J$7:$K$9,2,0)";
    worksheet.Range["I2:I11"].Copy(worksheet.Range["I2:I11"], new PasteOption() { PasteType = PasteType.Values });
    worksheet.Range["I2:I11"].Copy(worksheet.Range["D2:D11"]);
    worksheet.Range["D2:D11"].NumberFormat = "\"High\";\"Low\";\"Medium\"";
    worksheet.Range["J7:K9, I2:I11"].Delete();
```

Now that we have converted the text data into numbers use the [AddIconSetCondition()](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatConditions~AddIconSetCondition.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatConditions~AddIconSetCondition.html") method to add the _IIconSetCondition_ object. You can use the Icon3Symbols enum type to show high, low, and medium severity items.

The DsExcel code below shows different icons for each Severity:

```
    //Icon set rule on Severity
    IIconSetCondition severityIconSetCondition = worksheet.Range["D2:D11"].FormatConditions.AddIconSetCondition();
    severityIconSetCondition.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
    severityIconSetCondition.IconCriteria[2].Value = 0;
    severityIconSetCondition.IconCriteria[2].Type = ConditionValueTypes.Number;
    severityIconSetCondition.IconCriteria[2].Operator = FormatConditionOperator.Greater;
    severityIconSetCondition.IconCriteria[1].Operator = FormatConditionOperator.GreaterEqual;
    severityIconSetCondition.IconCriteria[1].Value = 0;
    severityIconSetCondition.IconCriteria[1].Type = ConditionValueTypes.Number;
```

![Excel Conditional Formatting C#](//cdn.mescius.io/umb/media/1a3hynzj/05-formatting.png?rmode=max&width=754&height=170)

## <a id="three"></a>Three Color Scale Rule

The three color scale rule shades the cell with different colors according to its value. The cells get colored with a mix of three colors, showing the lowest, middle, and highest values. You can pick your own colors and decide what numbers should be the low, middle, and high points.

To use the threecolor scale rulevia DsExcel, you need to add the [IColorScale](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IColorScale.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IColorScale.html") object in the FormatConditions collection. After adding it, you can create specific rules for each color by working with the [ColorScaleCriteria](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IColorScale~ColorScaleCriteria.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IColorScale~ColorScaleCriteria.html") collection within your _IColorScale_ object.

Imagine we want to use different colors to indicate a patient's condition in the _TreatmentOutcome_ column. Like the icon set rule, the three color scale rule also works with numbers. So, we first need to change our data into numeric form.

Here's how you do it with DsExcel code:

```
    //Change the String Data into Numerical values to apply conditional formatting
    worksheet.Range["J7:K9"].Value = new object[3, 2]
    {
         {"Recovered",1 },
         {"Recovering",0 },
         {"Deteriorating",-1}
    };
    worksheet.Range["I2:I11"].Formula2 = "=VLOOKUP(G2,$J$7:$K$9,2,0)";
    worksheet.Range["I2:I11"].Copy(worksheet.Range["I2:I11"],new PasteOption() { PasteType = PasteType.Values});
    worksheet.Range["I2:I11"].Copy(worksheet.Range["G2:G11"]);
    worksheet.Range["G2:G11"].NumberFormat = "\"Recovered\";\"Deteriorating\";\"Recovering\"";
    worksheet.Range["J7:K9, I2:I11"].Delete();
```

Now that we have converted the text data into numbers, use the [AddColorScale()](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatConditions~AddColorScale.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/DS.Documents.Excel~GrapeCity.Documents.Excel.IFormatConditions~AddColorScale.html") method with _ColorScaleType.ThreeColorScale_ as a parameter to add the _IColorScale_ object. Then, adjust the colors and conditions to make it look how you want.

The DsExcel code given below shows different colors for each treatment result:

```
    //Three color scale rule on Treatment Outcome column
    IColorScale treatmentOutcomeThreeColorScaleRule = worksheet.Range["G2:G11"].FormatConditions.AddColorScale(ColorScaleType.ThreeColorScale);
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[0].Type = ConditionValueTypes.Number;
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[0].Value = -1;
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[0].FormatColor.Color = Color.FromArgb(255,251,226,213);
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[1].Type = ConditionValueTypes.Number;
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[1].Value = 0;
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[1].FormatColor.Color = Color.FromArgb(255, 192, 230, 245);
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[2].Type = ConditionValueTypes.Number;
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[2].Value = 1;
    treatmentOutcomeThreeColorScaleRule.ColorScaleCriteria[2].FormatColor.Color = Color.FromArgb(255, 218, 242, 208);
```

![Excel Conditional Formatting C#](//cdn.mescius.io/umb/media/dh1agg4j/06-formatting.png?rmode=max&width=752&height=167)

You can check out this [sample](https://cdn.mescius.io/umb/media/0b5lhtzn/conditionalformatting.zip) implementing all the above said rules. 

## Conclusion

In this blog, you’ve seen how to make your Excel data easier to read, analyze, and present using conditional formatting in a .NET app. With Document Solutions for Excel, you can take your Excel skills to the next level by adding your own code.  
Check out our [demos](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/conditionalformatting "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/conditionalformatting") to discover the cool features of DsExcel that can improve your Excel experience.

Have any questions about this tutorial? Drop them in the comments below!

More References:

*   [Documentation .NET](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ApplyConditionalFormatting.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ApplyConditionalFormatting.html")
*   [Product Demo JAVA](https://developer.mescius.com/document-solutions/java-excel-api/demos/conditionalformatting "https://developer.mescius.com/document-solutions/java-excel-api/demos/conditionalformatting")
*   [Documentation JAVA](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/ApplyConditionalFormatting.html "https://developer.mescius.com/document-solutions/java-excel-api/docs/online/ApplyConditionalFormatting.html")