How to Highlight and Remove duplicates in google sheet

by

While working in 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 would be if you had to highlight and remove entries one by one. However, with the help of conditional formatting, highlighting and removing duplicates becomes quite easy. Follow this guide as we tell you how to find and remove duplicate in Google Sheets.

How to Highlight Duplicates in a Single Column in Google Sheets

Before you know how to remove duplicate entries from Google Spreadsheets, let’s learn how to highlight duplicates in a single column. Follow these steps.

  1. Open your spreadsheet in Google Sheets and select a column.
  2. For example, select column A > Format > Conditional format.
  3. Under Formatting Rules, open the drop-down list and select The custom formula is.
  4. Enter the value for the custom formula, = countif (A1: A, A1)> 1.
  5. Below the formatting rules, you can find formatting styles, which allow you to set a different color for highlighted duplicates. To do that, press the Fill color icon and select your preferred ringtone.
  6. Once you’re done, press Done to highlight duplicates in a single column.
  7. Likewise, if you have to do it for column C, the formula becomes, = countif (C1: C, C1)> 1 and it does it for other columns as well.

Furthermore, there is also a way to find duplicates in the middle of the columns. To learn, follow these steps.

  1. Suppose you want to highlight duplicates between cells C5 to C14.
  2. In that case, go to Format and select Conditional format.
  3. In Apply to range, enter the data range, C5: C14.
  4. Then under Formatting Rules, open the drop-down list and select The custom formula is.
  5. Enter the value for the custom formula, = count if (C5: C, C5)> 1.
  6. Set a different color for the highlighted duplicates by following the steps above if desired. Once you’re done, press Done.
  7. Set a different color for the highlighted duplicates by following the steps above if desired. Once you are done, press Done.

How to Highlight Duplicates in Multiple Columns in Google Sheets

In case 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 example, select columns B through E> click Format > click Conditional format.
  3. Under Formatting Rules, open the drop-down list and select The custom formula is.
  4. Enter the value for the custom formula, = count if (B1: E, B1)> 1.
  5. Set a different color for the highlighted duplicates by following the steps above if desired. Once you are done, press Done.
  6. Likewise, if you want to select duplicates for column M to P, replace B1 with M1 and E with P. The new formula becomes, = count if (M1: P, M1)> 1.
  7. Also, if you want to highlight duplicates for all columns from A to Z, just repeat the above steps and enter the Value for custom formula, = countif (A1: Z, A1)> 1.

Remove duplicates in google sheet

Once you’ve finished highlighting the duplicate entries in your spreadsheet, the next step is to remove them. Follow these steps.

  1. Select a 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 Data has a header now> click Remove duplicates > click Done.
  4. You can also repeat the steps for other columns.

This is how you can highlight and remove duplicates in Google Sheets.

Leave a Comment