Finding duplicate values in Excel columns is, on closer inspection, not such a simple task. It usually comes up when you can’t just remove duplicate cells, because first you need to decide which of the repeated values to delete.
Finding duplicates can mean very different things, and depending on the input, it’s done differently. To pick the right method, first answer a few questions.
Key questions to ask when finding duplicates
- What exactly repeats: words inside cells, entire cells or entire rows?
- If cells, which ones count as duplicates: all except the first occurrence, or including it?
- Are “Apple” and “Apple ” (with an extra space) duplicates? What about “apple” and “APPLE”?
- Where do we look: in one column, between two columns or across several?
- How do we want to see them: highlighted with color, marked with TRUE/FALSE, or with the number of repetitions next to each value?
Let’s start with simple examples.
Using Conditional Formatting to highlight duplicates
Conditional Formatting is the built-in tool for highlighting duplicate cells. It has a number of ready-made rules, including one for duplicate values. You’ll find it on the Home tab.
How to use it
- Select the range where you want to find duplicates.
- Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
- Choose the formatting for the matching cells: a preset one or your own.
The caveat
It’s important to understand that the rule finds duplicates within the entire selected range, so it may not work for comparing two columns. A duplicate inside one column is enough, and the rule highlights it, even though the second column has no such value:
This behavior is not obvious and is often forgotten. If you then plan to delete the repeats, you may lose both copies from the same column.
How to avoid it if you need duplicates between columns? The simplest way is to remove duplicates inside each column first. But there are other solutions, read on.
Using formulas for greater control
Your own formula for checking duplicates has several advantages. The only difficulty is building it, and I’ve done that for you :)
Finding duplicates within a single range
To check whether a range contains any repeated values at all, use this array formula based on the COUNTIF and SUMPRODUCT functions:
=SUMPRODUCT(COUNTIF(A1:A100,A1:A100)-1)>0
COUNTIF counts how many times each value occurs in the range, and the formula returns TRUE if at least one of them occurs more than once.
To mark each cell instead of the whole range, put this formula next to the first cell and fill it down. It returns TRUE for every value that occurs more than once:
=COUNTIF($A$1:$A$100,A1)>1
And this one marks only the second and following occurrences, leaving the first one unmarked. That’s exactly what you need before deleting:
=COUNTIF($A$1:A1,A1)>1
Why bother with formulas if Conditional Formatting does the same? Because a formula is easy to modify and improve.
For example, you can add the TRIM function to it. Then it also finds duplicates that differ only in invisible extra spaces:
=SUMPRODUCT(--(TRIM(A1)=TRIM($A$1:$B$4)))>1
This formula is slightly different: it checks how many times the value of one cell occurs in the range. If you add it as a Conditional Formatting rule (Home → Conditional Formatting → New Rule → Use a formula), it detects these implicit duplicates:
Note one detail: the range is fixed with dollar signs ($A$1:$B$4), and the checked cell (A1) is not. That’s what lets the rule check each cell against the whole range. Here is the result: the cells are highlighted even though they differ in the number of spaces.
Finding duplicates between two columns
To find the values of column A that also occur in column B, regardless of their position, count them in the other column:
=COUNTIF($B$1:$B$100,A1)>0
TRUE means the value from A1 is somewhere in column B. This is how you compare two lists without the caveat of Conditional Formatting described above.
Case-sensitive duplicates
COUNTIF ignores case: for it, “apple” and “APPLE” are the same. If case matters, use the EXACT function:
=SUMPRODUCT(--EXACT(A1,$A$1:$A$100))>1
List of duplicates in Excel 365
In Excel 365, you can get a separate list of the values that repeat, each of them once:
=UNIQUE(FILTER(A1:A100,COUNTIF(A1:A100,A1:A100)>1))
All these formulas work, but each of them has to be typed, the references fixed with dollar signs where needed, filled down, and then the helper column removed.
A faster solution: the !SEMTools add-in
I used to spend a lot of time on the methods above. I didn’t like any of them, and the reason was the same: it’s simply slow. So I made dedicated procedures for finding and deleting duplicates in my !SEMTools add-in for Excel. Select the column, click the procedure, and each cell gets TRUE or FALSE right in place. Let me show you how they work.
Find duplicate cells in a column, except the first
The procedure marks all the second, third and following repeated values in a column. The first occurrence stays unmarked, so after filtering you can safely delete the marked rows.
Find all duplicate cells in a column, including the first
Often you need to find all repeated cells in a column, including the first one, to filter and review all of them.
Find duplicates in a column ignoring extra spaces
If phrases that differ only in the number of spaces between or after words count as duplicates, first get rid of the extra spaces, and then run the same search. For the first step, !SEMTools has a separate tool to remove extra spaces:
The add-in can also find duplicates between two ranges and implicit duplicates, phrases with the same words in a different order, like “buy sofa” and “sofa buy”. They are described in the article on implicit duplicates in Excel.
Why duplicates aren’t found: common mistakes
- Obvious duplicates are not found. The values differ in spaces, invisible characters or case. Remove extra spaces first or use the TRIM version of the formula.
- Numbers and the same numbers stored as text. For Excel, 1024 and “1024” are different values. Convert the column to one type before searching.
- Both copies were deleted. Conditional Formatting highlights all occurrences, including the first one. Mark only the second and following occurrences before deleting.
Find duplicates in a column except or including the first occurrence, right in place, without COUNTIF formulas or helper columns.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: which way to find duplicates
I hope you found what you were looking for on this page and were able to find the duplicates in your data. Conditional Formatting is fine for a quick look at one column, formulas give control over spaces, case and comparing columns, but each of them takes time to build and fill down. For regular work with lists, the !SEMTools procedures mark duplicates in a couple of clicks right in place.
You may also be interested in related operations: