Manually checking duplicates one by one isn't feasible. Besides "Remove Duplicates", Excel offers several ways to do this, including Filter, Pivot Table, and even formulas. Here are 4 methods, each with pros and cons — let's take a look!
- Select the data, then Data → Data Tools → Remove Duplicates.
- Pros: simplest and most direct
- Cons: data does not update automatically, and Excel 2003 or earlier doesn't have this feature
- Select the data, then Data → Sort & Filter → Advanced Filter.
- Use the following settings and remember to check "Unique records only".
- Pros: simple and easy to use
- Cons: does not update as data changes
- Select the data, then Insert → Table → PivotTable.
- Open with the following settings
- Then place the data label into the Columns field.
- Pros: Fast, and can be used for later analysis.
- Cons: The PivotTable's placement may affect access to the original data.
- Enter the formula below and press Ctrl + Shift + Enter.
=INDEX(A:A,SMALL(IF(MATCH(A$1:A$14,A:A,)=ROW($1:$14),ROW($1:$14),99),ROW(A2)))&""
- Pros: Updates automatically when the data changes.
- Cons: The formula is complex and hard to understand.
