How to Remove Duplicates in Google Sheets

To remove duplicate rows in Google Sheets, select your whole data range, then choose Data > Data cleanup > Remove duplicates. Confirm whether the range has a header row, choose the columns to compare, and click Remove duplicates. Sheets keeps the first matching row and removes later matches, so make a copy of important data before you begin.

This is useful for cleaning contact lists, inventory, survey responses, and other tables. You can compare every column to find identical rows, or select particular columns when a repeated value in those columns is what defines a duplicate. The steps below are for Google Sheets in a web browser.

What You’ll Need

A Google account with permission to edit the spreadsheet.
A table with a clear header row and data arranged in columns. Remove blank rows or unrelated notes from the middle of the table first.
A backup if the data is important. You can duplicate the sheet tab or make a copy of the file before removing anything.

Step-by-Step

Make a safe copy. If you might need to restore the original list, right-click the sheet tab at the bottom and choose Duplicate. You can also use File > Make a copy to create a separate file. Do this before cleanup; undo is helpful, but it is not a substitute for a backup when the data matters.
Check that the table is ready. Make sure each row represents one record and each column represents one field, such as name, email, or order number. Avoid merged cells in the data area. If there are unrelated columns beside the table, do not include them in the selection.
Select the complete table range. Click the first cell in the table, then drag to the last column and last row, including the header row. For example, if the headers are in row 1 and the records occupy columns A through D down to row 250, select A1:D250. Selecting the whole table keeps a row’s associated details together when a duplicate is removed.
Open the cleanup command. In the menu bar, choose Data > Data cleanup > Remove duplicates. If you do not see the command, check that the file is open in Google Sheets and that you have editing permission. The available options may also depend on whether a range is selected.
Tell Sheets whether the range has headers. In the dialog, tick Data has header row if the first row of your selection contains column names such as Name or Email. If the selection starts with actual records, leave it unchecked. This helps ensure the header is not treated as a data record.
Choose how duplicates should be identified. To remove only completely identical records, leave all relevant columns selected. To treat a repeated email address as a duplicate even when the name or another field differs, select only the email column in the column list. Think carefully about this choice: when you compare just one column, Sheets can remove a row even if other cells in that row contain different information.
Run the cleanup and review the result. Click Remove duplicates. Sheets displays a message about how many duplicate rows it removed. Look over the remaining records and check a few known examples, especially if you chose only some columns for comparison. If the result is not right, use Ctrl+Z on Windows or Command+Z on Mac immediately, then repeat the cleanup with the correct range or columns.
Check totals and save. Compare the number of rows before and after cleanup, allowing for the header row. Confirm that the first copy of each record has the details you want to keep. Google Sheets saves changes automatically, but it is still worth checking that the file has finished syncing before closing the tab.

For example, suppose a contact table has columns for Name, Email, and City. If two rows have the same email but different cities, comparing all three columns will keep both rows because the complete records differ. Selecting only Email treats them as duplicates, and one row will be removed. Make a deliberate choice about which copy should remain before using a single identifying column.

Common Mistakes

Selecting only one column by accident. If you select just the email column rather than the entire table, you risk removing or separating information you intended to keep. Select the full rectangular table first, then use the dialog’s column choices to decide what defines a match.
Comparing too many columns. If a timestamp, note, or minor variation differs between rows, selecting every column can make two records count as distinct even when you consider them duplicates. Choose only the fields that define a duplicate for your task.
Comparing too few columns. A shared first name, product category, or postal code may not uniquely identify a record. If you compare on a broad field, legitimate entries can be removed. Use an identifier that is genuinely unique, such as an email address or record number, when appropriate.
Forgetting the header setting. If the header is not recognized, it can be included in the comparison. If your first data row is mistakenly treated as a header, it will not be checked like the others. Verify the checkbox against the range you selected.
Expecting formatting to distinguish records. Different colors, number formats, or capitalization do not necessarily make values unique to Sheets’ duplicate cleanup. Conversely, hidden spaces or different underlying values can make entries that look alike compare differently.
Cleaning a live shared list without warning others. A collaborator may be editing the same records. Coordinate first, or make a copy and clean that copy so you can review the changes before replacing the shared data.

