You import a customer list from two systems and suspect repeated emails, but with three thousand rows you will not check them one by one. You need to see them before deleting anything.
How to use Duplicates Formula step by step
1. Choose what you need
Flag every copy, flag from the second occurrence, count appearances, build the colour rule or pull out unique values.
2. Enter the range
Give the column you want to check. The tool pins the references with $ so you can drag the formula without the range sliding.
3. Copy it into your sheet
Paste the formula beside your data, or into the rules manager if you chose highlighting, and drag it down.
The Step Count: Traditional Friction vs. KorexTools
Hunting repeats by eye in a long column (4 steps)
- Sort the column alphabetically.
- Walk down it looking for equal neighbours.
- Colour each repeat you find.
- Miss the ones sitting far away because of a trailing space.
With the KorexTools “Duplicates Formula” builder (3 steps)
- Pick the variant and the range.
- Copy the formula beside the data.
- Filter on the flag and decide what to delete.
Repeated does not always mean surplus: you decide which copy stays
COUNTIF over the whole column flags every copy, the first one included, which makes it a good review tool. If you are cleaning up instead, flag only from the second occurrence using a range that grows row by row: the first stays untouched and the later ones are what you can delete without losing the record.
To paint them, conditional formatting wants a formula with the column fixed and the row free, such as $A2, and the builder writes it with that detail settled. If your Excel has UNIQUE, the clean list appears without touching the original data, and its extra arguments let you keep only what appears once.
Which variant fits which task
- •Reviewing before deleting: flag every copy.
- •Cleaning while keeping the first row: flag from the second occurrence.
- •A new list without touching the original: UNIQUE, or UNIQUE with both extra arguments to keep what shows up once.
A column holding Ana, Luis and Ana
| Variant | Formula in A2 | Result in the three rows |
|---|---|---|
| Flag all copies | =IF(COUNTIF($A$2:$A$4,A2)>1,"Duplicate","Unique") | Duplicate, Unique, Duplicate |
| From the second | =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Unique") | Unique, Unique, Duplicate |
| List without repeats | =UNIQUE($A$2:$A$4) | Ana, Luis |
Common Mistakes and How to Avoid Them
- ⚠️Applying the highlight to a single cell instead of the whole range.
- ⚠️Deleting the first copy and losing the row that carried more data.
Frequently Asked Questions
How do I flag only the surplus rows?
Choose "Flag only from the second occurrence". The first copy stays Unique and the later ones come out as Duplicate, so you can filter on that label and delete those rows.
Does the highlight rule go in a cell?
No. It goes in Conditional Formatting, New Rule, "Use a formula". Apply it to the same range you are checking and pick the fill colour.
Does UNIQUE work in every version?
Only in Microsoft 365 and Excel 2021 onwards. In earlier versions use the COUNTIF-based options, which work everywhere.
What I would do if I were in your shoes
«Before removing a duplicate, check that both rows say the same thing: sometimes the repeat carries the newer value.» — DataKorex Engineering Team.