How to Highlight Duplicates in Google Sheets With COUNTIF

Duplicate rows are one of those small spreadsheet problems that quietly cause big ones. The same customer emailed twice, a product counted twice in stock, or a sign-up list that looks longer than it should. Before you delete anything, it's far safer to see the repeats first.

Google Sheets doesn't have a single highlight duplicates button. Instead you use conditional formatting with a short COUNTIF formula, which colours every value that appears more than once. It sounds technical, but it takes about a minute once you've done it.

Highlighting duplicates in one Google Sheets column

This follows the example in Google's Docs Editors Help, set up for a list in column A:

1. Open your spreadsheet on a computer.
2. Select the cells you want to check, for example A1 to A100, or click the A at the top to select the whole column.
3. Click Format, then Conditional formatting. A panel opens on the right.
4. Under Format cells if, open the dropdown and choose Custom formula is.
5. In the Value or formula box, type =COUNTIF(A:A,A1)>1
6. Under Formatting style, pick a fill colour that stands out.
7. Click Done.

Every value that appears more than once now lights up, including the first copy. That's deliberate: you see both halves of each pair and can decide which one to keep.

What the Google Sheets duplicate formula is doing

COUNTIF counts how many times a value appears in a range. The formula asks: how many times does the value in this row appear in column A? If the answer is more than 1, the rule is true and the cell is coloured.

Google's own example writes the range as A1 to A100 and locks it with absolute references, so the range doesn't slide down as the rule moves from row to row. Using the whole column, A:A, gets you the same result for a single column without needing to lock anything, and it keeps working as you add new rows.

Part of the formula	Meaning
COUNTIF(	Count matching values
A:A	Look through all of column A
A1	The value in the row being checked
)>1	Colour it if it appears more than once

Write the formula for the first row of your selection. If your list starts in A2 because row 1 is a header, select from A2 and use A2 in the formula instead of A1.

Highlighting an entire duplicate row in Google Sheets

Sometimes you want the whole row coloured so the duplicate jumps out across all your columns. Google's help shows a second example for formatting an entire row based on one cell, and you can combine that idea with COUNTIF:

1. Select the full range, for example columns A to E.
2. Open Format, then Conditional formatting, and choose Custom formula is.
3. Write the rule so it always checks column A for each row. Google's help does this by locking the column letter with an absolute reference.
4. Pick your colour and click Done.

If that feels fiddly, stick with highlighting the single column. It does the job for most lists.

Highlighting duplicates versus removing duplicates in Google Sheets

These are different tools, and it's worth knowing which you need:

• Highlighting with conditional formatting changes nothing in your data. It only adds colour.
• Remove duplicates, under Data, then Data cleanup, deletes the repeats for you. You choose which columns count and whether your data has headers.

Google's help says Remove duplicates treats cells with identical values but different letter cases, formatting or formulas as duplicates. I'd always highlight first, scan the results, then remove once you're sure.

Why some duplicates in Google Sheets don't get highlighted

The usual culprit is invisible spaces. Jane Smith with a space on the end isn't the same value as Jane Smith without one. Google's fix is Data, then Data cleanup, then Trim whitespace, which removes leading, trailing and extra spaces. Google notes it won't trim non-breaking spaces, which sometimes come in from copied web pages.

• Nothing highlights: check you chose Custom formula is, not Text contains.
• Wrong cells highlight: the cell in your formula must match the first row you selected.
• Formula points at another tab: Google says conditional formatting can only reference the same sheet unless you use INDIRECT.
• Two rules clash: Google says the first rule that's true decides the format, so drag your duplicate rule to the top.

Using duplicate highlighting for small business lists

Shared sheets are where duplicates breed. Two people paste in the same new leads, or a form feeds rows in while someone else types them by hand. Leaving the rule switched on means repeats show up the moment they land, rather than when a client replies to the same email twice. A bright fill colour that nobody uses for anything else makes it obvious at a glance.

I use this on client lists, event sign-ups and order sheets before any email goes out. It saves the embarrassment of contacting someone twice. Pair it with a tick box column, and my guide on adding checkboxes in Google Sheets shows how to mark each row as checked once you've dealt with it.

If you then need totals from the cleaned list, the walkthrough on the SUM function in Google Sheets picks up from there.

Turning off duplicate highlighting in Google Sheets

To get rid of the colours, open Format, then Conditional formatting, point to your rule and click Remove. One thing to watch: Google says that if you copy and paste from cells that have formatting rules, the rules come along with the pasted data. So if colours turn up somewhere unexpected, check the rules panel for that range.

Frequently asked questions

Is there a highlight duplicates button in Google Sheets?

No. You use Format, then Conditional formatting, choose Custom formula is, and enter a COUNTIF formula such as =COUNTIF(A:A,A1)>1.

Does it highlight the first copy as well?

Yes. The formula colours every value that appears more than once, so you see each pair before deciding which one to keep.

How do I delete duplicates instead?

Select the range and click Data, then Data cleanup, then Remove duplicates. Choose the columns and whether your data has headers, then confirm.

Why are obvious duplicates not highlighted?

Extra spaces are the usual cause. Use Data, then Data cleanup, then Trim whitespace, and check that your formula starts on the first selected row.

Can I check for duplicates on another tab?

Google says conditional formatting formulas can only reference the same sheet, unless you use the INDIRECT function.