Tips

Sort or filter before cleaning. Sorting can help you see related records together and decide which version to keep. Filters can help narrow the view, but check the selected range carefully so the cleanup does not affect unrelated table data.
Use a unique key where possible. An email address, product code, or transaction ID is often a safer comparison field than a person’s name. Names can be shared by different people, while an identifier is intended to distinguish records.
Preview repeats instead of deleting immediately. For values in A2:A100, you can apply conditional formatting to highlight repeats: select the range, choose Format > Conditional formatting, set the rule to Custom formula is, and enter =COUNTIF($A$2:$A,A2)>1. This highlights every occurrence of a repeated value, including its first occurrence. It does not remove anything.
Create a separate unique list with a formula. If your records are in A2:D and you want a non-destructive list of distinct rows elsewhere, enter =UNIQUE(A2:D) in an empty cell. The result expands into the cells below and beside it, so leave that area clear. It returns one copy of each distinct row and leaves the source data untouched. If you only want uniqueness based on one field while retaining other columns, review the rows carefully rather than assuming this formula will choose the version you prefer.
Keep Sheets and Excel directions separate. In Google Sheets, use Data > Data cleanup > Remove duplicates. Microsoft Excel has a separate Data > Remove Duplicates command; its dialog and options are not identical.

Troubleshooting

The menu option is missing or unavailable: Confirm that you are in Google Sheets in a browser, have permission to edit, and have selected a range of cells. If the file is an imported Excel workbook, consider opening or converting it as a Google Sheets file. In a shared file, ask the owner for edit access if you only have viewing or commenting permission.

Duplicates remain after cleanup: Check which columns were selected in the dialog. If the rows differ in one of those columns, Sheets may treat them as distinct. Also inspect values that look identical for leading or trailing spaces, punctuation, or different underlying dates and numbers. Formatting alone is not a reliable way to tell whether two values are the same. You can use conditional formatting to locate repeated values before deciding whether to standardize the data and run cleanup again.

Good records disappeared: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Then restore from the duplicate tab or file if needed. Select the complete table and choose a more specific comparison column, or compare all relevant fields if only identical rows should be removed. Remember that Sheets keeps one copy; it does not ask you to choose which duplicate to retain.

The formula result will not appear: With =UNIQUE(A2:D), enter the formula in a blank area with enough empty cells for the results to spill. Clear any content blocking the output. If the formula is in a filtered or protected area, try a new blank sheet or an unprotected range. If the formula shows an error, check that its range uses the right sheet and columns.

Frequently Asked Questions

Does Google Sheets keep one copy or delete every matching row?
It keeps the first occurrence in the selected range and removes later rows it identifies as duplicates. Review the order of your data first if the first copy is not necessarily the one you want to keep.

Can I remove duplicates based on just one column?
Yes. Select the whole table, open Data > Data cleanup > Remove duplicates, and select only the column that defines a duplicate. Be aware that rows with different values in other columns can still be treated as duplicates.

Does the command distinguish uppercase and lowercase?
Google Sheets treats duplicate values as duplicates regardless of capitalization. Formatting differences also do not make values unique for this cleanup tool.

Can I check for duplicates without deleting them?
Yes. Conditional formatting can highlight repeated values, and =UNIQUE(A2:D) can create a separate list of distinct rows. Both approaches let you inspect results without changing the original records.

Can I undo the cleanup?
Usually, yes: use Ctrl+Z on Windows or Command+Z on Mac soon after the change. For valuable data, make a copy first rather than relying only on undo.

Will this prevent duplicates from being added later?
No. Remove duplicates cleans the current selection; it does not automatically block future entries. Use a review process or a separate check to spot new repeats as the sheet is updated.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.