How to Find and Highlight Duplicate and Unique Values in Excel with Python

When registration forms from several channels are merged, a repeated email address usually means someone submitted twice. When an inventory list is reconciled, a duplicated product code means the ledger holds two records for the same item. And when hunting for data-entry mistakes, the interesting cells are often the unique values: an entry that appears exactly once is frequently the result of a typo or an omission. In all three cases, scanning thousands of rows by eye is not realistic.

Excel's built-in "Conditional Formatting → Duplicate Values" feature handles this, but it is an interactive workflow: every new file means re-selecting the range and redefining the color rule. Python turns that judgement into a script that can be applied to any number of worksheets. This article shows how to find and highlight duplicate and unique values in Spire.XLS for Python.

Why Check Duplicates in a Script

• Batch execution: dozens of worksheets or files can be processed with the same rule set, without clicking through each dialog.
• Reusable rules: the color, range, and comparison type all live in the code, so switching datasets means changing a filename.
• Traceable results: the script writes the marked output to a new file and leaves the source data untouched for comparison.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

from spire.xls import *
from spire.xls.common import *

Two Dedicated Conditional Format Methods

The conditional formatting entry point is the worksheet's ConditionalFormats collection. A general rule is built by calling Add() to create the rule, AddRange() to set the area it applies to, and AddCondition() to attach a condition whose FormatType determines the comparison. Duplicate and unique values have two more explicit methods that skip the FormatType assignment altogether:

Method	What it highlights
AddDuplicateValuesCondition()	Values that occur two or more times in the range
AddUniqueValuesCondition()	Values that occur exactly once in the range

Both evaluate within the area passed to AddRange(). In other words, whether a value counts as a duplicate is decided by the selected range alone, not by anything outside it. Once the condition exists, FillPattern, BackColor, and FontColor control how the matching cells look.

Highlighting Duplicate Values

The example below flags repeated student IDs in an exam sheet with a solid fill:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Template_Xls_6.xlsx")
sheet = workbook.Worksheets[0]

# Create a conditional formatting rule and set its range
conds = sheet.ConditionalFormats.Add()
conds.AddRange(sheet.Range["C2:C10"])

# Attach the duplicate-values condition
cf = conds.AddDuplicateValuesCondition()
cf.FillPattern = ExcelPatternType.Solid
cf.BackColor = Color.get_IndianRed()

workbook.SaveToFile("HighlightDuplicates.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

AddDuplicateValuesCondition() takes no arguments — the method name is the condition. FillPattern = ExcelPatternType.Solid selects a solid fill, and BackColor supplies the actual color. Setting a color without setting the fill pattern is the most common reason a conditional format appears to do nothing in Excel, so both properties belong together. To recolor the text of the duplicates as well, add cf.FontColor = Color.get_White().

Marking Duplicates and Unique Values Together

The unique-value rule is symmetric: swap in AddUniqueValuesCondition(). Applying both rules to the same range colors every non-empty cell in that range — one color for values seen two or more times, another for values seen once — which makes the distribution of a column easy to read at a glance.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Template_Xls_6.xlsx")
sheet = workbook.Worksheets[0]

# Rule 1: duplicates in Indian red
xcfs = sheet.ConditionalFormats.Add()
xcfs.AddRange(sheet.Range["C2:C10"])
dup = xcfs.AddDuplicateValuesCondition()
dup.FillPattern = ExcelPatternType.Solid
dup.BackColor = Color.get_IndianRed()

# Rule 2: unique values in yellow
xcfs1 = sheet.ConditionalFormats.Add()
xcfs1.AddRange(sheet.Range["C2:C10"])
uniq = xcfs1.AddUniqueValuesCondition()
uniq.FillPattern = ExcelPatternType.Solid
uniq.BackColor = Color.get_Yellow()

workbook.SaveToFile("DuplicateAndUnique.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The important detail is that each rule needs its own ConditionalFormats.Add() call before AddRange(). Attaching the second condition to the first rule's collection compiles without complaint but produces the wrong range and color — a mistake that also appears in sample code in the wild, so it is worth checking that each AddRange() is called on the object that owns the condition.

Applying the Rules to Several Worksheets

A ledger usually holds one sheet per month with identical structure, so a loop is enough to reuse the rules:

for i in range(workbook.Worksheets.Count):
    sheet = workbook.Worksheets.get_Item(i)

    xcfs = sheet.ConditionalFormats.Add()
    xcfs.AddRange(sheet.Range["C2:C10"])
    cf = xcfs.AddDuplicateValuesCondition()
    cf.FillPattern = ExcelPatternType.Solid
    cf.BackColor = Color.get_IndianRed()

Worksheets.Count returns the number of sheets and get_Item(i) fetches each one by index. When the sheets differ in width, replacing the fixed C2:C10 range with a dynamic one such as sheet.AllocatedRange is safer.

Practical Tips

• Do not skip FillPattern: a color without a fill pattern may render as nothing in Excel.
• The range decides the verdict: with a range of A2:B100, rows that repeat across columns also count as duplicates; restrict the range to a single column to deduplicate on that column only.
• Watch rule count: larger ranges and more rules mean more work for Excel when the file opens, so cover only the area that actually holds data.
• Release resources: call workbook.Dispose() after each workbook, and in batch jobs load, process, and save one file at a time.

Conclusion

This article covered how to find and highlight duplicate and unique values in Excel with Python: creating a rule with ConditionalFormats.Add(), limiting its scope with AddRange(), marking the two categories through AddDuplicateValuesCondition() and AddUniqueValuesCondition(), and controlling the appearance with FillPattern and BackColor. Because the comparison is scoped to the selected range, the same code works as a pre-cleaning duplicate check and, wrapped in a worksheet loop, covers an entire workbook.