How to Eliminate Duplicates in Google Sheets Quickly

To eliminate duplicates in Google Sheets, select the data range, then choose Data > Data cleanup > Remove duplicates. Confirm whether the range has a header and which columns to compare, then click Remove duplicates. Sheets keeps the first matching row and removes later matches, so make a copy first if you may need the original data.

This guide covers the built-in cleanup tool, how to avoid deleting the wrong rows, and a formula option for creating a duplicate-free list while leaving your source data intact. The steps apply to Google Sheets in a web browser.

What You’ll Need

A Google account and access to the spreadsheet in a browser.
A clear idea of what counts as a duplicate: an identical value in one column, or a matching combination of values across several columns.
Permission to edit the sheet. If the data is important, make a copy or duplicate the relevant sheet before removing rows.

Step-by-Step

Review the data and decide what to compare. Identify the records you want to keep and the fields that define a duplicate. For example, if a contact list has columns for name, email, and city, decide whether two records are duplicates when their emails match, or only when all three fields match. This decision affects which rows Sheets removes. Check that records are arranged consistently, with one record per row and field names in a header row if you have them.
Protect the original data. If you might need to restore the original later, create a copy before proceeding. Use File > Make a copy to copy the spreadsheet, or click the arrow on the sheet tab and choose Duplicate to copy just that tab. You can also copy the data to a separate tab. This is especially useful for shared sheets, imports, customer lists, and any data that would be difficult to reconstruct.
Select the complete range of records. Click and drag over the data, including every column that belongs to each record. You can include the header row; you will identify it in the next dialog. Avoid selecting unrelated notes or another table beside or below the list. If the data has many rows, click the first cell, then use the sheet’s range box near the formula bar to enter a range such as A1:C500. Adjust the letters and row numbers to match your sheet.
Open the duplicate-removal tool. With the range selected, choose Data > Data cleanup > Remove duplicates from the menu. Google Sheets opens a dialog showing the selected range and options for the header row and columns. If the menu option is unavailable, confirm that you have selected cells in the grid and have edit access to the file.
Set the header and comparison columns. If the first selected row contains labels such as Name or Email, check Data has header row. Then choose the columns Sheets should use to identify duplicates. Selecting every column means a later row is considered a duplicate only when all selected field values match. Selecting just Email means rows with the same email are treated as duplicates even if their names or cities differ. Be deliberate: comparing too few columns can remove records that are different in other important ways.
Check the selection before confirming. Read the selected range and column choices in the dialog. Make sure the range covers whole records, the header option matches your data, and the selected columns reflect your definition of duplicate. The tool changes the selected cells directly; it does not create a second, cleaned copy automatically. If anything looks wrong, click Cancel, correct the selection, and reopen the tool.
Remove the duplicate rows and review the result. Click Remove duplicates. Sheets displays a message reporting how many duplicate rows it removed and how many unique rows remain. Check a few records near the top, middle, and bottom of the range, and verify that the row count is plausible. If the result is not what you expected, immediately use Edit > Undo or press Ctrl+Z on Windows or Chromebook. On a Mac, press Command+Z.

Example: Suppose A1:C4 contains a header row, followed by three rows: Ava, ava@example.com, North; Ava S., ava@example.com, South; and Ben, ben@example.com, West. If you select all three columns, the two Ava entries are not identical across the selected fields, so both remain. If you select only the email column, Sheets treats the repeated email as a duplicate and keeps the first matching row. That may be what you want for a unique email list, but it could discard useful details from the second record.

Formula alternative for a separate clean list: If you want to preserve the original rows and display unique values elsewhere, enter =UNIQUE(A2:A) in an empty cell, assuming the values to deduplicate are in A2:A and row 1 is a header. The formula spills a list of distinct values into the cells below it; for example, repeated names in A2:A appear once in the output. Ensure those cells are empty so the result has room to expand. To return unique records based on multiple columns, use a range covering those columns, such as =UNIQUE(A2:C), assuming each record is in columns A through C. This formula produces a distinct row for each different combination; it does not delete or alter the source data.

Common Mistakes

