How to Remove Duplicates in Google Sheets

how-to-remove-duplicates-in-google-sheets

If you want to remove duplicates in Google Sheets, you need to use the UNIQUE function. This function extracts all the unique values and puts it in another place on the sheet.

This is very different than the way you can remove duplicates in MS Excel. In Excel, there is an option in the data tab to remove duplicates.

See Also: How to Find and Remove Duplicates in Excel.

Remove Duplicates in Google Sheets

Suppose you have a dataset as shown below:

How to Remove Duplicates in Google Sheets - Dataset

In this data, there are repetitions in names (Mark and Brenda).

How to Remove Duplicates in Google Sheets - Dataset highlighted

Now let’s see how you can use the UNIQUE function to remove duplicates in Google Sheets.

Example 1: Remove Duplicates from a Single Column

Let’s say you only want to remove the duplicates from the Name column.

Here are the steps to do this:

  • Select a cell where you want to get the list of unique items/names.
  • Enter the following formula: =UNIQUE(A2:A10)
  • Hit Enter.

This would instantly remove the duplicates and give you a list of unique names.

 

How to Remove Duplicates in Google Sheets - Single Column

Note: If you want to remove the list, delete the first cell, or select the entire range and hit delete. Google Sheets will not let you delete individual cells (other than the first one – which would delete the entire list).

Since this is a formula, it would automatically update if you make any changes in the original list.

UNIQUE function returns a #REF! error if there is already some data in the cells that are supposed to be filled by the unique function.

If you hover the mouse over the cell with error, it will show a message stating the issue.

How to Remove Duplicates in Google Sheets - Error

Handling Leading and Trailing Spaces

Another issue you may face is when there are leading or trailing spaces. For example, if the example below, there is a trailing space in Brenda (in A10). When the UNIQUE function is used on this data set, it considers both the names as unique and returns both the names in the result.

How to Remove Duplicates in Google Sheets - Trailing Space

Here is how to handle this:

  • Go to the cell where you want to get the list of unique items/names.
  • Enter the following function: =UNIQUE(TRIM(A2:A10))
  • While in the eidt mode, press Ctrl + Shift + Enter. It would change the formula to =ArrayFormula(UNIQUE(TRIM(A2:A10))).How to Remove Duplicates in Google Sheets - Using with TRIM
  • Press Enter.

This would automatically account for any leading, trailing, or double spaces and give you the final result after removing these.

Example 2: Remove Duplicates from Multiple Columns

If you have more than one columns, you can use the above method to remove duplicate rows and get unique data.

Suppose you have the data as shown below:

How to Remove Duplicates in Google Sheets - Dataset highlighted

In this data, there are two duplicate rows (highlighted in orange and green).

Here are the steps to remove these duplicate rows:

  • Select a cell where you want to get the list of unique items/names.
  • Enter the following formula: =UNIQUE(A2:B10)
  • Hit Enter.

How to Remove Duplicates in Google Sheets - Single Column

If there are leading, trailing or double spaces, use the following formula:
=ArrayFormula(UNIQUE(trim(A2:B10)))

I hope this helps you cleaning your data in Google sheets. Do let me know your thoughts by leaving a comment.

You may Also Like the Following Google Sheets Tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X