How to highlight duplicates in Google Sheets

1Highlight duplicates in one column

1. Select the cells to check, for example A1:A200.
2. Open Format > Conditional formatting.
3. Under Format rules, open the "Format cells if" list and choose "Custom formula is".
4. Enter the formula below, choose a fill color, and click Done.

=COUNTIF($A:$A,$A1)>1

COUNTIF counts how often the cell's value appears in column A. More than once means it is a duplicate. Write the formula for the first cell of your selection: if the range starts at A2, use $A2.

2Highlight whole rows where a value repeats

1. Select all the columns of the table, for example A2:D200.
2. Open Format > Conditional formatting and choose "Custom formula is".
3. Enter the formula below. The $ before A keeps every cell in the row checking column A.

=COUNTIF($A$2:$A$200,$A2)>1

To treat a row as a duplicate only when two columns repeat together, such as name and email, use COUNTIFS: =COUNTIFS($A$2:$A$200,$A2,$B$2:$B$200,$B2)>1.

3Highlight only the second and later copies

1. Select the column, starting at the first data row, for example A2:A200.
2. Open Format > Conditional formatting and choose "Custom formula is".
3. Enter the formula below. The range starts locked at $A$2 and grows one row at a time, so it only counts the rows above and including the current one.

=COUNTIF($A$2:$A2,$A2)>1

The first occurrence stays unhighlighted and every repeat is colored, which shows exactly which rows you could delete.

4Remove duplicate rows

1. Select the data range, including the header row.
2. Open Data > Data cleanup > Remove duplicates.
3. Tick "Data has header row" if it does, choose which columns must match for rows to count as duplicates, and click Remove duplicates.

This deletes rows in place and keeps the first copy of each. If you need the original later, File > Version history has it, or work on a copy of the tab.

5Use formulas instead: flag, count or list

1. Flag duplicates in a helper column next to the data: =COUNTIF($A$2:$A$200,A2)>1 returns TRUE for every value that appears more than once.
2. List each value once with =UNIQUE(A2:A), which leaves the original data untouched.
3. Count distinct values with =COUNTUNIQUE(A2:A).

=UNIQUE(A2:A)

Formulas update as data changes, so they suit shared sheets where new rows keep arriving. Clean stray spaces first: "Acme" and "Acme " are different values to COUNTIF and UNIQUE.

Practice the formulas

Conditional formatting runs the same formula for every cell. Write it in a helper column first and you can see exactly which rows it flags. Each answer is graded in Sheets mode, then re-checked on changed data.

The full lesson on UNIQUE, COUNTUNIQUE and duplicates →