You can find exact duplicate records in a database or spreadsheet by identifying the fields that should uniquely identify each record (customer ID, email, transaction number), then counting how many times each value appears — any value appearing more than once is a duplicate.
Exact duplicate detection is the starting point for any deduplication effort. Before tackling fuzzy matching and near-duplicate detection, find and remove the easy cases — the records where every key field matches perfectly. These are almost always errors, not legitimate separate records.
Finding Exact Duplicates in SQL
Method 1: Find duplicate key values This query finds all values in the "email" field that appear more than once:
Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.
SELECT email, COUNT() as count FROM contacts GROUP BY email HAVING COUNT() > 1 ORDER BY count DESC;
Method 2: Return the full duplicate records This returns all records that share a duplicate email:
SELECT * FROM contacts WHERE email IN ( SELECT email FROM contacts GROUP BY email HAVING COUNT(*) > 1 ) ORDER BY email;
Method 3: Multi-field deduplication When the unique key spans multiple fields (first_name + last_name + company):
SELECT first_name, last_name, company, COUNT() as count FROM contacts GROUP BY first_name, last_name, company HAVING COUNT() > 1;
Finding Exact Duplicates in Excel
Method 1: Conditional formatting Select your key column → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Excel highlights all duplicate values in your selected column.
Method 2: COUNTIF formula Add a helper column with: =COUNTIF($A:$A,A2). Any row where this returns more than 1 has a duplicate. Filter for values > 1 to see all duplicates.
Method 3: Remove Duplicates feature Data → Remove Duplicates → select which columns to check. Excel removes all rows where the selected columns match, keeping the first occurrence.
Important: Excel's Remove Duplicates modifies the data in place. Always work on a copy, not your original.
Finding Exact Duplicates in Google Sheets
Same COUNTIF approach as Excel: =COUNTIF(A:A, A2) returns the count of how many times the value in A2 appears in column A. Use conditional formatting to highlight cells where COUNTIF > 1.
For multi-column checking: =COUNTIFS($A:$A,A2,$B:$B,B2) counts rows where both column A and column B match.
What to Do Once You Find Exact Duplicates
-
Review before deleting. Even "exact" duplicates sometimes have slight differences in other fields — one record might have a phone number the other doesn't. Check all fields before deciding which to keep.
-
Merge, don't just delete. If the duplicate has any unique information in non-key fields, copy it to the master record before deleting the duplicate.
-
Investigate the source. How did the exact duplicate get created? Was it a double-import? A system sync that creates new records instead of updating? Fix the source.
-
Document what you found. Record the count of duplicates, the key field they matched on, and the date of the cleanup.
Frequently Asked Questions
Q: What is an exact duplicate in data quality? An exact duplicate is a record where one or more key fields have identical values to another record in the same dataset — indicating that both records represent the same real-world entity. Unlike near-duplicates, exact duplicates match perfectly on the key field(s).
Q: Which field should I use to detect exact duplicates? Use the field that should uniquely identify each entity: email address for contacts, transaction ID for financial records, order number for orders, SKU for products, customer ID for customer records. A duplicate on a unique identifier field is always an error; a duplicate on a name field may be legitimate (two different people with the same name).
Q: What's the fastest way to find exact duplicates in a large dataset? A GROUP BY + HAVING COUNT(*) > 1 query in SQL runs in seconds on millions of records. For spreadsheets, the COUNTIF approach is fast but may slow on very large files. Data quality tools with profiling capabilities show exact duplicate counts instantly.
Q: What's the difference between finding duplicates and removing duplicates? Finding duplicates identifies which records are duplicated and produces a report. Removing duplicates merges or deletes the redundant records, keeping only one copy. Finding comes first — you need to understand what you're removing before you do it.
Q: Can I find exact duplicates across multiple fields simultaneously? Yes. In SQL, use GROUP BY with multiple columns: GROUP BY field1, field2, field3. In Excel, use COUNTIFS with multiple criteria. This finds records that match on all specified fields simultaneously.
Q: What should I do with exact duplicates where both records have different data in non-key fields? Merge the records: keep the record with the most complete or most recent data as the master, copy any unique data from the duplicate to the master, then delete the duplicate. The goal is one record with all available data.
Q: How do I find duplicates added recently (since my last cleanup)? Add a date filter to your query: WHERE created_at > '2024-01-01'. This finds duplicates that were created after a specific date, allowing you to focus on recent additions without reprocessing records you've already cleaned.
Q: Can COUNTIF in Excel find duplicates across different columns? COUNTIF checks one column at a time. For multi-column duplicate detection, use COUNTIFS: =COUNTIFS($A:$A,A2,$B:$B,B2) checks whether both column A and column B values match in any other row.
Q: What is the risk of accidentally deleting non-duplicate records while deduplicating? The risk is low for exact key field matches but non-zero if the "key" field isn't truly unique. Two customers might legitimately share the same phone number (a main company line). Before deleting any record, verify that it's actually a duplicate by checking additional fields.
Q: Should I deduplicate the raw data or create a deduplicated view? For production databases, create a deduplicated view first — this lets you verify the results without modifying the source data. Once you've confirmed the deduplication logic is correct, apply it to the source data. For file-based data (CSV), always work on a copy.
Exact duplicate detection is the fastest, highest-confidence deduplication operation available. Run it first — find the obvious duplicates, clean them, then move on to the harder near-duplicate cases.
