Find and Remove Duplicate Rows in Google Docs

Learn how to remove duplicate rows from Google Docs spreadsheets. You can apply the same trick to detect non-unique records as well.

November 23, 2011

Unlike Microsoft Excel, there aren’t any Filter functions available in Google Docs spreadsheets but you can easily emulate them with simple formulae. Let’s see how:

Filter Unique Records in Google Docs

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

unique-records

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.

Related: Google Docs Guide