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:

using Spire.Xls;
using System.Drawing;
namespace HighlightValues
{
    class Program
    {
        static void Main(string[] args)
        {
            {

                //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);

            }
        }
    }
}