How to Find Duplicates in Google Sheets: A Practical Step-by-Step Guide
Learn how to find duplicates in Google Sheets using built-in tools like conditional formatting, COUNTIF, and UNIQUE. This practical guide covers multi-column checks, large datasets, and safe cleanup practices for students, professionals, and small business owners.
Duplicates in Google Sheets refer to identical values in the chosen comparison columns or rows. This quick, quotable answer provides a roadmap: highlight with conditional formatting, flag with COUNTIF, and refine with UNIQUE. See our detailed steps for exact formulas.
What qualifies as a duplicate in Google Sheets?
In Google Sheets, a duplicate means two or more rows share the same key values across the columns you choose for comparison. The simplest case is exact duplicates within a single column (e.g., two identical email addresses). More commonly, people look for duplicate records defined by a combination of fields, such as first name, last name, and date of birth. Understanding what constitutes a duplicate is the first step in any reliable cleanup process. According to How To Sheets, a well-defined criterion prevents accidental data loss when you remove duplicates. Start by deciding which columns to compare and whether you care about case sensitivity. If your data has nonessential spaces or inconsistent capitalization, you may get false positives. Trim spaces and normalize text before running formulas. This is especially important in large datasets where small differences become amplified. By setting clear rules, you ensure that your duplicates are meaningful and actionable rather than noisy clutter.
Common scenarios where duplicates matter
Duplicates can distort summaries and trend analyses. For example, a customer list with repeated emails can inflate contact counts, while repeated product SKUs can skew inventory metrics. When you know what to compare (e.g., email + date of signup, or SKU + batch number), you can tailor your approach. The How To Sheets team emphasizes defining the scope before you clean. Inconsistent formatting (spaces, capitalization, or special characters) often creates false duplicates. Cleaning with TRIM, LOWER, and proper capitalization helps ensure you’re flagging true duplicates rather than near- matches.
Methods to find duplicates: built-in tools
Google Sheets provides several native approaches to find duplicates. The most common are conditional formatting (to visually highlight duplicates), COUNTIF/COUNTIFS (to flag duplicates with a helper column), and the UNIQUE function (to extract unique rows or identify duplicates). Each method has trade-offs in speed, readability, and data preservation. For educational purposes, it’s helpful to mix methods: visually scan with formatting, then validate with formulas, and finally extract duplicates if needed.
Practical guidance: when to remove vs. mark duplicates
Not every duplicate should be deleted. Depending on your workflow, you may want to mark duplicates for review, keep a single canonical record, or remove duplicates only from a specific column while preserving others. Plan a backup step before removing duplicates: duplicate rows can carry important contextual information in other columns. If you’re working with shared data, document your cleanup criteria so others can reproduce the result. Clear criteria also reduces the risk of erasing legitimate, similar-looking records.
Deep dive: spreadsheet hygiene for duplicates
Before applying any cleanup, normalize data. Remove extra spaces, unify date formats, and convert text to consistent case. When repeating checks across large datasets (thousands of rows or more), consider performing the cleanup on a copied sheet to avoid accidental data loss. Regularly audit formulas and references to ensure that any filtering or sorting doesn’t reintroduce duplicates. Finally, test edge cases—empty cells, merged cells, and multi-valued fields—to avoid surprises during cleanup.
Verification: ensuring accuracy after cleanup
After identifying and acting on duplicates, verify results with spot checks across key columns. Build a quick sanity checklist: are there any remaining exact duplicates in the chosen scope, did any non-duplicate records get touched, and do totals in pivot tables still reconcile? If you’re sharing the sheet with others, request a quick peer review. The goal is reproducibility and trust in your results.
Handling duplicates across multiple sheets
Duplicates don’t stop at a single sheet. When duplicates may exist across multiple tabs, apply consistent criteria in each sheet or use a consolidated view with QUERY or IMPORTRANGE to cross-check. A centralized reference sheet helps maintain uniform rules and reduces drift between datasets. If you need cross-sheet checks, consider scripting a lightweight routine to compare key columns and surface mismatches.
Final tips for beginners and advanced users
For beginners, start with conditional formatting to build intuition about what counts as a duplicate. For advanced users, combine COUNTIF-based flags with dynamic filters to create a live dashboard of duplicates. Always keep a recovery plan and document your criteria so future users can reproduce your cleanup. By practicing these steps, you’ll learn how to find duplicates in Google Sheets efficiently and safely.
Tools & Materials
- Google Sheets access(A Google account with access to Google Sheets)
- Sample dataset(Spreadsheet containing duplicate entries for practice)
- Formula cheat sheet(Optional reference for COUNTIF, COUNTIFS, UNIQUE)
- Backup copy(Create a backup before removing duplicates)
Steps
Estimated time: 20-40 minutes
- 1
Open your dataset in Google Sheets
Launch Google Sheets and open the spreadsheet that contains duplicates you want to identify. Ensure you’re working on a copy if possible to preserve the original data.
Tip: Verify the columns you’ll compare and note any irregular formatting before proceeding. - 2
Add a helper column for duplication flags
Insert a new column next to your data and create a COUNTIF-based formula to flag duplicates. For example, in cell D2, use =IF(COUNTIF(A:A, A2)>1, "duplicate", "unique"). Copy down the column.
Tip: Use absolute references where appropriate to avoid shifting ranges as you fill down. - 3
Highlight duplicates with conditional formatting
Select the range you want to inspect, go to Format > Conditional formatting, choose a rule like 'Custom formula is' and enter a formula such as =COUNTIF($A:$A, $A1)>1 for a single-column check. Pick a bold color.
Tip: Apply the rule to multiple columns if you’re checking several fields in tandem. - 4
Isolate duplicates using FILTER or SORT
Use FILTER to create a new view of only the duplicates. Example: =FILTER(A:C, D:D="duplicate").
Tip: This non-destructive approach keeps the original data intact while you review duplicates. - 5
Extract unique records with UNIQUE
Create a clean dataset by extracting unique rows: =UNIQUE(A:C). This removes exact row duplicates while preserving the rest.
Tip: If you need to preserve duplicates for audit, work on a separate sheet or column instead. - 6
Decide what to do with duplicates
Review flagged duplicates and choose to remove, merge, or mark them for further investigation. Always keep a backup and verify totals afterward.
Tip: Document your cleanup rules so others can reproduce the results.
FAQ
What counts as a duplicate in Google Sheets?
A duplicate occurs when two or more rows have identical values in the columns you choose for comparison. You can define duplicates by a single column or a combination of fields.
Duplicates are rows with identical key values in the chosen columns.
Which method is best for large datasets?
For large datasets, a helper column with COUNTIF is efficient and easy to audit. For very big data, consider using QUERY or FILTER with UNIQUE to minimize processing time.
For big data, use COUNTIF first, then FILTER or QUERY to scale up.
How do I remove duplicates without losing data?
Always start with a backup, then use the built-in Remove duplicates feature or create a unique list with a formula and replace data selectively.
Back up first, then remove duplicates using built-in tools or a formula approach.
Can duplicates exist across multiple sheets?
Yes. Apply the same criteria to each sheet or use a consolidated view to check duplicates across tabs, potentially with a script for cross-sheet checks.
Duplicates can span multiple sheets; check each sheet or use scripts for cross-sheet checks.
How can I highlight duplicates across non-adjacent columns?
Use a custom conditional formatting formula that references the relevant ranges and checks for duplicates across non-adjacent columns.
Use a custom formula in conditional formatting to mark non-adjacent duplicates.
Watch Video
The Essentials
- Identify duplicates precisely using chosen columns.
- Use conditional formatting to visualize duplicates quickly.
- Flag duplicates with a helper column before cleanup.
- Verify results with spot checks and keep a backup copy.
