Learn how to highlight and remove duplicates in Google Sheets

While working in Google Sheets, you may come across 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 would be to individually highlight and remove entries. However, with the help of conditional formatting, highlighting and removing duplicates becomes pretty easy. Follow this guide to learn how to find and remove duplicate entries in Google Sheets.

Google Sheets: How to Mark Duplicates in a Single Column

Before you know how to remove duplicate entries from Google Sheets, learn how to highlight duplicate entries in a single column. Follow these steps.

  1. Open your spreadsheet in Google Sheets and select a column.
  2. For example, choose Column A. > format > Conditional formatting.
  3. Under Format Rules, open the drop-down list and select Custom formula is.
  4. Enter the value for the custom formula. = countif (A1: A, A1)>1.
  5. Below the formatting rules are formatting styles that allow you to set a different color for highlighted duplicates. To do that, press the Fill color Icon and choose your preferred shade.
  6. When you’re done, hit Done to highlight the duplicates in a single column.
  7. If you need to do this for column C, the formula is also: = countif (C1: C, C1)>1 and you do the same for other columns.

There is also a way to find duplicates in the middle of the columns as well. Follow these steps to learn.

  1. Let’s say you want to highlight duplicates between cells C5 through C14.
  2. In that case go to format and select Conditional formatting.
  3. Under Apply to Range, enter the data range. C5: C14.
  4. Next, under Format Rules, open the drop-down list and select Custom formula is.
  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 previous steps if you want. When you’re done, hit Done.
  7. Set a different color for the highlighted duplicates by following the previous steps if you want. When you’re done, hit Done.

Google Sheets: How to Mark Duplicates in Multiple Columns

Follow the steps below if you want to highlight duplicates in multiple columns and rows.

  1. Open your spreadsheet in Google Sheets and select multiple columns.
  2. For example, select columns from B to E>click format > click Conditional formatting.
  3. Under Format Rules, open the drop-down list and select Custom formula is.
  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 previous steps if you want. When you’re done, hit Done.
  6. If you want to select the duplicates for columns M through P, replace B1 with M1 and E with P. The new formula is: = countif (M1: P, M1)>1.
  7. Also, if you want to mark duplicates for 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 a spreadsheet

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

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

This is a great way to mark and remove duplicates in Google Sheets.

For more tutorials, see our How To section.


Are iPhone 12 Mini, HomePod Mini the Perfect Apple Devices for India? We discussed this on Orbital, our weekly technology podcast, which you can subscribe to via Apple Podcasts, Google Podcasts, or RSS, Download the episodeor just hit the play button below.

Follow Gadgets 360 for the latest technical news and reviews Twitter, Facebook, and Google news. Subscribe to our latest videos on gadgets and technology Youtube channel.

Aman Rashid

The “SMS categories” function for Google messages is made available to more users

similar posts

.

Source link : https://gadgets.ndtv.com/how-to/features/highlight-duplicates-in-google-sheets-conditional-formatting-how-to-2319552#rss-gadgets-all