When using Google Sheets, you may encounter large spreadsheets in which you have to deal with many duplicate entries. We understand the pain of dealing with duplicates and how difficult it can be if you want to highlight and delete entries. However, with conditional formatting, it is very easy to highlight and delete duplicates. Please follow this guide because we tell you how to find and delete duplicate entries in Google Sheets.

Google Sheets: How to highlight duplicates in a single column

Before knowing how to remove duplicates from Google Sheets, let us learn how to highlight duplicates in a single column. Follow these steps.

  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 that allow you to set different colors for highlighted duplicates.To do this, click Fill color​​​ Icon and choose the shade you like.
  6. When finished, click finished Highlight duplicates in a single column.
  7. Similarly, if you have to operate on column C, the formula becomes = countif(C1: C, C1)> 1 You can also do this in other columns.

In addition, 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 the duplicates between cells C5 to C14.
  2. In this case, please go to format Then choose 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 want, you can follow the previous steps to set a different color for the highlighted copy.When finished, click finished.
  7. If you want, you can follow the previous steps to set a different color for the highlighted copy.When finished, click finished.
See also  Tokyo 2020 Olympics: Google Dinosaur Games now features Olympic torches and other mini games

Google Sheets: How to highlight duplicates in multiple columns

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

  1. Open the spreadsheet in Google Sheets and select multiple columns.
  2. For example, select columns from 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 want, you can follow the previous steps to set a different color for the highlighted copy.When finished, click finished.
  6. Similarly, if you want to select duplicates in columns M to P, you can replace B1 with M1 and E with P. The new formula becomes: = countif(M1: P, M1)> 1.
  7. In addition, if you want to highlight the duplicates of all columns from A to Z, just repeat the previous steps and enter the “value” for the custom formula, = countif(A1: Z, A1)> 1.

Google Sheets: Remove duplicates from spreadsheet

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

  1. Select the column from which you want to remove duplicates.
  2. Please click data > Delete duplicates.
  3. You will now see a pop-up window. tick The box next to the data now has a title> click Delete duplicates >click finished.
  4. You can also repeat the above steps for other columns.

This will highlight and delete duplicates in Google Sheets.

For more tutorials, please visit our “how to” section.


Is the iPhone 12 mini and HomePod mini the perfect Apple device in India? We discussed it on the weekly technical podcast Orbital, you can subscribe via Apple Podcast, Google Podcast or RSS, download the episode, or click the play button below.

For the latest technical news and reviews, please follow Advertisement Shout on Twitter, Facebook and Google News. For the latest videos on gadgets and technologies, please subscribe to our YouTube channel.

Aman Rashid

Google Messages SMS category feature is being rolled out to more users

Related stories