Highlight Duplicates in Google Sheets
Find repeated values, highlight entire duplicate rows, and review the result before deleting anything. Use a COUNTIF rule manually or ask SheetsX to apply it to the exact range.
Reviewed by the SheetsX team
=COUNTIF($A$2:$A$100,$A2)>1The fastest way to highlight duplicates in Google Sheets
Select the rows you want to format, open Format → Conditional formatting, choose Custom formula is, and use =COUNTIF($A$2:$A$100,$A2)>1. Apply the rule to the whole row range, such as A2:C100, to highlight every duplicate row based on column A. This changes formatting only; it does not delete or rewrite values.
Watch SheetsX find and highlight duplicate rows
The Agent checks Order IDs in A2:A9, adds a COUNTIF conditional formatting rule to A2:C9, highlights four repeated rows, and confirms that no rows or values were changed.
“On Sheet3, find duplicate Order IDs in A2:A9. Highlight all rows A:C where the Order ID appears more than once with a light red fill. Do not delete rows or change any values. Keep the header and unique rows unchanged.”
How to find duplicates in Google Sheets manually
Start by deciding what makes a row a duplicate. An order ID, email address, or SKU usually works better than comparing every column.
Google's conditional formatting instructions- 1
Choose the key column
Use the field that identifies a record, such as Order ID in column A. Decide whether the first occurrence should also be highlighted.
- 2
Select the rows to format
Select the full output range, such as A2:C100, so the entire duplicate row receives the fill color.
- 3
Add the COUNTIF formula
Open conditional formatting, choose Custom formula is, and enter =COUNTIF($A$2:$A$100,$A2)>1.
- 4
Review before deleting
Filter or inspect the highlighted rows. Only then use Remove duplicates if permanent deletion is actually required.
COUNTIF rules for common duplicate checks
Write the formula as if it applies to the first row of the selected range. Lock the lookup range with dollar signs, but keep the current-row reference relative.
| Goal | Apply to range | Custom formula | Result |
|---|---|---|---|
| Highlight every duplicate occurrence | A2:C100 | =COUNTIF($A$2:$A$100,$A2)>1 | Highlights every row whose column A value appears more than once. |
| Highlight only the second and later copies | A2:C100 | =COUNTIF($A$2:$A2,$A2)>1 | Keeps the first occurrence unformatted and flags later repeats. |
| Ignore blank keys | A2:C100 | =AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1) | Prevents empty rows from being highlighted as duplicates. |
| Match duplicates across two columns | A2:C100 | =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1 | Flags a row only when both the column A and column B values repeat together. |
Highlight, find, remove, or return unique rows?
These methods answer different questions. Use highlighting or a helper column for review; use Remove duplicates only when deleting rows is the intended outcome.
| Method | What it does | Best use |
|---|---|---|
| Conditional formatting | Highlights duplicates without changing data | You want a reversible visual check that updates as values change. |
| Data cleanup → Remove duplicates | Deletes repeated rows from the selected range | You have reviewed the key columns and are ready to remove copies. |
| COUNTIF helper column | Labels or counts duplicate values | You need a filterable audit column before deciding what to remove. |
| UNIQUE | Returns a separate de-duplicated list | You want to preserve the source range and build a clean output elsewhere. |
Google's Remove duplicates command deletes rows from the selected range. Confirm which columns define a duplicate and whether the range contains a header before running it.
Tell the Agent exactly what counts as a duplicate
Name the sheet, key column, row range, formatting action, and whether deletion is allowed. The Agent can then state the rule and affected rows for review.
Install SheetsX- Open a new Agent chat for a clean task context.
- Name the sheet and exact data range.
- Specify the column or columns that define a duplicate.
- Say whether to highlight every occurrence or only later copies.
- Explicitly forbid deletion when you only want a visual audit.
Google Sheets duplicate questions
How do I highlight duplicates in Google Sheets?+
Select the range, open Format → Conditional formatting, choose Custom formula is, and enter a COUNTIF rule such as =COUNTIF($A$2:$A$100,$A2)>1. Choose a fill color and click Done.
Does Google Sheets have a built-in duplicate highlight option?+
Google Sheets supports duplicate highlighting through conditional formatting rather than a one-click duplicate-value preset. Google’s official example uses COUNTIF in a custom formula.
How do I highlight an entire duplicate row based on one column?+
Apply the rule to the full row range, such as A2:C100, but lock the key column in the formula: =COUNTIF($A$2:$A$100,$A2)>1. The dollar signs keep every row tied to column A.
How do I highlight duplicates but keep the first occurrence unmarked?+
Use an expanding range: =COUNTIF($A$2:$A2,$A2)>1. The first instance returns 1, while each later instance returns a count greater than 1.
How do I remove duplicates after highlighting them?+
Review the highlighted rows, select the full data range, then use Data → Data cleanup → Remove duplicates. Confirm the header setting and select only the columns that define a duplicate before removing rows.
Can SheetsX find duplicates without deleting anything?+
Yes. Ask the Agent to highlight or label duplicate rows and explicitly say not to delete rows or change values. The demo on this page applies a conditional formatting rule and leaves every value unchanged.