How to Remove or Highlight Duplicates in Excel and Google Sheets
Excel
- Remove: select the data, then Data > Remove Duplicates, and choose the columns to compare.
- Highlight: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Google Sheets
- Remove: Data > Data cleanup > Remove duplicates.
- Highlight: Format > Conditional formatting > Custom formula:
=COUNTIF($A:$A, $A1)>1
Formula that keeps the original
=UNIQUE(A2:A500)UNIQUE works in Microsoft 365, Excel 2021 and Google Sheets and updates automatically.
Count how many times each value appears
=COUNTIF($A$2:$A$500, A2)Tip: copy your data to a new sheet before removing duplicates, because Remove Duplicates deletes rows permanently once saved.