Highlight Duplicate and Unique Values in Excel Using C#

Using Excel conditional formatting, we can quickly find and highlight the duplicate and unique values in a selected cell range. In this article, we’re going to show you how to programmatically highlight duplicate and unique values with different colors using Spire.XLS and conditional formatting.

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: Use conditional formatting to highlight duplicate values in range "A2:A10" with IndianRed color.

ConditionalFormatWrapper format1 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.DuplicateValues;
format1.BackColor = Color.IndianRed;

Step 4: Use conditional formatting to highlight unique values in range "A2:A10" with Yellow color.

ConditionalFormatWrapper format2 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition();
format2.FormatType = ConditionalFormatType.UniqueValues;
format2.BackColor = Color.Yellow;

Step 5: Save the file.

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

Screenshot:

Highlight Duplicate and Unique Values in Excel Using C#

Full code:

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

                //Load the Excel file
                Workbook workbook = new Workbook();
                workbook.LoadFromFile("Input.xlsx");

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

                //Use conditional formatting to highlight duplicate values in range "A2:A10" with IndianRed color
                ConditionalFormatWrapper format1 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition();
                format1.FormatType = ConditionalFormatType.DuplicateValues;
                format1.BackColor = Color.IndianRed;

                //Use conditional formatting to highlight unique values in range "A2:A10" with Yellow color
                ConditionalFormatWrapper format2 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition();
                format2.FormatType = ConditionalFormatType.UniqueValues;
                format2.BackColor = Color.Yellow;

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

        }
    }
}

Highlight Duplicate and Unique Values in Excel Using C#

See Also