# How to highlight duplicates in Google Sheets

> Google Sheets has no single "highlight duplicates" button, but a conditional formatting rule with a short COUNTIF formula does the job, and Data cleanup can remove duplicate rows. Below are the methods step by step, then graded practice on the formulas, which you can also use in a helper column to flag, count or list duplicates.

Source: https://spreadsheetpractice.com/google-sheets/highlight-duplicates · Updated October 29, 2026

## The short answer

Select the column, open **Format > Conditional formatting**, choose **Custom formula is** and enter `=COUNTIF($A:$A,$A1)>1`. Every value that appears more than once gets the fill color.

## 1. Highlight 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.

## 2. Highlight 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.

## 3. Highlight 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.

## 4. Remove 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.

## 5. Use 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

5 graded exercises on these formulas are on [the duplicates guide](https://spreadsheetpractice.com/google-sheets/highlight-duplicates).

## Questions

### Why is my duplicate rule highlighting the wrong cells?

The formula is written for the first cell of the selected range. If you selected A2:A200 but wrote $A1, every result is shifted by one row. Match the row in the formula to the first row of the range.

### Can I highlight duplicates across two columns?

Yes. Select both columns and use =COUNTIF($A$2:$B$200,A2)>1 to flag any value that appears anywhere in either column, or COUNTIFS to flag rows where both columns repeat together.

### Does Remove duplicates keep the first or the last copy?

It keeps the first copy, counting from the top of the selection, and deletes the rows below it. Sort the data first if you want to keep, say, the most recent entry.

### Why aren't two values that look the same treated as duplicates?

Usually a stray space or a number stored as text. Data > Data cleanup > Trim whitespace removes extra spaces, and TRIM does the same in a formula.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
