How to Highlight Duplicates in Excel
Last updated: 2026-09-06
⚡ Quick Answer
Conditional formatting rules allow you to flag redundant records visually before database entry.
=COUNTIF($A$2:$A$100, A2) > 1📊 Visual Demo
HOME · INSERT · FORMULAS · DATA
fx =COUNTIF($A$2:$A$100, A2) > 1
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1 | INV-1002 | Duplicate Check | =COUNTIF(A:A, A2)>1 | Highlighted Red |
| 2 | 2 | INV-1003 | Unique Record | — | Normal Fill |
| 3 | 3 | Action | Audit Flag | Verified | Risk Mitigated |
🧠 Deep Dive
Conditional formatting rules allow you to flag redundant records visually before database entry.
Key Insights
- Import problems are often hidden non-breaking spaces.
- Numbers stored as text break SUM and lookups.
Common Mistakes
- Running VLOOKUP against uncleaned keys.
- Using Find & Replace on spaces blindly.
Pro Tips
- Add a helper column with LEN().
- Standardize case with UPPER.
FAQ
What does this formula do in plain English?
Conditional formatting rules allow you to flag redundant records visually before database entry.
Why is my formula not working?
Running VLOOKUP against uncleaned keys.
Does this work in Google Sheets?
Yes, this syntax is fully compatible with Google Sheets.
What is the most common mistake?
Using Find & Replace on spaces blindly.
Related Guides
How to Use the UNIQUE Function in ExcelHow to Remove Extra Spaces in ExcelHow to Split Text in ExcelHow to Use IS Data Validation FunctionsExcel Error Hub
Need Complete Playbooks?
Download our verified manuals covering ERP Cleanup and Modeling.
Explore Playbook Series (From ₹199)