How to Find Duplicates in Google Sheets (Easy Method)

Find and highlight duplicate data.
Michael Michael (179)
0

In a long list, it can be frustrating to scroll through endless items to find duplicates in Google Sheets. We often use Google Sheets when recording ideas, but we don't want to record the same idea twice. So we'll need to highlight, and, in some cases, remove the duplicate data from a column or row.

In this guide, we'll show you how to easily find duplicates in a Google spreadsheet, highlight them, and then remove them (if that's your intention).

Deleting duplicate data in Google Sheets

If you know that you'll be deleting duplicate data, then we recommend you skip ahead to that step.

Google WorkspaceGoogle Workspace ×1

Howchoo is reader-supported. When you buy through links on our site, we may earn a small affiliate commission at no cost to you.

Duplicates in Column Google Sheets

To find duplicate data in a column of a Google Sheet:

  • Highlight the column by clicking on the corresponding letter above it.
  • Go to Format.
  • Select Conditional formatting.
  • Under "Format rules," select Custom formula is....
  • Input a version of the following formula, depending on the letter of the column you've highlighted. (In this formula, we selected column A.) You'll need to change the letter if you're using a different column.
=countif($A$1:$A$1000,A1)>1
  • Click Done.

You may also need to change the formula if your column starts at a different value.

duplicates in rows Google Sheets

To find duplicate data in a row of a Google Sheet:

  • Highlight the row by clicking on the corresponding number next to it.
  • Go to Format.
  • Select Conditional formatting.
  • Under "Format rules," select Custom formula is....
  • Input a version of the following formula, depending on the row you've highlighted. (In this formula, we selected row 1.) You'll need to change both numbers that follow the letters if you're using a different column.
=countif($A$1:$Z1,A1)>1
  • Click Done.

In the above, if you want to search row 2, for example, then your formula would be =countif($A$2:$Z2,A2)>1.

Remove duplicates google sheets

If all you need to do is remove duplicates from a column or row, then it's even easier than finding and highlighting them.

  • Highlight the row or column.
  • Go to Data.
  • Select Remove duplicates.
  • Make sure that you're selecting the right column(s) or row(s).
  • Click Remove duplicates.

You'll be shown how many duplicates are removed. By default, duplicates are removed in Google Sheets, leaving the first instance of the data.

  • Click OK.
Expand what you can do in Google Sheets!
Britt Britt (54)
1 minute

Google Sheets makes it easy to create data spreadsheets that can be used by a team of collaborators.