When working with data in Excel, duplicate values can cause confusion in analysis and reporting. Today, we’ll introduce easy ways to remove duplicates in Excel that anyone can follow.
1. Remove Duplicates Using the Built-In Feature
Excel includes a built-in Remove Duplicates feature.
1-1. How to Use It
- Select the cell range from which you want to remove duplicate values.
- Click [Data] → [Remove Duplicates].
- Select the columns to use as the duplicate criteria, then click OK.
2. Remove Duplicates Using Advanced Filter
If you want to extract only the data with duplicates removed, rather than simply deleting duplicates, use Advanced Filter.
2-1. How to Apply Advanced Filter
- Click [Data] → [Advanced].
- Specify the source range and destination.
- Select the “Unique records only” option, then click OK.
3. Check for Duplicates with Formulas
You can use the COUNTIF or UNIQUE function to identify duplicates or extract only unique values.
3-1. COUNTIF Example
=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")3-2. UNIQUE Function Example (Excel 365 or Later)
=UNIQUE(A2:A100)4. Final Tip: Back Up Your Data Before Removing Duplicates
Removing duplicates can be difficult to undo, so be sure to make a copy of your original data before you begin.
5. Frequently Asked Questions (FAQ)
- Q1. Can I keep the data linked to the original after removing duplicates?
A: Yes, if you use Power Query instead of Advanced Filter. - Q2. Can I remove duplicates based on multiple columns?
A: Yes. When removing duplicates, select multiple columns to remove duplicates based on combined criteria. - Q3. Can deleted values be recovered?
A: Be sure to back up your data before deleting. It cannot be undone. - Q4. Are cells hidden by a filter also included when removing duplicates?
A: The standard Remove Duplicates feature includes hidden cells. Keep this in mind. - Q5. Does the UNIQUE function work in Excel 2010?
A: No. The UNIQUE function is supported only in Excel 365 or Excel 2019 and later.