GadgetARQ Logo
Close this search box.

How to find duplicates in Google Sheet?


Google Sheets is a spreadsheet program included as a part of the free, online Google Docs Editors suite presented by Google. The service also includes Google Docs, Google Slides, Google Docs, Google Forms, Google Sites, and Google Keep. Google is helpful to save your data as a spreadsheet or data analysis. We can use it in the mark sheet or to make any formal report for your organization. In addition, Google Sheet makes the complex work easier and more efficient for any individual. Google Sheets allows users to edit, organize, and analyze various kinds of data. It allows collaboration, and various users can change and organize documents progressively, and any progressions made to the spreadsheet can be tracked by a revision history. Here’s how you find duplicates in Google Sheet.

The problem of Duplicate Cell

If you are a regular user of Google Sheet you may probably face the problem, where you accidentally enter a similar type of data on your spreadsheet, which turns out to be a duplicate cell. This is quite disturbing for users. This happens when you are working with a massive amount of data in your spreadsheet. But Google Sheets has given a feature to remove these duplicate cells from your spreadsheet.

Several Methods to Remove Duplicates in Google Sheet

  • By default Remove Duplicates Feature is given in Google Sheet.
  • Use highlighting to find duplicates.
  • Copy unique cells and move them to a new sheet.
  • Also, use a third-party duplicate finder.
  • Create a Pivot Table that counts duplicates on an individual basis.

The above-given ways are given, through which you can find and remove the duplicate cell from a Google Sheet.

Use the built-in Remove Duplicates Feature

This Remove Duplicates feature accurately helps you to find out a duplicate cell in an entire row, column, or an entire worksheet. However, you should aware of the idea that it would remove all duplicates, even if they are not associated with the same data.

Follow the given below steps

  1. Highlight the sections you need to check for duplicate data.

2. In the menu at the top, select “Data– > Data cleanup,” and afterward pick “Remove duplicates.”


3. A dialogue will pop up. Mark the boxes close to every column in the list you need to check, or you can verify “Select All” and afterward click “Remove duplicates.”


4. Google Sheets shows the number of duplicates that were found and removed so you can guarantee the process performed as intended.


Google Sheet Built-in feature “Find and Remove duplicates” is the most effortless and efficient way to remove duplicates. However, sometimes you need to review those duplicates before removing them which is not possible with this feature. On the other hand to get the solution to the problem you can use this color highlighting feature to review your cell.

Highlight Your Duplicate Using Colour Highlighting Feature

When you want to highlight your incorrect data from your spreadsheet you can use a Colour Highlighting feature in Google Sheet.

Follow the given below steps

  1. Open your “Google Sheets document.” and highlight the “column or columns” you need to modify.

2. At the top of the menu bar, select “Format

Google dupicate

3. After that in the drop-down menu, select “Conditional Formatting“.

Google duplicate

4. Now select the “Range” as per your choice from the new menu that appears.

google duplicate

5. Under “Format rules,” change the drop-down section titled “Format cells if…” to “Custom formula is.”

google duplicate

6. And then write the following formula into the box under the “Custom formula is” option:

google duplicate

7. Under the “Formatting style” section, select the Fill color icon to highlight your data with a yellow (or any color of your choice) cell background color.

Google duplicate

8. Afterwards click on Done to save changes, review your duplicates, and delete those you find as legitimate copies.

This feature is good to highlight a column’s cells beginning with column 1 rather than picking cells in the middle. However, The formula does not like using row 2 as the first column. It missed two or three duplicates in the selected field (A2:B9). When row 1 was added (A1:B9), it tracked down all duplicates.

Copy Unique Cells in Google Sheets for Easy Removal of Duplicates

You can use this feature if you prefer to sort your raw data automatically rather than highlight your duplicate cell, it is best to copy all your unique cells. However, if you are having a surety about the correct information on your spreadsheet. This method is faster for sorting and filtering.

Follow the given below steps

  1. Firstly open the Sheets document you would wish to sort. And then highlight the “column” you want to edit. This process records the column range for the next step.
google duplicate

2. Click on a blank column” where you want to place your unique entries too. While highlighted (do not click any cell), Afterwards write the following formula: =UNIQUE().

google duplicate

3. Type the cell coordinates of the original column inside the parenthesis, for example: (A2:A10).

google duplicate

4. Afterwards Press Enter to move your new data to the column that you specified earlier.

google duplicate

This method reduces the time of finding the duplicate cells on your spreadsheet. Besides you can easily import your data to your workspace.

Causes of Duplicate Cells

  • User input error especially while you are entering Financial data in Google Sheet.
  • Data import and export can also cause duplicate data entry in sheets.
  • The copy / Paste process can also cause duplicate cells.
  • It can also cause mistakes by your team.


In Google Sheet, you can easily remove, identify and delete duplicate data in your sheet. Moreover, these features make your work easier and faster. Because a duplicate cell is one of the extensive problems while you are entering your data in the sheet, this will create mishaps during your work and also for your organization.

This will make your workflow smoother and easier. If you are looking for something to sort content quicker and faster, you can always use a built-in feature “Remove highlight” or copy “Unique Cell” to ensure your information is well-sorted and organized.

Read More:





Subscribe to Updates

Get the latest news about technologies into your mailbox.



Subscribe to Updates

Get the latest news about technologies into your mailbox.