Spreadsheets Expert

SpreadSheets Expert

How to Find and Remove Duplicates in Google Sheets?

If you are a regular Google Sheets user then you might face a problem of duplicate entries in your spreadsheet. When you store the same data in the same database then duplicate entries occur. This may occur accidentally.

Table of Contents:

  1. How to Find and Remove Duplicates in Google Sheets?
    1. Steps to Find & Highlight Duplicates in Google Sheets
    2. Steps to Remove Duplicates in Google Sheets
  2. FAQs
  3. Wrap Up

For example, data may become duplicated as a result of human error where an individual may enter data more than one time in the same database. It is important to find and remove duplicate entries to get a more accurate and general prediction of desired data.  Check out here how you can find and remove duplicates in Google Sheets.

How to Find and Remove Duplicates in Google Sheets?

Here are some steps in which you can use conditional formatting to find and remove duplicates in Google Sheets.

Steps to Find & Highlight Duplicates in Google Sheets:

  1. Select the columns in which you want to find duplicate data.
how to find duplicates in google sheets
  1. Now the next step is how to highlight duplicate entries. For this after selecting columns go to Format then choose Conditional Formatting as:
choose conditional formating to find duplicates in google sheets
  1. Enter a custom formula in the format rules box and enter formula  =countif(range)>1 for example range is A: B21 then the formula will be written as =countif($A$1:$B$21,A1)>1 and select default and done the formula.
use custom formula to find duplicates in google sheets
  1. Then the duplicate entries will be highlighted as:
final how to find duplicates in google sheets

In the next section, we will tell you how to remove duplicates in Google Sheets.

Steps to Remove Duplicates in Google Sheets:

Follow the steps below to remove duplicates in Google Sheets.

  1. Select the data in which you want to delete duplicates.
how to remove duplicates in google sheets
  1. After selecting go to the data tab, select data cleanup, and choose “remove duplicates” as shown below:
choose remove duplicates option in data tab
  1. In the “Remove duplicates” dialog box, ensure ‘Select all’ is selected.
select all to remove duplicates in google sheets
  1. Click on the Remove Duplicate.
final how to remove duplicates in google sheets
  1. You will get the following prompt then click on OK.
how to remove duplicates in google sheets
  1. Your duplicate entries in Google Sheets will be removed.

FAQs:

Q: Can you remove duplicates in Google Sheets?

Yes, you can easily find duplicates in Google Sheets. Google Sheets does not have a built-in function to identify duplicates just like Excel has. But in the current blog, we have shown you how to find and highlight duplicates in Google Sheets in one or two columns.

Q: Is there a quick way to find duplicates in Google Sheets?

Yes, you can find duplicates using the following formula in Google Sheets. Enter the following formula in the Custom Formula field of Conditional formatting. It will highlight all the duplicate cells in the given range.

=countif($A$2:$A$999,A2)>1

Conclusion:

In this article, we have demonstrated how to find and remove duplicates in Google Sheets by using Conditional formatting. This is the easiest way to remove duplicates in Google Sheets. Now you will be able to find and remove duplicates in your Google Sheets by using the formula.

Leave a Comment