Duplicate entries in Google Sheets can make reports inaccurate, inflate totals, confuse teams, and slow down decision-making. Whether a spreadsheet contains customer emails, inventory items, survey responses, or transaction records, finding and removing repeated data helps keep information clean, reliable, and easier to analyze.
TLDR: Google Sheets offers several ways to find and remove duplicates, including the built-in Remove duplicates tool, conditional formatting, formulas, and pivot tables. The fastest option is the built-in tool, while formulas provide more control when records need to be reviewed before deletion. For best results, spreadsheet users should always make a backup, confirm which columns define a duplicate, and verify the results afterward.
Why Duplicates Matter in Google Sheets
Duplicates may seem harmless at first, but they can create serious issues. A sales report with repeated orders may show inflated revenue. A contact list with duplicate emails may cause customers to receive the same message multiple times. A task tracker with repeated assignments may make a team think more work exists than actually does.
In many cases, duplicates appear because data is copied from multiple sources, imported from forms, merged from different departments, or entered manually. Even small inconsistencies, such as extra spaces or different letter casing, can make duplicate cleanup more difficult. That is why a careful process is important.
Method 1: Use the Built-in Remove Duplicates Tool
The easiest way to remove duplicates in Google Sheets is by using the built-in Remove duplicates feature. This method is best when a user wants a quick cleanup without writing formulas.
- Select the range of cells that should be checked.
- Click Data in the top menu.
- Select Data cleanup, then choose Remove duplicates.
- If the selected range includes headers, check Data has header row.
- Choose the columns that should be used to identify duplicates.
- Click Remove duplicates.
Google Sheets will then display a message showing how many duplicate rows were removed and how many unique rows remain. This is useful for quick confirmation, but it does not show which specific rows were deleted. For that reason, it is wise to create a copy of the sheet before using this tool.
Important: The chosen columns matter. If only the email column is selected, rows with the same email address will be considered duplicates, even if names or phone numbers differ. If all columns are selected, Google Sheets will remove only rows that match across every selected column.
Method 2: Highlight Duplicates with Conditional Formatting
Sometimes duplicates should be reviewed before they are removed. In that case, conditional formatting is a safer option because it highlights duplicate values without deleting anything.
To highlight duplicates in a single column, a user can follow these steps:
- Select the column or range, such as A2:A100.
- Click Format, then choose Conditional formatting.
- Under Format rules, choose Custom formula is.
- Enter a formula such as
=COUNTIF($A$2:$A$100,A2)>1. - Choose a highlight color.
- Click Done.
This formula checks whether a value appears more than once in the selected range. If it does, Google Sheets highlights it. This method is especially helpful for email lists, product IDs, invoice numbers, and other fields that should be unique.
Tip: Conditional formatting does not remove duplicates automatically. It only makes them easier to see, so the spreadsheet owner can decide what to keep and what to delete.
Image not found in postmeta
Method 3: Use the UNIQUE Function
The UNIQUE function is a convenient way to create a clean list without changing the original data. It is ideal when a person wants to preserve the source sheet while generating a duplicate-free version elsewhere.
For example, if data appears in cells A2:C100, the following formula can be placed in an empty area:
=UNIQUE(A2:C100)
Google Sheets will return only unique rows from the selected range. If duplicate rows exist, only the first instance will appear in the result. This is a non-destructive method, meaning the original data remains unchanged.
The UNIQUE function works well for simple tables, but it depends on exact matches. If one row contains “John Smith” and another contains “John Smith ” with an extra space, Google Sheets may treat them as different values. Cleaning spaces with functions such as TRIM can improve accuracy.
Method 4: Find Duplicates with COUNTIF
The COUNTIF function can identify how many times a value appears in a range. This is useful when a spreadsheet needs an extra helper column that marks duplicates clearly.
For example, if email addresses are listed in column A, a user can place this formula in column B:
=COUNTIF($A$2:$A$100,A2)
The formula returns a number. If the result is 1, the value appears once. If the result is greater than 1, the value is repeated. A more descriptive version can be written as:
=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")
This approach gives the spreadsheet owner more flexibility. The sheet can be filtered by the word Duplicate, reviewed, and cleaned manually.
Method 5: Remove Duplicates with a Pivot Table
A pivot table can also help summarize duplicate data. While it is not primarily a deletion tool, it is useful for spotting repeated names, emails, products, or transaction IDs.
- Select the data range.
- Click Insert, then choose Pivot table.
- Add the suspected duplicate field to Rows.
- Add the same field to Values and summarize by COUNTA.
The pivot table will show each unique value and how many times it appears. Any value with a count greater than one is a duplicate. This method is especially helpful for larger datasets where manual scanning would take too much time.
Image not found in postmeta
Best Practices Before Removing Duplicates
Before deleting any repeated data, a careful user should follow a few best practices. These steps reduce the risk of losing important information.
- Make a backup: Duplicate the sheet or file before making permanent changes.
- Define what counts as a duplicate: One project may define duplicates by email address, while another may require matching name, date, and order number.
- Check for formatting issues: Extra spaces, inconsistent capitalization, and different date formats can affect detection.
- Review before deleting: When possible, highlight or label duplicates first.
- Verify totals afterward: Reports, charts, and formulas should be checked after cleanup.
Cleaning Data for Better Duplicate Detection
Duplicate detection works best when data is consistent. Google Sheets includes several functions that can help clean records before duplicates are removed.
- TRIM: Removes extra spaces from text.
- LOWER: Converts text to lowercase for consistent matching.
- UPPER: Converts text to uppercase.
- CLEAN: Removes non-printing characters.
For example, if cell A2 contains an email address with possible extra spaces or inconsistent capitalization, this formula can standardize it:
=LOWER(TRIM(A2))
After cleaned values are created, they can be copied and pasted as values, then checked for duplicates more accurately.
FAQ
Can Google Sheets remove duplicates automatically?
Yes. The built-in Remove duplicates tool can delete duplicate rows automatically based on selected columns. However, it is best to make a backup first because removed rows are not displayed individually before deletion.
How can duplicates be found without deleting them?
Duplicates can be found using conditional formatting, the COUNTIF function, or a pivot table. These methods allow duplicates to be reviewed before any data is removed.
Does the UNIQUE function delete duplicate data?
No. The UNIQUE function creates a new duplicate-free list in another location. It does not change or delete the original data.
Why are some duplicates not being detected?
Some duplicates may contain hidden differences, such as extra spaces, capitalization changes, or different date formats. Functions such as TRIM, LOWER, and CLEAN can help standardize the data first.
What is the safest way to remove duplicates?
The safest method is to duplicate the sheet, highlight or label duplicates first, confirm which columns define a duplicate, and then use the Remove duplicates tool or manually delete reviewed entries.