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:
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:
- Select the columns in which you want to find duplicate data.
- Now the next step is how to highlight duplicate entries. For this after selecting columns go to Format then choose Conditional Formatting as:
- 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.
- Then the duplicate entries will be highlighted as:
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.
- Select the data in which you want to delete duplicates.
- After selecting go to the data tab, select data cleanup, and choose “remove duplicates” as shown below:
- In the “Remove duplicates” dialog box, ensure ‘Select all’ is selected.
- Click on the Remove Duplicate.
- You will get the following prompt then click on OK.
- 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.
With over two decades of experience in writing about Microsoft Excel, Google Sheets, and various other spreadsheet tools, Muhammad Nadeem Salam is your go-to expert for all things data. Since 2004, he has been passionately sharing his knowledge and insights through engaging and informative blog posts, helping countless readers unlock the full potential of their spreadsheet tools.
