How to find duplicates in google sheets?

How often do you find duplicate data in Google Sheets? If you don’t know, then you should start looking into it. Duplicate data is very common in spreadsheets, especially when they contain large amounts of information. This can cause problems when you try to sort or filter them.

Google Sheets is a free spreadsheet application developed by Google. It allows you to create and edit spreadsheets from anywhere at any time. The software also has built-in tools that allow you to perform basic functions such as sorting, filtering, and searching.

You can use the built-in functions to remove duplicated values from columns or rows. This way, you can easily spot duplicates and eliminate them before they cause confusion.

Correct data in the Google sheets on which you are working is necessary to perform the task because the incorrect or duplicate entries may cause incorrect results if any mathematical operation is performed and also it will not be possible to use the data correctly.

Duplicates mean having multiple copies of same data, some data fields are repeated, in some cells in a sheet.

If you are using google sheets regularly, then you might face some issues. Issues can be related to the duplicate entries. The situation related to duplicate entries can affect your dataset on which you have been working.

Google sheets have different ways through which one can identify, compare and then remove duplicate entries. These methods can help one to fix the issues related to the duplicates in google sheets. Each of described two methods below will let you deal with the duplication of data in a google sheet in cells.

So, let’s get started with the ways one can find duplicates in Google sheets and step by step procedure also along with the screenshots provided.

How to find duplicates in google sheets?

USING sort function in google sheets 

SORT is a function in google sheets through which by applying it on data, one can arrange the data systematically. It can be done in “Ascending order” or “Descending order” which mean ascending order is the arrangement of the data from smallest alphabetical letter to largest alphabetical letter, which is A-Z.

Descending order is the arrangement of the data from largest alphabetical letter to smallest alphabetical letter, which is Z-A. 

By using the SORT Function, one can easily review the duplicate data in rows as it display the data in some order and in repeated manner.

Using the SORT Function, one can find duplicates in a very less time as well easily as it involves very simple steps to apply the SORT Function in Google sheet. 

Step by step process to find duplicates using sort is explained also along with the screenshots provided below. 

  1. Open the Spreadsheet, which you want to analyze and find duplicates in it. 
  1. Select the data in the google sheets which you want to sort. To do so, Click on the first cell and drag it till you want to include. Some dummy data is added as shown in the screenshot to explain the procedure. Column B will be sort and duplicates will be shown. 
  1. After selecting the data, click on the “Data” option at top of the sheet and select “sort Range”. This will let open a popup window, which consists of some options. 
  1. A popup window will be display, Select the sort option according to your requirement. Here I selected “A-Z” and check the option “Data has header row”. Select Sort. Below Screenshot is attached for guidance. 
  1. The data in the “Column B” is now sorted. Below screenshot represents how the duplicates are shown continuously. Through this, one can easily find duplicates in the data. 

Using Add-Ons to Find Duplicates in Google Sheets 

With Functions, Google sheets also provide some add-ons to make tasks easier. Add ons is a software extension, which provide some extra features so that the task can be done easily. 

There are variety of add-ons which google sheets offer. One of them is “Remove Duplicates”. 

To apply “Remove Duplicates” add-on, you first need to install it. To install it, follow the below steps. 

  1. Click on Add-ons option at the top of the sheet and select “Get add-ons”. 
  1. Different add-ons will be display on the screen. 
  1. Search the add-on which is needful, here it is remove duplicates. 
  1. Select the needful add on and click on install and proceed further by providing the permissions. 

Add-on has been installed. Now need to use the add-on and find the duplicates. 

The installed add-on have different features like to find duplicates, remove duplicates etc. 

To find the duplicates using installed add-on, follow the steps explained below. 

  1. “Column A” consists of data which include duplicate entries and “Column B” will get those values which are duplicate among the data in “Column A”. 
  1. Click on the Add-ons option at the top of the sheet and select the add-on which you have installed. Installed add-on have different options to proceed. Select according to your needful. Here I have to find the duplicates so for that Select “Find duplicate or unique cells”. 
  1. After selecting the option, a popup will be displayed.Step 1 of 3 need to Select the range from which you want to find duplicates.Here in my case it is “A2:A16”. After selecting range,Click on Next. 
  1. Step 2 of 3 consist of to select the relevant option. You can select option according to your requirement, but in my case it is “Duplicates”. 
  1. Select “Duplicates” option and check “Skip empty cells” option. Click on Next. 
  1. Step 3 of 3 consists of options to where apply the functionality and how. Select “Copy to another location” and also select “Custom location” which includes the location where you want to put duplicate entries found in the process. Here in my case it is “B2:B16” in sheet named “duplicates”. Click on Finish. 
  1. Therefore, after completing the whole process, the entries, which are duplicates, will be entered in “column B”. Now “Column B” consist of entries, which are duplicates in “column A”.  

Therefore, in this way, the duplicate entries will be populated in “Column B” and one can identify easily. 

Finding Duplicates in Google sheet is a really good and efficient way to continue with doing the task. As after finding the duplicates, one can easily know if any amendment is need to be made or not. 

Once you find the duplicates in the google sheets, you can easily decide that which data need to be deleted and which entry should stay there in google sheets. 

It is really easy by using the above explained two methods to find the duplicates. Get on hands on practice by following the explained step by step procedure along with the screenshots.