How to quickly find duplicate values in Excel

I think the best way to find duplicate records is using Conditional Formatting and Filter. It’s easy, simple and fast.

The following table contains some duplication in column “Name”. We want to find the people who are there more than once and show rows where it is.

table with duplicate values

Highlight duplicates by Conditional Formatting

Select the values in the column “Name” and go to the menu Conditional Formatting >> Highlight Cells Rules >> Duplicate Values…

menu Conditional Formatting

Choose the cell formatting and look at the colored cells in the table.

highlighted duplicate records

Filter duplicate values

In Excel is possible to filter row by the cell color or by the font color. Insert Filter into the table. On the column “Name” choose Filter by Color >> color.

Filter by color

Now you see only the duplicate values.

Only duplicates

You can sort the table for better orientation in records.

Sorting in Filter


The result

Add a comment »6 comments to this article

  1. What is the best way how to mark duplicities? I am looking for very quick solution for big data table.


  2. Is there a simple way how to copy duplicity?


  3. I required the Excercise file


  4. You can highlight or delete duplicated or identical rows easily using Dose for Excel Add-In which provides more than +100 Features, check their website in below:


  5. How to delete duplicates in Excel?


    • You have to find them first 🙂


Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress