I have already received an Excel file where each value appeared at least twice by mistake: duplicated orders, duplicate contacts, repeated dates… The result? Skewed analyses, bank statements trapped by inconsistent figures, and above all a lot of time wasted manually tracking these duplicates. Thanks to conditional formatting, I can now highlight them with a simple click and make my tables clear in an instant. In this article, I explain how to set up this feature, but also how to adapt it to your most specific needs.
Why identify and highlight duplicates?
The risks associated with duplicated values
Imagine that your order tracking file shows the same reference twice: you risk placing your order twice with the supplier. Or worse: you send two invoices to the same client. These small errors, often invisible at first glance, can be costly, both in time and money.
The advantage of visual formatting
With conditional formatting, you instantly get a color code that catches your eye. No need to scroll through hundreds of lines, the computer highlights what stands out. At a glance, you know where to intervene.
Using conditional formatting to highlight duplicates
Accessing the conditional formatting tool
Go to the Home tab of the Excel ribbon. You will find the Conditional Formatting button there. A simple click displays a dropdown menu, where several types of rules are hidden, including those for spotting duplicates.
Configuring the duplicate detection rule
In the menu, choose Duplicate Values Rules. A dialog box opens and offers you to select your cell range (for example A2:A1000). Then, you define the formatting: light red fill, bold text, or any other style you prefer. Confirm, and immediately see your duplicates highlighted.
Using a custom formula
For more complex scenarios, go through New Rule → Use a formula to determine which cells to format. For example, if you want to highlight in column A every recurring value, enter:
=NB.SI($A:$A;A2)>1
This formula calls the COUNTIF function, which counts the number of occurrences of the value present in A2 across the entire column. If it appears more than once, the formatting applies.
Tips and best practices
- Structured table: convert your range into an Excel table (Ctrl+T) so that conditional formatting automatically follows your added rows.
- Ignore certain values: if you don’t want to highlight empty cells or headers, adapt your formula with a condition
ET(A2<>"";… - Combine multiple rules: you can stack two conditional formats, for example to distinguish simple duplicates from triplets.
- Refresh formatting: sometimes, after many changes, Excel needs a manual refresh via Refresh (right-click on the rule).
- Extract duplicates: once identified, you can filter and copy them to another sheet for in-depth analysis, or bring them up using a VLOOKUP based on criteria.
Avoiding the “smudging” effect
If your entire column turns red, it might be wiser to adopt a more discreet formatting: for example, a pastel background or simply a cell border. Your table will remain readable and professional.
Concrete examples
| Original list | Condition formula | Visual result |
|---|---|---|
| Alice |
=NB.SI($A:$A;A2)>1 |
Alice, Bob and Alice are highlighted |
| Bob | ||
| Alice |
Extending the method to other needs
Beyond simple duplicates, you can adapt the technique to highlight:
- Values below or above an average (SUMIF or SUMIFS to support calculating reference totals).
- Differences between two columns (useful in accounting).
- Past or upcoming dates, for more responsive project tracking.
Tip: if your data contains extra spaces or lowercase/uppercase letters, start by cleaning them with TRIM or UPPER to avoid invisible duplicates.
Conclusion
Conditional formatting in Excel is a powerful tool to quickly identify duplicates. In just a few clicks, you gain clarity and precision while reducing the risk of errors. Don’t hesitate to experiment with custom formulas, structure your data in tables, and combine multiple rules for ever more refined analyses.