How to spot duplicates using a formula instead of manual checking

The fastest way to find duplicates in Excel is to use the COUNTIF function, which counts how many times each value appears in a range. When COUNTIF returns a number higher than 1, that value is a duplicate. You write the formula once, copy it down your column, and Excel marks every duplicate automatically — no clicking through hundreds of rows by hand.

This approach works because COUNTIF does the counting for you. Instead of scanning visually or using Excel's built-in conditional formatting (which highlights duplicates but doesn't label them), a formula gives you a number you can sort, filter, or use to decide what to do next. If you have a list of customer IDs, product codes, or email addresses and need to know which ones repeat, a formula is usually faster than any other method.

Key Takeaways

  • The COUNTIF formula counts how many times each value appears; any count above 1 means that value is a duplicate.
  • You write the formula in one cell, then copy it down to check every row in your list at once.
  • A formula result of 1 means the value appears only once; 2 or higher means it repeats somewhere in your data.
  • You can sort or filter by the formula column afterward to group all duplicates together and see them at a glance.

Setting up the COUNTIF formula for your data

Start by opening your spreadsheet and identifying which column holds the values you want to check. Let's say your data is in column A, rows 2 through 100 (row 1 is your header). Click on an empty column next to your data — column B works well — and click on cell B2.

Type this formula: =COUNTIF($A$2:$A$100,A2)

The dollar signs ($) lock the range so it stays the same when you copy the formula down. The A2 at the end (without dollar signs) changes for each row, so the formula checks each value against the entire list. Press Enter. Excel shows a number — usually 1 if that value appears only once, or 2, 3, or higher if it repeats.

If your data ends at a different row, change the 100 to match your actual last row. If your data is in a different column, replace A with that column letter. The formula works the same way.

Copying the formula down to check all rows

Click back on cell B2 (the cell where you just typed the formula). You'll see a small square in the bottom-right corner of the cell — that's the fill handle. Click and drag it down to the last row of your data. Excel copies the formula to every cell and adjusts the row number automatically.

Alternatively, click B2, then hold Shift and click on the last cell in column B where you want the formula to go. Then press Ctrl+D (or Cmd+D on Mac). Excel fills down the formula to all selected cells at once. Either method takes seconds and checks your entire list in one action.

Now column B shows a count for every row. Scan down and look for any number higher than 1. Those are your duplicates.

Filtering to see only the duplicates

Once your formula column is complete, you can filter to show only rows where the count is 2 or higher. Click on any cell in your data range, then go to the Data menu and click AutoFilter. Small dropdown arrows appear in your header row.

Click the dropdown arrow in your formula column (column B). Uncheck the box next to 1, so only counts of 2 and above show. Click OK. Excel hides all the rows with unique values and displays only the duplicates. Now you can see at a glance which values repeat and how many times.

When you're done reviewing, click the dropdown arrow again and select "Show All" to bring back the hidden rows. The formula stays in place, so you can filter again whenever you need to.

Using a formula to mark duplicates with text labels

If you prefer to see the word "Duplicate" or "Unique" instead of a number, use an IF statement combined with COUNTIF. In cell B2, type: =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")

This formula counts the value, and if the count is greater than 1, it displays "Duplicate". Otherwise it displays "Unique". Copy this formula down the same way as before. Now your column shows a clear label instead of a number, which some people find easier to read at a glance.

You can change the text inside the quotation marks to anything you want — "Yes"/"No", "Repeat"/"Single", or any other label that makes sense for your work. The logic stays the same: if the count is more than 1, show the first label; if not, show the second.

Finding duplicates across multiple columns

If you need to check whether entire rows repeat (not just a single column), you can combine multiple columns into one formula. For example, if you want to find rows where both the Name column (A) and Email column (B) match another row, use: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)

COUNTIFS (with an S) works like COUNTIF but checks multiple conditions at once. This formula counts how many rows have both that name AND that email together. If the result is 2 or higher, that combination appears more than once.

You can add more conditions by adding more column pairs. For three columns: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2) This is useful when you're looking for completely identical records, not just repeated values in one field.

What to do once you've found the duplicates

After you've identified which values or rows repeat, you have several options. You can delete the duplicates, keep only the first occurrence, or flag them for review. Excel's Remove Duplicates feature (on the Data menu) can delete them automatically, but a formula approach gives you more control — you can see exactly what's being removed before you act.

Some people sort by the formula column so all duplicates sit together, then manually review them to decide which copy to keep. Others export the duplicate rows to a separate sheet for investigation. The formula itself doesn't delete anything; it just identifies what's there. What you do next depends on why the duplicates exist and whether you need to preserve any of them.

Frequently Asked Questions

What if my data has blank cells?

COUNTIF treats blank cells as a value, so if you have empty cells in your range, they'll be counted as duplicates if more than one cell is blank. If this creates noise, you can filter them out separately or use a formula that ignores blanks: =IF(A2="","",COUNTIF($A$2:$A$100,A2)) This shows nothing for blank cells instead of counting them.

Can I use this formula on text that has different capitalization?

COUNTIF is not case-sensitive by default, so "John", "JOHN", and "john" are all treated as the same value and counted as duplicates. If you need to treat different cases as separate values, you'll need a more complex formula using SUMPRODUCT, but for most business data, the standard COUNTIF behavior is what you want.

What's the difference between COUNTIF and COUNTIFS?

COUNTIF checks one condition (one column). COUNTIFS checks multiple conditions at once (multiple columns). Use COUNTIF when you're looking for duplicates in a single field, and COUNTIFS when you need to match across several fields to find identical rows.

Does the formula update automatically if I add new data?

No. If you add new rows below your original range, the formula won't include them. You'll need to extend the range in the formula (change the 100 to 150, for example) or copy the formula down to the new rows. For constantly changing data, some people use a larger range than they currently need, so new entries are automatically included.

Can I delete the formula column after I've found the duplicates?

Yes. Once you've identified and handled the duplicates, you can delete the formula column. The duplicates themselves remain in your data — the formula was just a tool to find them. If you think you'll need to check again later, you can keep the column and just hide it instead of deleting it.