Tutorials
Spire.XLS
Program Guide
Conditional Formatting

Highlight Top and Bottom Ranked Values in Excel Using C#

One of the best uses of conditional formatting is to quickly highlight top or bottom ranked values in a large set of data. In this article, we’re going to show you how to highlight top and bottom ranked values in an Excel worksheet using Spire.XLS and conditional formatting.

The input.xlsx file we used for demonstration:

Highlight Top and Bottom Ranked Values in Excel Using C#

Detail steps:

Step 1: Initialize an object of Workbook class and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Apply conditional formatting to range "C2:C5" to highlight the top 2 ranked values.

ConditionalFormatWrapper format1 = sheet.Range["C2:C5"].ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.TopBottom;
format1.TopBottom.Type = TopBottomType.Top;
format1.TopBottom.Rank = 2;
format1.BackColor = Color.Red;

Step 4: Apply conditional formatting to range "D2:D5" to highlight the bottom 2 ranked values.

ConditionalFormatWrapper format2 = sheet.Range["D2:D5"].ConditionalFormats.AddCondition();
format2.FormatType = ConditionalFormatType.TopBottom;
format2.TopBottom.Type = TopBottomType.Bottom;
format2.TopBottom.Rank = 2;
format2.BackColor = Color.ForestGreen;

Step 5: Save the file.

workbook.SaveToFile("TopBottomValues.xlsx", ExcelVersion.Version2013);

Screenshot:

Highlight Top and Bottom Ranked Values in Excel Using C#

Full code:

//Initialize an object of Workbook class
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Input.xlsx");

//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//Apply conditional formatting to range “C2:C5” to highlight the top 2 values
ConditionalFormatWrapper format1 = sheet.Range["C2:C5"].ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.TopBottom;
format1.TopBottom.Type = TopBottomType.Top;
format1.TopBottom.Rank = 2;
format1.BackColor = Color.Red;

//Apply conditional formatting to range “D2:D5” to highlight the bottom 2 values
ConditionalFormatWrapper format2 = sheet.Range["D2:D5"].ConditionalFormats.AddCondition();
format2.FormatType = ConditionalFormatType.TopBottom;
format2.TopBottom.Type = TopBottomType.Bottom;
format2.TopBottom.Rank = 2;
format2.BackColor = Color.ForestGreen;

//Save the file
workbook.SaveToFile("TopBottomValues.xlsx", ExcelVersion.Version2013);