How to Find Duplicate Values in Excel Using a Formula

Duplicate data is one of the most common problems in spreadsheets — and one of the easiest to miss. Whether you're managing a customer list, tracking inventory, or reconciling financial records, undetected duplicates can quietly distort your results. Excel offers several formula-based approaches to surface them, and understanding how each one works helps you choose the right tool for your data.

Why Formulas Beat Manual Scanning

Visually scanning a column for duplicates works fine at 20 rows. At 2,000, it's unreliable. Formulas are deterministic — they apply the same logic to every cell, every time, without fatigue. They also adapt dynamically: add new data, and the formula re-evaluates instantly.

The two most widely used formula approaches are COUNTIF-based detection and conditional flagging. Each serves a slightly different purpose.

The Core Method: COUNTIF for Duplicate Detection

The COUNTIF function counts how many times a value appears in a range. When that count exceeds 1, you have a duplicate.

Basic syntax:

=COUNTIF(range, criteria) 

Applied to duplicate detection:

=COUNTIF($A$2:$A$100, A2) 

Place this in column B, starting at B2, and drag it down. Each cell returns a number. A result of 1 means the value is unique. A result of 2 or more means it appears more than once.

To make this more readable, wrap it in an IF statement:

=IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "Unique") 

Now your helper column labels each row clearly. This is the most portable approach — it works in Excel 2010 through Microsoft 365, and in Google Sheets with identical syntax.

Locking the Range — and Why It Matters

Notice the dollar signs in $A$2:$A$100. These create an absolute reference, meaning the range doesn't shift as you copy the formula down. Without them, the range slides with each row and your counts become inaccurate. The cell reference A2 (without dollar signs) remains relative, so it updates correctly as you move down the column.

Finding the First Occurrence vs. All Occurrences 🔍

There's an important distinction worth understanding: do you want to flag every instance of a duplicate, or only the second and subsequent occurrences?

Flag all instances:

=IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "Unique") 

This marks both the original entry and any repeats.

Flag only the repeats (keep the first):

=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "Unique") 

The subtle difference here is in the range: $A$2:A2 uses a mixed reference where the start of the range is locked but the end expands as you move down. The first time a value appears, the range only contains that single occurrence — count is 1, no flag. When it appears again lower in the list, the expanded range now contains two instances — count exceeds 1, flagged.

This distinction matters significantly depending on your goal. Deduplication workflows often want to preserve the first record and remove subsequent ones, so flagging only the repeats is cleaner.

Checking Duplicates Across Multiple Columns

Single-column duplicate checks are straightforward. Multi-column checks — where a duplicate means two rows share the same combination of values — require a slightly different approach.

Using COUNTIFS:

=IF(COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)>1, "Duplicate", "Unique") 

COUNTIFS applies multiple criteria simultaneously. In this example, a row is flagged only if both column A and column B match another row — useful for scenarios like checking whether the same customer placed the same order on the same date.

Alternatively, you can concatenate columns into a helper column and run a standard COUNTIF on that:

=A2&"|"&B2 

Then apply COUNTIF to the concatenated column. The pipe character (|) acts as a separator to prevent false matches (e.g., "John" + "son" shouldn't match "Johns" + "on").

Using EXACT for Case-Sensitive Duplicate Detection

Standard COUNTIF is case-insensitive — it treats "Apple", "apple", and "APPLE" as the same value. For most use cases, that's fine. But if case matters in your data (product codes, usernames, file paths), you need a different approach.

Case-sensitive duplicate check using SUMPRODUCT and EXACT:

=IF(SUMPRODUCT((EXACT($A$2:$A$100, A2))*1)>1, "Duplicate", "Unique") 

EXACT compares strings character by character, including case. SUMPRODUCT handles the array logic without needing Ctrl+Shift+Enter. This formula is heavier on processing for large datasets, but delivers precision that COUNTIF cannot.

Variables That Affect Which Formula Works for You

When Formulas Interact With Your Data Structure 📊

Formula-based duplicate detection works on the data as Excel sees it — which means formatting differences, hidden spaces, and data types can affect results. The number 42 and the text "42" are not the same value to a formula. Leading or trailing spaces (common in imported data) cause two visually identical entries to be treated as distinct.

Running TRIM on your data before applying duplicate formulas — or nesting TRIM inside your COUNTIF — can eliminate false negatives caused by whitespace:

=COUNTIF($A$2:$A$100, TRIM(A2)) 

Whether that preprocessing step is necessary depends entirely on where your data came from and how clean it already is. Imported CSVs, CRM exports, and manually entered lists each carry different risk profiles for these kinds of inconsistencies — and only someone looking at the actual dataset can assess that accurately.