How to Remove Duplicates in Google Sheets

Google Sheets

Welcome to Excel Dose! In this blog you will learn two ways to remove duplicate data in Google Sheets. Let’s checkout how to do it.


Table of content:

  • About duplicate data
  • Method 1 – Using UNIQUE Function
  • Method 2 – Using “Remove Duplicate” Option
  • Method 3 – Using “Cleanup Suggestions” Option
  • Video on removing duplicate data
  • About the author

About Duplicate Data

While working on the Google Sheets, sometime we might ended up with having duplicate data in same Column or same Row.

Example: Data of “Employee ID 1” is repeated two times in the same dataset. It is having same Name and Phone Number.

How to Remove Duplicates in Google Sheets

Method 1 – Using UNIQUE Function

First way to remove duplicate data in Google Sheet is using “UNIQUE” function. The UNIQUE function is most advance and powerful function in Google Sheets. Basic syntax for UNQIUE function is:

  • Range – Return unique row from the dataset while removing the duplicate entries.
  • by_column – This attribute is used weather to filter the data by rows or columns. This is optional attribute and by default it is false.
  • exactly_once – This attribute is used weather to return only entires that are not duplicates in the dataset. This is optional attribute and by default it is false.

Remove duplicate data in Google Sheets

For the above mentioned example, the raw data has been mentioned in range from “E1:G7”. To get the unique values we have used the UNIQUE function in Cell A1 and then gave the first argument as the range of ‘Raw Data’. After hitting the “Enter” button you will get only the unique data. Below is the formula used:


Method 2 – Using “Remove Duplicate” Option

Another way to remove duplicate from dataset is using “Remove Duplicate” option in Google Sheets. The Google Sheets is having inbuilt option (like Excel) to remove the duplicate data by column or by row.

Steps:

  1. To navigate to this option follow below path.

remove duplicates in google sheets

2. Once you click in the “Remove duplicates” option, then a new popup window will appear and make sure all checkbox are selected

remove duplicates in google sheets

3. Once you click on “Remove duplicates” button, you will see a popup window which will show how many duplicate values are found in the raw data all the duplicate values will be removed from the raw dataset. Post you click on the ‘OK’ button you will get only the unique values.

remove duplicates in google sheets


Method 3 – Using “Cleanup Suggestions” Option

There is another method to remove duplicates is using “Cleanup Suggestions” option in the Google Sheets.

Steps:

  1. To navigate to this option follow below path.

remove duplicates in google sheets

2. Once you click on “Cleanup suggestions”, new window will be appear in the right hand side. There you can remove the duplicate values from the sheet.

remove duplicates in google sheets

Good thing about this option is that you can see which row is duplicate in dataset. Based on that you can take your action.


Video of removing duplicates in Google Sheets

We have also created a video on how you can use remove the duplicate values in Google Sheets and get only the unique values. You can watch it on our YouTube channel. For more educational videos you can subscribe to our official YouTube channel.


About the Author

Vishal Pooner has over 12+ years of experience in Digital Marketing and automating existing workflow using the Google AppScript. You can follow him on LinkedInYouTubeInstagram.

If you need our help to make productivity tools or automate existing workflows for your business. Please write an email to info@exceldose.com


Contact Info

Let's Chat!

Get in touch with us to discuss your needs. Write an email to us at