In this tutorial, I will guide you on How to Remove Duplicates in Excel. Manually removing duplicates in Excel can be time-consuming and frustrating; when you are working with large data sets, it is not possible to remove duplicate entries one by one.
The reason we use the Remove Duplicates Excel command is because we remove duplicate items, records, and entries from an Excel sheet and keep only specific values. We only need to keep one specific value instead of multiple duplicate values. The rest of the duplicate values will have to be removed.
How to remove duplicates in Excel is a common topic that recruiters ask about during job interviews that require Excel knowledge.
Incorrect data always messes up your spreadsheet, and that is one of the most common reasons why you should learn how to remove duplicates in Excel.
Excel’s Remove Duplicates function makes it easy to remove duplicate data entries.
In this article, we will learn how to Remove Duplicates in Excel in different ways using various tools and functions.
1. How to Remove Duplicates in Excel based on one column using the Remove Duplicates Command.
We have a sheet with a single column of employee names that contains some duplicate entries. If we want to remove duplicate values with the help of the remove duplicates command, then we have to follow some steps.
We will follow 5 easy steps to remove duplicate entries.
1. Select the range where you want to Remove Duplicate Entries.
2. Click on the Data Tab.
3. Then, click the Remove Duplicates Command.
4. Checkmark the employee name columns.
5. Then click OK.
Result.
8. Duplicate values found and removed.
10. Unique values remain.
How to Highlight Duplicates in Excel from Conditional Formatting.
- Select the range where you want to Highlight Duplicates Entries (B4:E13).
- Click on the Home Tab.3. Then, click the Conditional Formatting Command.
- After that, Choose Highlight Cell Rules.
- Select Duplicate Values.
- Choose your desired formatting style and click OK.
You can easily Find Duplicates in Excel using Conditional Formatting and see only the duplicates value in the entire sheet using conditional formatting highlight rules.
2. How to Remove Duplicates in Excel from Multiple Columns using the Remove Duplicates Command.
Suppose we have the below Excel sheet, which contains Employee ID, Employee Name, Department, and Salary details. It has some duplicate rows; we will remove the duplicate rows using the Remove Duplicates command.
1. Select the range where you want to Remove Duplicate Entries (B4:E13).
2. Click on the Data Tab.
3. Then, click the Remove Duplicates Command.
4. In the dialog box, Checkmark all relevant columns (ID, Name, Department, Salary).
5. Then click OK.
Result.
2 Duplicate values found and removed
8 Unique values remain.
3. How to remove duplicates in Excel from Unique Function.
Using the =UNIQUE Function, you can extract a list of unique values without altering your original data.
- Type the Formula =UNIQUE
- Open the bracket (
- Select the range (B4:C13) from which you want to extract unique values.
- Close the bracket.
- Then Press the Enter Key.
Note:- The UNIQUE Function is only available in Microsoft 365, Excel 2021, and later versions. This function is not available in Excel 2019, 2016, and earlier versions.
Also Read: How to Use COUNTIFS Function in Excel with multiple criteria.
Summary:-
We should know that Excel provides us with many tools, each of which is designed for a different purpose. Such as removing duplicates to clean up data, conditional formatting to visualize duplicate values, or using a unique formula to extract a different list while preserving your original table.
Each tool helps you keep your spreadsheet accurate, clean, and ready for analysis. Mastering them won’t take you long, but it can save you hours of hassle later.