Highlight Duplicate IDs in Google Sheets Without Deleting Rows
Two rows can share an ID while containing different amounts. Before deleting anything, highlight repeated keys and compare the records. The useful rule depends on whether you want to mark every occurrence or only repetitions after the first.
Decide what makes two records duplicates
Open a Google Sheets spreadsheet in a desktop browser and place the synthetic data in A1:C7. The comparison key is record_id in column A. A101 appears twice with different amounts; this is a repeated key, not two identical full records.
Highlighting makes the situation visible without declaring which row should survive. If an ID can legitimately identify several transactions, repeated IDs may be expected. Write that business rule before choosing a removal workflow.
Apply the rule to whole rows
Select A2:C7, choose Format > Conditional formatting, and choose Custom formula is. Enter the formula below, choose a visible fill color, and save the rule. The application range begins at row 2, matching $A2 in the formula.
The dollar sign before A keeps every cell in the formatted row checking its ID column. The relative row number lets the check move from row 2 through row 7. The comparison range is fixed in both dimensions so it does not slide as each row is evaluated.
=AND($A2<>"",COUNTIF($A$2:$A$7,$A2)>1)
Verify blanks and the exact highlighted rows
The all-occurrences rule marks rows 2, 3, 4 and 7. Row 5 has no ID and should stay unmarked by this rule. Missing identifiers deserve a separate check; ignoring them for duplicate detection does not establish that the records are valid.
Use a copy to test an ID containing an ordinary trailing space. Decide whether whitespace is meaningful before normalizing the data. COUNTIF comparisons are case-insensitive, so A101 and a101 are not separate identifiers under this method.
Mark only occurrences after the first
Remove or disable the first rule in the test copy before trying the second formula. Otherwise its formatting can obscure which rule produced the color. Apply the new rule to the same A2:C7 range; only rows 4 and 7 should now be marked.
The beginning $A$2 stays fixed while the endpoint $A2 grows as the rule moves down. This makes the result depend on worksheet order. Sorting the complete records can change which occurrence is first; that does not identify the latest or most reliable version.
=AND($A2<>"",COUNTIF($A$2:$A2,$A2)>1)Keep the key contract and range explicit
The example IDs contain only letters and digits. COUNTIF treats * and ? as wildcards in criteria, so arbitrary identifier strings containing those characters need escaped criteria or another comparison method. Do not apply the simple rule above unchanged to such keys.
When extending the table beyond row 7, extend both the formatting application range and the fixed comparison endpoint in all-occurrences mode. Test a duplicate added on the new final row. A colored sample does not prove future rows are covered.
Review before making destructive changes
Compare the two A101 amounts and determine whether these are two valid transactions or versions of one record. The two A102 rows happen to be identical in this fixture, but retain an original copy before removing either. Conditional formatting itself does not change row counts or amounts.
Independent checks of this synthetic data confirm the expected row numbers and the unchanged total of 78. They do not execute the Google Sheets formatting interface. Compare the colors in your sheet with expected-highlighted-rows.csv, switch off the first rule before trying the later-occurrences rule, and confirm the blank-ID row stays unmarked.
Common questions
Why did only one cell get highlighted?
Check the application range covers A2:C7 and the formula locks the ID column with $A2.
Why do blank rows light up?
Include $A2<>"" to exclude blank identifiers from this duplicate rule.
Should I delete highlighted rows?
Review the key's meaning first. Repeated IDs can represent distinct transactions.
Sources & method
Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.