How To Remove Duplicates In Google Sheets (2026)
Google Sheets has a built-in tool that removes repeated rows from a list, so you do not have to hunt for them by eye.
It lives in the Data cleanup menu, and a separate formula called UNIQUE can show a de-duplicated copy without touching your original data.
The steps below follow Google’s Docs Editors Help pages for Sheets on a computer, including what counts as a duplicate and what the pages do not say.
Quick answer: remove duplicates in Google Sheets
| What you want | Where to go |
|---|---|
| Delete repeated rows from the sheet | Select the data, then Data > Data cleanup > Remove duplicates |
| Show a de-duplicated list and keep the original | Type =UNIQUE(range) in an empty cell |
| Remove stray spaces first | Select the data, then Data > Data cleanup > Trim whitespace |
| Get suggestions automatically | Data > Data cleanup > Cleanup suggestions |
Remove duplicates step by step
- Open the spreadsheet in Google Sheets on your computer.
- Select the data range to check.
- At the top, click Data, then Data cleanup, then Remove duplicates.
- Choose which columns to include, and say whether the data has a header row.
- Click Remove duplicates.
- Click OK in the status window.
Google’s Learning Center page gives the shorter menu path Data > Remove duplicates, so look there if you do not see Data cleanup in your menu.
What counts as a duplicate
Google says cells with identical values are treated as duplicates even if their letter case, formatting or formulas differ.
So Apple and apple count as the same value, and a cell showing 5 from a formula matches a cell where you typed 5.
If you select a single cell inside a range, Google says duplicates are removed across the whole range.
Choose the right columns and header setting
The dialog asks which columns to include, which decides what Sheets compares.
| Dialog choice | Why it matters |
|---|---|
| Columns to include | These are the columns Sheets uses when it decides whether a row is a repeat |
| Data has a header row | Tell Sheets this so the labels in row 1 are not treated as data |
Google’s pages do not describe any other options in the dialog.
Trim whitespace before you remove duplicates
Two entries that look identical can differ by a space, and a stray space can make Sheets treat them as different.
Google’s UNIQUE page warns that text cells with hidden differences, such as trailing spaces, can make rows that look identical count as different.
- Select the data range.
- Click Data > Data cleanup > Trim whitespace.
Google says this removes extra leading, trailing and excessive spaces, but non-breaking spaces are not trimmed.
Use the UNIQUE function to keep the original
The Remove duplicates tool changes your data, whereas UNIQUE builds a separate list.
Google describes UNIQUE as returning unique rows in the source range and discarding duplicates, in the order they first occur.
| Formula | What it returns |
|---|---|
| =UNIQUE(B2:D5) | The distinct rows from a three-column range |
| =UNIQUE(B2:D5, TRUE) | Compares columns instead of rows, so a column that repeats another is dropped |
| =UNIQUE(B2:D5, FALSE, TRUE) | Only the rows that appear exactly once; rows that repeat are excluded |
The syntax is UNIQUE(range, by_column, exactly_once), and both optional arguments default to FALSE.
Google also says numbers should share the same formatting, such as percentages as percentages and currency as currency, so that they match correctly.
Let Sheets suggest a cleanup
Smart cleanup can flag duplicates for you.
- Click Data > Data cleanup > Cleanup suggestions.
- Click the error icon on a column to see its suggestions in the sidebar.
- Hover over a suggestion to highlight the related cells.
Google says anomalies show in red and other suggestions show in green, and that suggestions cover extra spaces, duplicates, number formatting and inconsistent data.
Its page does not explain how duplicates are detected or how to apply a duplicate suggestion, so use the Remove duplicates tool above for the actual deletion.
Check a column with Column Stats
To see which values repeat before you delete anything, click Data > Column Stats.
The sidebar shows count and distribution charts, frequency tables of the most and least common values, and summary statistics for the column.
Use the arrows at the top of the sidebar to switch columns.
Split combined text before you compare
Duplicates are easier to spot when each value sits in its own column, for example a first and last name that were pasted into one cell.
- Select the cells to split, or paste the data into the sheet first.
- Click Data > Split text to columns.
- To change the character Sheets splits on, open the drop-down next to Separator.
- To let Sheets choose, select Detect automatically from the same menu.
If you pasted the data, Google says you can also click the Paste options button and then Split text to columns.
Google’s Learning Center page lists a few other tidy-up steps for the same kind of data.
| Task | Steps |
|---|---|
| Wrap long text consistently | Select the cells, then Format > Wrapping and choose Overflow, Wrap or Clip |
| Swap rows and columns | In an empty cell type =TRANSPOSE( followed by the range, then press Enter |
| Add checkboxes | Select the cells, then Insert > Checkbox |
What Google does not say
Google’s Remove duplicates page does not state which duplicate row is kept, how removed rows are handled, or any size limits.
Work on a copy of important data, or use File > Make a copy first, and check the result before you share the file.
Google’s version history tool can bring back an earlier state, as covered in our guide to restoring an earlier version of a Google Doc.
Related guides
If you use both programs, here is how to remove duplicates in Excel.
To restrict what people can type in a column, see how to create a drop-down list in Google Sheets.
To stop others editing a cleaned-up sheet, read how to lock cells or protect a sheet in Google Sheets.
Sources
Google Docs Editors Help: Split text, remove duplicates or trim whitespace.
Google Docs Editors Help: UNIQUE function.
Google Docs Editors Help: Use Sheets smart cleanup to prepare your data for analysis.
Google Workspace Learning Center: Tips to format and clean up data.