Afrina
While working on Google Sheets, datasets may contain duplicate values. For small datasets, you can manually remove the duplicates. But, this will become a very tiresome job for large datasets. Fortunately, you can use formulas to remove duplicates from your dataset. In this article, we will highlight how to remove duplicates in Google Sheets with or without using formula. Follow the article to learn how to do that.
A Sample of Practice Spreadsheet
2 Simple Ways to Remove Duplicates in Google Sheets Using Formula
The dataset below contains Student’s Name and Number obtained in a test. Suppose you need to extract the unique student names from the dataset.
Let’s follow the methods below to remove the duplicates in Google Sheets using formula.
1. Using UNIQUE Function
You can use the UNIQUE function to get the desired result. This function extracts the unique values from a range of data.
- Enter the following formula in cell D5 to get the desired result as follows.
=UNIQUE(B5:B11)
Read More: How to Remove Duplicates in Google Sheets Without Shifting Cells
2. Applying COUNTIF Function
You can also use the COUNTIF function to remove duplicates. This function counts the occurrences of values within a range based on particular criteria. Follow the steps below to see how to do that.
📌 Steps:
- First, apply the following formula in cell D5 and drag the fill handle icon below. The formula will return 1 for unique values.
=COUNTIF($B$5:$B5,B5) 
- The formula also considers the first occurrence of multiple duplicate values as the unique value.
- Now you need to apply Filter to the dataset to remove the duplicates. Click anywhere in the dataset and go to Data >> Create a filter to do that.
- After that, the filter icons will appear in the header cells as shown below.
- Now click on the Filter icon in the Output column. Then uncheck all values greater than 1.
- Finally, the duplicate names will be removed from the dataset as shown below.
Read More: Google Sheets Use Filter to Remove Duplicates in Column
How to Remove Duplicates in Google Sheets Without Using Formula
Alternatively, you can use the Data cleanup feature to remove duplicates in Google Sheets without using any formula. Follow the steps below to be able to do that.
📌 Steps:
- First, select the range B4:B10 as you want to remove the duplicates from there. Then, go to Data >> Data cleanup >> Remove Duplicates.
- Next, check the Data has header row checkbox. After that, check the checkbox for the desired column name and click on Remove duplicates.
- Finally, the duplicate names will be removed as follows.
Read More: How to Find and Remove Duplicates in Google Sheets (5 Ways)
Things to Remember
- You must use the cell references properly while using the COUNTIF function. Otherwise, you won’t get the desired result.
- You need to check the specific column name while using the Data cleanup feature. Also, don’t forget to check the “Data has header row” checkbox if your dataset contains headers.
Conclusion
In this article, we have shown 2 simple ways to remove duplicates in Google Sheets using formula. We have also highlighted how to use the Data cleanup feature to do so. Hopefully, you can implement these methods to remove duplicates from your own datasets. Please let us know in the comment section if you have any queries or suggestions. You may also visit our OfficeWheel blog to explore more Google Sheets-related articles.
