Duplicate rows sneak in when data gets copied between files, imported twice, or typed in by more than one person. Before you can fix them, you have to find them. Here are three ways, from quickest to most useful long-term.
1. Highlight them (quick look)
Select the column you want to check, then go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every repeated value turns a color.
Good for a one-time check. The downside: it only looks at one column at a time, and the color disappears from view once you start sorting and filtering.
2. Flag them with a formula (best for trackers)
Add a column called “Duplicate?” and put this formula in the first row, then fill it down:
=IF(COUNTIF($A:$A, A2)>1, "YES", "")
It counts how many times the value in A2 appears in column A and marks it YES if it shows up more than once. Because it’s a real column, you can filter it, count it, or feed it into a summary. This is how the duplicate check works in my sample order tracker.
3. Remove them (careful)
Data → Remove Duplicates deletes repeated rows for you. It’s fast, but it deletes data permanently, and it keeps whichever copy comes first, which isn’t always the right one.
Before you use it, save a copy of the file, and check the flagged rows from method 2 to make sure they really are duplicates and not two different orders that happen to look alike.
Bonus: a list without repeats
If you just need a clean list of each value once, like a customer list for a dropdown, =UNIQUE(A2:A500) builds it for you and updates as the data changes. It works in Microsoft 365, Excel 2021 and newer, and Google Sheets.