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.
Suppose you have a dataset as shown below:
In this data, there are repetitions in names (Mark and Brenda).
Now let’s see how you can use the UNIQUE function to remove duplicates in Google Sheets.
Let’s say you only want to remove the duplicates from the Name column.
Here are the steps to do this:
This would instantly remove the duplicates and give you a list of unique names.
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.
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.
Here is how to handle this:
This would automatically account for any leading, trailing, or double spaces and give you the final result after removing these.
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:
In this data, there are two duplicate rows (highlighted in orange and green).
Here are the steps to remove these duplicate rows:
If there are leading, trailing or double spaces, use the following formula:
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:
How to Create a Drop Down List in Google Sheets
Using IMPORTFEED in Google Sheets to Fetch Feed from URL
Using Query Function in Google Sheets – Examples
Concatenate in Google Sheets – Combine Cells Using Formula
How to Zoom In and Zoom Out in Google Sheets