How to Highlight and Remove Duplicates in Google Sheets?

If you have a large spreadsheet with lots of data, it can be helpful to highlight any duplicates that appear. This way, you can easily spot them and take action accordingly. Thankfully, Google Sheets makes it easy to do this with a built-in feature.

Finding duplicates in Google Sheets in a single column

Let’s look at an example, of how to highlight duplicates in Google Sheets if the search is performed on one column.

How to find duplicates in Google Sheets:

1. We open the table where we will look for duplicates.
2. We select the fragment in which we will perform the search (not necessary, you can do it later).
3. Click on the “Format” tab and select the “Conditional Formatting” element.
4. In the “Apply to range” block, specify the search area. In this example, the search is only possible within one column.
5. In the “Format Rules” line, select “Custom Formulas is” from the drop-down list.
6. Where there is a field for the formula, insert =COUNTIF(A:A,A1)>1. If your column is not A then changes it to your letter.

Search for duplicates in Google Sheets across multiple columns

Google Sheets offers a few different ways to find and highlight duplicates in your spreadsheet. The easiest way is to use the built-in conditional formatting feature. The principle is similar to the one described earlier, but the formula and search field will be different if you need to search multiple columns and rows at once.

How to check duplicates in Google Sheets:

1. Go to the “Format” tab and select “Conditional Formatting” from the drop-down list.
2. In the “Apply to range” field, click on the square icon and specify the field in which the search will be performed. You can just put in numbers. In our example – A1:F13.
3. In the “Format Rules” line, select the “Custom Formulas is” option.
4. Use the formula =COUNTIF($A$2:G,Indirect(Address(Row(),Column(),)))>1.
5. Remove duplicates from a spreadsheet

How to remove duplicates?

Removing duplicates is important in order to maintain accurate data. The easiest way to remove duplicates is to use the built-in Google Sheets feature.

How to remove duplicates:

1. Open the “Data” tab.
2. Hover over the “Data cleanup” option.
3. Click on the “Remove Duplicates” option.

There are more advanced ways to find, underline, and remove duplicates, but simple formulas are more than enough. If necessary, they can be modified to suit your needs.