Google Sheets: How to Highlight and Remove Duplicates

Conditional formatting makes it very easy to highlight duplicates in Google Sheets.

While working in Google Sheets, you may find large spreadsheets where you have to deal with a lot of duplicate entries. We understand the pain of dealing with duplicates and how difficult it can be if you highlight and remove entries one by one. However, conditional formatting makes it much easier to highlight and remove duplicates. Follow this guide as we show you how to find and remove duplicate entries in Google Sheets.

Google Sheets: How to highlight duplicates in a single column

Before we learn how to remove duplicate entries from Google Sheets, let's learn how to highlight duplicates in a column. Follow these steps.

1. Open your spreadsheet in Google Sheets and select a column.
2. For instance, select column A > Format > Conditional formatting.
3. Under Format rules, open the drop-down list and select Custom Formula.
4. Enter the Value for the custom formula, =countif(A1:A, A1)>1.
5. Below Format rules, you can find Formatting styles, which lets you set a different color for highlighted duplicates. To do that, hit the Fill color icon and select your preferred shade.
6. Once you're finished, hit Done to highlight the duplicates in a single column.
7. Similarly, if you have to do it for column C, the formula becomes, =countif(C1:C, C1)>1 and you do it so for other columns as well.

Also, there is a way to find duplicates between columns. To learn, follow these steps.

1. Let's assume you wish to highlight duplicates between cells C5 to C14.
2. In that case, go to Format and select Conditional Formatting.
3. Under Apply to the range, enter the date range, C5:C14.
4. Next, under Format rules, open the drop-down list and select Custom Formula.
5. Enter the Value for the custom formula, =countif(C5:C, C5)>1.
6. Set a different color for the highlighted duplicates by following the earlier steps if you wish to. Once you're finished, hit Done.
7. Set a different color for the highlighted duplicates by following the earlier steps if you wish to. Once you're finished, hit Done.

Google Sheets: How to highlight duplicates across multiple columns

Only if you want to highlight duplicates in multiple columns and rows, follow these steps.

1. Open your spreadsheet in Google Sheets and select multiple columns.
2. For instance, select columns from B to E > click Format > click Conditional formatting.
3. Under Format rules, open the drop-down list and select Custom Formula.
4. Enter the Value for the custom formula, =countif(B1:E, B1)>1.
5. Set a different color for the highlighted duplicates by following the earlier steps if you wish to. Once you're finished, hit Done.
6. Similarly, if you want to select the duplicates for columns M to P, then you replace B1 with M1 and E with P. The new formula becomes, =countif(M1:P, M1)>1.
7. Besides, if you want to highlight duplicates for all the columns from A to Z, simply repeat the earlier steps and enter the Value for the custom formula, =countif(A1:Z, A1)>1.

Google Sheets: Remove duplicates from a spreadsheet

After highlighting duplicate entries in your spreadsheet, the next step is to delete them. Follow these steps.

1. Select a column from where you want to remove the duplicates.
2. Click Data > Remove duplicates.
3. You will now see a pop-up. Tick the box next to Data has header now > click Remove duplicates > click Done.
4. You can repeat the steps for other columns as well.

This way you can highlight and remove duplicates in Google Sheets.