Google Sheets: How to Highlight and Remove Duplicates

When working with Google Sheets, you may come across large spreadsheets where you have to deal with many duplicate entries. We understand the pain of dealing with duplicates and how difficult it becomes if you want to highlight and delete entries one by one. However, with the help of conditional formatting, highlighting and removing duplicates becomes very easy. Follow this guide and we’ll tell you how to find and delete 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 us first understand how to highlight duplicate entries in a single column. Please follow the steps below.

1. Open the spreadsheet in Google Sheets and select a column.
2. For example, select Column A > Format > conditional formatting.
3. Under Formatting Rules, open the drop-down list and select The custom formula is.
4. Enter the value of the custom formula, =countif(A1:A,A1)>1.
5. Under Formatting Rules, you can find Formatting Styles, which allow you to set a different color for highlighted duplicates. To do this, click fill color icon and choose your preferred color.
6. When finished, click complete Highlight duplicates in a single column.
7. Likewise, if you had to do this for column C, the formula would become: =countif(C1:C,C1)>1 Do the same for the other columns.

Also, there is a way to find duplicates in the middle of a column. To learn, follow the steps below.

1. Suppose you want to highlight duplicates between cells C5 to C14.
2. In this case, please visit Format and select conditional formatting.
3. Under Apply to range, enter the data range, C5:C14.
4. Next, under Formatting Rules, open the drop-down list and select The custom formula is.
5. Enter the value of the custom formula, =countif(C5:C,C5)>1.
6. If you wish, you can follow the previous steps to set a different color for the highlighted duplicates. When finished, click complete.
7. If you wish, you can follow the previous steps to set a different color for the highlighted duplicates. When finished, click complete.

Google Sheets: How to highlight duplicates in multiple columns

If you want to highlight duplicates in multiple columns and rows, follow the steps below.

1. Open a spreadsheet in Google Sheets and select multiple columns.
2. For example, select columns B to E > click Format > Click conditional formatting.
3. Under Formatting Rules, open the drop-down list and select The custom formula is.
4. Enter the value of the custom formula, =countif(B1:E,B1)>1.
5. If you wish, you can follow the previous steps to set a different color for the highlighted duplicates. When finished, click complete.
6. Likewise, if you want to select duplicates of columns M through P, you can replace B1 with M1 and E with P. The new formula becomes: =countif(M1:P,M1)>1.
7. Furthermore, if you want to highlight duplicates for all columns from A to Z, just repeat the previous steps and enter the value of the custom formula, =countif(A1:Z,A1)>1.

Google Sheets: Remove duplicates from spreadsheets

Once you’ve highlighted duplicate entries in your spreadsheet, the next step is to delete them. Please follow the steps below.

1. Select the column from which you want to remove duplicates.
2. Click data > Remove duplicates.
3. You will now see a pop-up window. Tick The box next to the data now has a title > click Remove duplicates > Click complete.
4. You can repeat these steps for other columns as well.

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

For more tutorials, visit our How-To section.

Are iPhone 12 mini, HomePod mini the best Apple devices for India? We discuss this on Orbital, our weekly technology podcast, which you can subscribe to via Apple Podcasts, Google Podcasts, or RSS, download the episode, or just hit the play button below.