Find and Remove Duplicate Rows 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.
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.
This is a reverse case where you want to display rows in a spreadsheet that have 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.
Google Developer Expert, Google Cloud Champion
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.