How do I remove duplicate rows from my spreadsheet?
Both Excel and Google Sheets have a built-in Remove Duplicates tool (Data menu) that deletes repeated rows based on the columns you choose. If you want to flag duplicates without deleting anything, use =COUNTIF($A$2:A2,A2)>1 to mark rows that repeat an earlier value.
Formula (Excel and Google Sheets)
=COUNTIF($A$2:A2,A2)>1The built-in tool (Data > Remove Duplicates in Excel, Data > Data cleanup > Remove duplicates in Google Sheets) is the fastest path when you are sure you want the extra rows gone. It lets you choose which columns must match for two rows to count as duplicates, which matters: matching on every column is stricter than matching on just, say, an email address.
The formula version is safer when you are not sure yet: it flags TRUE on any row whose value in column A already appeared in an earlier row, without deleting anything, so you can review before removing.
Step by step
- 1
Back up the sheet first
Removing duplicates deletes rows permanently in most spreadsheet programs' default behavior. Duplicate the sheet or export a copy first.
- 2
Select your data and open Remove Duplicates
Choose which column(s) must match for a row to be considered a duplicate.
- 3
Or flag first with a formula
Add a helper column with =COUNTIF($A$2:A2,A2)>1 to review which rows would be considered duplicates before deleting anything.
Common questions
Which row does Remove Duplicates keep?
It keeps the first occurrence and deletes later repeats, based on the columns you selected to match on.
Will trailing spaces cause false duplicates or missed duplicates?
Yes, "Acme Inc" and "Acme Inc " (with a trailing space) are treated as different values. Clean up spacing with TRIM before checking for duplicates.
Related formulas
How to Use COUNTIF and COUNTIFS in Excel and Google Sheets
RelatedHow to Format Text and Dates with the TEXT Formula
RelatedHow to Use Conditional Formatting to Highlight Cells Automatically
RelatedHow to Remove Extra Spaces and Hidden Characters with TRIM and CLEAN
From the blogHow to Make Sense of Your Spreadsheet Data Without Being an Analyst
Want a formula written for your exact spreadsheet, not a generic example?
Try the free formula generator