How to Highlight Duplicates in Google Sheets (and Remove Them Safely)

You can highlight duplicates in Google Sheets with one conditional formatting rule built on COUNTIF, then delete the extras with the built-in Remove duplicates tool. This guide covers both on a computer and on Android. It also explains why the rule sometimes seems to do nothing, and which Remove duplicates behavior can delete more than you expect.

If you're on an iPhone or iPad, read this first. Google's iPhone and iPad help page says you can see conditional formatting rules there but can't edit them. To create the rule, use a computer or an Android device.

Highlight duplicates on a computer

These steps follow Google's desktop conditional formatting guide, using its own duplicates example for a list in A1 to A100.

1. Select the range, for example A1 to A100.
2. Click Format > Conditional formatting. A toolbar opens on the right.
3. In the Format cells if drop-down, click Custom formula is. If a rule already exists, click it, or click Add new rule > Custom formula is.
4. Click Value or formula and enter:

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

5. Under Formatting style, choose how matching cells should look.
6. Click Done.

Any cell whose value appears more than once in A1:A100 gets the format.

Why the dollar signs matter

Google's example writes the rule for the first row of the selection. The dollar signs control what stays fixed. Per Google's guide, a $ in front of the column letter and row number keeps a reference absolute, while a reference without them is relative and shifts from row to row. So $A$1:$A$100 stays the same for every cell, and A1 moves to A2 on the second row, A3 on the third, and so on.

If your data lives somewhere other than A1:A100, change both parts to match your selection: the fixed range and the first cell. Leaving out the dollar signs on the range is the usual reason a copied formula highlights the wrong cells, because the range slides down with each row.

Highlight duplicates on an Android phone or tablet

The Android version of Google's guide uses the same formula, but the buttons have different names.

1. Open the spreadsheet in the Google Sheets app and select the range.
2. Tap Format > Conditional formatting. A panel opens.
3. If a rule already exists, tap Add first.
4. In the Format cells if drop-down, tap Custom formula (not "Custom formula is"), or tap Add > Custom formula.
5. Tap Value or formula, enter =COUNTIF($A$1:$A$100,A1)>1, and choose a style. For a custom style, tap Add (+) under custom.
6. Tap Done.

To see what rules a tab already has, tap Format > Conditional formatting and choose Selected range (rules that overlap your selection) or All (every rule on the tab). Tap Done to go back.

When the duplicates rule seems to do nothing

The most common cause is another rule sitting above it. Google's desktop help page says rules are checked in the order they're listed, and the first one that's true decides the format. If an older rule already colors those cells, your duplicates rule never gets a say.

The fix:

• Computer: open Format > Conditional formatting and drag the duplicates rule to the top of the list. To delete a rule you don't need, hover over it and click Remove.
• Android: per Google's Android page, tap Move and drag the rule, or tap Remove > Done to delete one.

Two other things to know. Copying and pasting cells that have rules also pastes the rules, so a pasted block can bring stray formatting with it. And a rule can't point at another tab directly.

Checking against a list on another tab

Google's desktop guide says rule formulas can only refer to the same tab, and that to refer to another tab you use the INDIRECT function. Per Google's INDIRECT reference, the syntax is:

INDIRECT(cell_reference_as_string, [is_A1_notation])

The reference goes in as text, such as INDIRECT("Sheet2!"&B10) from Google's examples. The second argument is optional and defaults to TRUE (A1-style references). Google doesn't publish a ready-made cross-tab duplicates rule, so build yours on a copy of the sheet and confirm it colors the right cells before using it on live data.

Highlighting whole rows instead of one cell

Google's row example selects columns A:E and uses =$B1="Yes" to turn the whole row green when column B says "Yes". The $ before B is what makes it work: every cell in the row looks at column B, while the row number still shifts. The same idea applies if you want a duplicate in one column to color its entire row, but test any adapted formula on a copy first, since Google doesn't give a duplicate-row example.

Remove duplicates without losing rows you wanted

Once you've reviewed the highlights, Google's Data cleanup tool can delete the repeats:

1. Select the data range.
2. Click Data > Data cleanup > Remove duplicates.
3. Choose which columns to include and whether the data has headers.
4. Click Remove duplicates.

Before you click, know how it decides what counts as a duplicate. Google's page says: "Cells with identical values but different letter cases, formatting, or formulas are considered to be duplicates." So "Apple" and "apple" count as the same, and one of them will go. If case matters in your data, such as product codes, don't rely on this tool.

Also, selecting a single cell inside a range removes duplicates across the whole range, not just that cell. Select exactly the columns you mean, and keep a copy of the tab so you have something to compare against.

Google's instructions above are for the desktop version. Do the removal on a computer, where the steps are documented.