Digital Skills Hub
Menu

How to Find Duplicate Records in Excel With COUNTIF and COUNTIFS

Hands reviewing documents at a desk
Photo via Pexels

Two people can have the same name. One customer can have two orders on the same day. Before cleaning a spreadsheet, decide what a repeated value actually means. A duplicate flag is a cue to check the records against the original source.

This walkthrough uses a fictional order log. The same technique works for class registers, sign-up lists and inventory files.

Define the columns that identify a record

Suppose each row represents an order and each order has a unique ID. Repeat customer names are expected. An order ID that appears twice is worth reviewing, because either the order was entered twice or the sheet records separate items from the same order. Check what one row represents before choosing a rule.

Enter this data in A1:D6. The day values represent consecutive days in one sample month.

Order ID Customer Day Amount
ORD-101 Alex 12 28
ORD-102 Maya 12 15
ORD-101 Alex 12 28
ORD-103 Alex 12 28
ORD-104 Sam 13 20

Flag repeated order IDs

Put ID check in E1. Enter this formula in E2 and copy it to E6:

=IF(A2="","Missing ID",IF(COUNTIF($A$2:$A$6,A2)>1,"Review ID",""))

Rows 2 and 4 should show Review ID. Row 5 is an important test. Its customer, day and amount match the entries for ORD-101, but its ID is ORD-103. If the source lists two orders, keep both.

The dollar signs fix the lookup range. When you add new orders to this practice sheet, extend the formula range so it includes the new rows.

Compare more than one field when IDs are missing

If you receive a file without order IDs, look for rows that match across several fields. Put Possible match in F1 and enter this in F2:

=IF(OR(B2="",C2="",D2=""),"Incomplete record",IF(COUNTIFS($B$2:$B$6,B2,$C$2:$C$6,C2,$D$2:$D$6,D2)>1,"Review match",""))

Copy the formula through F6. It flags rows 2, 4 and 5 because all three share the same customer, day and amount. Comparing that result with the ID check shows the trade-off: the second method finds more candidates and requires extra evidence before you remove any record. In a real order log, compare the full date so transactions on the 12th of different months do not match.

Review the flagged rows and reconcile the result

  1. Make a copy of the source sheet and filter the copy by the review column.
  2. Compare the flagged entries with the original order receipts or export. Check whether the sheet uses one row per order or one row per item.
  3. Record your decision in a separate column. For example, write “Repeated entry confirmed” or “Separate order confirmed.”
  4. When a duplicate is confirmed, remove the repeated row from the working copy and compare the resulting total with the source.

The practice data totals 119. If the source confirms that ORD-101 was entered twice, removing one 28-unit entry brings the total to 91. If you also remove ORD-103 based on the customer, day and amount check alone, the total drops to 63. The reconciliation makes that extra removal visible.

Formula reference — Microsoft Support guide to counting cells by criteria