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.
Try the supplied extension-test.csv in a separate copy: row 8 adds A103, North, 9. Change Apply to range to A2:C8. For every occurrence, also change the fixed endpoint to $A$8; rows 2, 3, 4, 6, 7 and 8 should now be colored. For later occurrences only, keep the expanding formula unchanged; rows 4, 7 and 8 should be colored. There are seven records totaling 87. Restore the original six-record fixture when comparing against the base expected file.
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.
On October 27, 2026, we edited and saved both rules through the Google Sheets desktop-browser formatting interface, one rule at a time. The actual fills across columns A:C matched rows 2, 3, 4 and 7 in all-occurrences mode and rows 4 and 7 in later-occurrences mode. The blank-ID row stayed unmarked; all six records and the total of 78 were unchanged. The two downloadable browser screenshots show these results; their interface labels are Korean, while the sample data and formulas match this guide.
We also tested the additional A103 record on row 8, extended the application range, and verified both seven-record results described above before restoring the original data and one all-occurrences rule. These checks cover this bounded synthetic example in desktop Google Sheets, not every locale, arbitrary identifier string or mobile interface. Compare your colors with the expected CSV files before using the rule on live records.
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.