how to remove duplicates in google sheets

To remove duplicates in Google Sheets, you have several effective methods:

1. Using the Built-in Remove Duplicates Tool

• Select the data range you want to clean.
• Go to the menu: Data > Data cleanup > Remove duplicates.
• Choose which columns to check for duplicates (you can select all columns or specific ones).
• Confirm to remove duplicates; Google Sheets will delete duplicate rows and keep the first instance.
• You will receive a summary of how many duplicates were removed

2. Using the UNIQUE Formula

• Use the formula =UNIQUE(range) to generate a list of unique rows or values from a specified range.
• This creates a new list without duplicates, leaving the original data intact.
• For example, =UNIQUE(A1:D11) removes duplicate rows based on all columns in that range

3. Highlighting Duplicates Before Removal

• Use Conditional Formatting with a custom formula like =COUNTIF(range, cell)>1 to highlight duplicates.
• This helps identify duplicates visually before deleting them manually or using the Remove Duplicates tool

4. Using Add-ons for Advanced Needs

• The "Remove Duplicates" add-on from Google Workspace Marketplace offers features like:
  • Finding duplicates across multiple sheets or columns.
  • Highlighting, deleting, moving, or copying duplicates automatically.
  • Scheduling automatic duplicate removal.
  • No coding required, with more flexibility than built-in tools

5. Using Google Apps Script (for automation)

• You can write a script to remove duplicates programmatically if you need to automate the process repeatedly

Summary Table

Method| Best For| Key Features
---|---|---
Remove Duplicates Tool| Quick removal of duplicate rows| Easy UI, summary report
UNIQUE Formula| Creating unique lists without altering original data| Formula- based, dynamic updates
Conditional Formatting| Identifying duplicates visually| Highlights duplicates before removal
Remove Duplicates Add-on| Advanced duplicate management| Automation, multi- sheet, scheduling
Apps Script| Automated, custom workflows| Programmable, repeatable

These methods cover most scenarios for removing duplicates in Google Sheets, from simple manual removal to automated and advanced workflows