Find and Remove Duplicate Rows in Google Sheets

Published in: Google Sheets

Unlike Microsoft Excel, there aren’t any Filter functions available in Google Spreadsheets but you can easily emulate them with simple formulae.

How to Highlight and Remove Duplicates in Google sheets

Filter Unique Records in Google Sheets

If you like to remove duplicate rows from a table in Google Docs, use the UNIQUE formula as shown in this screencast.


Click an empty cell, type =UNIQUE(, select the range of cells you want to filter and then close the parenthesis. Simple.

Find Duplicate Rows in Google Docs

This is a reverse case where you want to display rows in a spreadsheet that have duplicates.

find duplicates

This requires a couple of steps:

Step 1: Assuming that our original data is in columns A, B & C, go to cell D1 and write a formula to concatenate the data in three columns. We’ll use a pipe separator to distinguish between a row like “a1, b, c” and “a, 1b, c”.

=CONCATENATE(A1, "|", B1, "|", C1) – drag the cell handle to fill the formula in other cells.

Step 2: Sort the D column by clicking the header as shown in the screencast.

Step 3: We now need a function to compare two adjacent values in column D. If the values are same, one of them is a duplicate for sure. Type this in E2:

=IF(D2=D1, "Duplicate", "") – drag to fill all cells until E5.

That’s it. All rows in column E that have value “Duplicate” are duplicate rows.

Published in: Google Sheets

Looking for something? Find here!

Meet the Author

Web Geek, Google Developer Expert
Amit Agarwal

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India. He is the developer of Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory

Get in touch