Say goodbye to duplicate rows in your dataset! A built-in Remove Duplicates menu option (command) is available in Google Sheets.

You can check the Data menu in Google Sheets to get a taste of this new feature.

Earlier, you were using UNIQUE or SORTN to meet the above, right? The drawback of these functions is visible.

They are not for altering the source. Instead, to get the result in a new range.

The Unique won’t take a key column (in vertical data) or key row (in horizontal data) for evaluation. It works row-wise or column-wise as a whole.

On the other hand, the SORTN does support the key column. But it may not be easy for a newbie to code.

Here comes the Remove Duplicates Data Clean-up Menu command handy.

Update:- Now it’s moved under Data > Data Clean-up.

How to Remove Duplicates Using Data Clean-up Menu in Google Sheets

Single Column (Unique Way)

Here are the steps to follow to eliminate repeated values in a column.

Range: A3:A11.

Select the data in A3:A11. It’s up to you whether to include or exclude the header row.

Anyhow, I have included it in my selection.

Then go to Data > Data Clean-up > Remove duplicates.

It will show you the below settings with a note stating the number of rows in your selected range.

Check “Data has a header row.”

Finally, select the Remove duplicates button, and voila!

Google Sheets will pop up a message stating the number of duplicates found and removed.

Formula Alternative to Data Clean-up Single Column Remove Duplicates Menu Command: =unique(A3:A11)

Multiple Columns (Unique Way)

Here is the most valuable part of this command.

When you have multiple columns, you can remove repeated records based on all columns (Unique way) or selected columns (Sortn way).

Here is an easy-to-follow example (Unique way).

My two-column dataset contains first names in column A and last names in column B.

In this sample data, row # 3 is the repetition of row # 2.

Row # 5 is not a duplicate of row # 4 because last name mismatch.

Here we can use the Remove Duplicates Data Clean-up Menu in Google Sheets as below.

Select A1:B5 and go to the Data menu and select the said command.

Follow the screenshot.

Formula Alternative to Data Clean-up Multiple Columns Remove Duplicates Menu Command: =unique(A1:B5)

Remove Duplicates Data Clean-up Menu in Selected Columns (Sortn Way)

This sample data shows the unit price of some fruit items.

The product names repeat since different vendors submitted the quote, and the same vendor has multiple quotes (revision).

Here are a few examples to help you learn how to use the Remove Duplicates Data Clean-up menu command in selected columns.

1. Picking the Lowest Priced Products from Multiple Quotes

To do this, first, select the range A1:C7.

Then sort the data in ascending order from Data > Sort range > Advanced range sorting options.

Again select the same range and apply Data > Data clean-up > Remove duplicates.

In the dialog box that subsequently appears, select only column A.

Equivalent Sortn Formula: =sortn(sort(A2:C7,1,1,2,1),9^9,2,1,1)

Note:- If you use the formula, manual sorting is not required. It’s (SORT) already coded within.

Related:- SORTN Tie Modes in Google Sheets – The Four Tiebreakers.

2. Picking the Highest Priced Products from Multiple Quotes

Here you can simply follow all the above steps except the sort. Here sort the data in descending order.

Equivalent Sortn Formula: =sortn(sort(A2:C7,1,1,2,0),9^9,2,1,1)

3. Delete Duplicates in Remote Columns Using the Data Clean-up Command

For this example, I am using new sample data.

Assume I want to remove duplicates in two distant columns, i.e., columns A and C (please refer to the image below).

The purpose of doing so is to remove the quotes for the same product from the same vendor multiple times.

In the Remove duplicates menu, select columns A and C.

Here also, we can use Sortn, and here is that surprising formula.

=sortn(A2:C4,9^9,2,A2:A4&C2:C4,1)