Selecting only one column from a multi-column table: Removing duplicates from a single field can leave other columns out of alignment with the records you meant to keep, or delete records because one value repeats even though the rest of the row differs. For a table, select the entire record range and choose the correct comparison columns in the dialog.
Checking too many or too few columns: If you compare every column, rows with the same email but a slightly different spelling of a name will both remain. If you compare only a common field, distinct people or transactions can be treated as duplicates. Confirm the business rule first, rather than assuming that duplicate means identical across the whole sheet.
Forgetting the header setting: If a header is treated as data, it may be included in the comparison. If the first actual record is mistakenly treated as a header, it will be excluded from duplicate checking. Verify the first selected row before running cleanup.
Expecting near-matches to count as duplicates: Different spellings, extra spaces, punctuation, or values stored differently may not match as you expect. For example, a name with an added middle initial is not an exact match. Clean or standardize the data before removing duplicates when you need to match variations.
Assuming removal is a preview: The cleanup tool changes the selected range. It does not simply highlight candidates or create a report. Use a copy, or use a UNIQUE formula in a separate area if you want to inspect the results before replacing anything.

Tips

Use a stable identifier where possible. Email address, order number, or another unique ID is often a better comparison field than a name, because different people can share a name and one person’s name can be entered in several ways.
Sort or filter before cleanup when it helps you choose which record to keep. The first occurrence is kept. If one version of a record is more complete, arrange the rows so that version appears first, then select the complete table and remove duplicates using the appropriate key column. Check the result afterward.
Standardize values before comparing. If spaces or inconsistent capitalization are causing confusion, make a helper column to normalize the values or edit them consistently. For example, you could create a cleaned version of a text field in another column, inspect it, and use that column as the comparison field. Keep the original field until you are satisfied with the cleanup.
Use filters or conditional formatting to inspect first. If you are unsure which rows will be removed, identify repeated values and review them before running the destructive cleanup. This is useful when duplicate records contain conflicting details that need a human decision.
Keep a record of the cleanup. For recurring imports, note the date, selected range, and comparison fields. This makes it easier to repeat the process consistently and explain why a row was removed.
Know the Excel difference. Excel for Microsoft 365 has a separate Remove Duplicates command on its Data tab; its dialog and selection workflow are not identical to Google Sheets. Follow the Sheets menu paths above when working in a browser-based Google spreadsheet.

Troubleshooting

Some duplicates remain. Check whether the relevant columns were all selected in the dialog, and confirm that the rows match in those columns. Differences such as a trailing space, alternate punctuation, or a different identifier can make two rows distinct. If you want to compare only one field, rerun the tool on the full record range but select only that field as the comparison column.

Rows you wanted to keep disappeared. Use Edit > Undo immediately (Ctrl+Z on Windows or Chromebook; Command+Z on Mac) if the change is still in the undo history. Otherwise, restore the backup or use version history: choose File > Version history > See version history, review an earlier version, and restore it if appropriate. Before restoring an entire spreadsheet, consider copying the missing data from the earlier version so you do not overwrite newer edits.

The cleanup tool is missing or unavailable. Make sure you are editing a Google Sheets file rather than viewing a read-only file or a preview of an Excel file. Confirm that you have permission to edit and that a cell range is selected. If a protected range prevents changes, ask the sheet owner for access or have them perform the cleanup.

The UNIQUE formula shows an error or no results. Confirm that the formula begins with an equals sign and that the referenced range is valid. Clear cells below and beside the formula if Sheets reports that the result could not expand. If you see a circular dependency, place the formula outside the source range instead of putting it in a cell that it references. A formula result updates when its source data changes, while the Remove duplicates tool makes a one-time change.

The cleaned list looks shorter than expected. Check whether the header row was included and whether only certain columns were used to identify repeats. Use Undo if you suspect valid records were removed, then repeat the operation on a copied tab with a more specific set of comparison columns.

Frequently Asked Questions

Does Google Sheets keep the first or last duplicate?
When removing duplicates, Sheets keeps the first occurrence in the selected range and removes later matching rows. If you need a particular version to remain, arrange the data so that record appears first before cleanup.

Can I remove duplicates based on one column but keep the full rows?
Yes. Select the complete table so rows stay together, then choose only the relevant field in the dialog’s column list. Sheets uses that field to decide which rows repeat and keeps the first row for each matching value.

Can I highlight duplicates instead of deleting them?
Yes. Use Format > Conditional formatting to highlight repeated values, or filter and review the data before removal. Highlighting is a safer first step when repeated records may contain conflicting information.

Will Remove duplicates affect formulas or formatting?
The command removes duplicate rows from the selected range; it is not a formatting cleanup tool. Review formulas that refer to the affected rows and check the surrounding sheet after removal. Make a copy first if those references are important.

Can I create a unique list without changing the original data?
Yes. Put a UNIQUE formula in an empty area or another tab. It returns distinct values or rows as a separate result, while the source range remains unchanged. Use the built-in removal command only when you intend to delete duplicate rows from the selected data.

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.