How to Remove Duplicates in Excel – Use Multiple Methods to Delete Duplicate Values

Having duplicate values in your dataset could be annoying because it can mess up the original results. Apart from that, it would be a tiring task to find and remove each duplicate value manually. Even for a small spreadsheet, you will not feel pleasure doing this annoying task. And when you have a large dataset with more than hundreds or thousands of entries, it would be entirely impossible for a person to sort it out.

With Microsoft Excel, you can perform computational analysis to keep track of inventories and manage other lists. Here the question is how to remove duplicates in Excel without wasting time and effort. These duplicates can make things worse and beyond your imagination. You may end up mailing the same document to the same person more than once.

How to Remove Duplicates in Excel

With Excel, you can easily remove or delete duplicate values in your dataset. Follow the steps given below to delete the duplicate entries.

1. Firstly, click on a cell or range in the dataset from which you want to remove duplicates. Choosing any cell lets Excel determine the range for you in the next step by default.

2. In the next step, click on the “Remove Duplicates” option and then click on the DATA tab to select the Data Tools section from which you will click on the Remove Duplicates option.
3. Choose the columns to compare and check for duplicate entries.

If the data had column headers, you must choose the “My data has headers” option and then hit OK. Once you click on the header option, you will see the first row is not included in removing duplicate values.

4. Now, you will see a dialog box once the duplicate rows are deleted. In the dialog box, you will find details about how many duplicate values are found and deleted. You will also get to know the number of unique values. 
5. All the duplicate entries are now deleted and your dataset is free from duplicate data.

Using Conditional Formatting to Remove Duplicate Values in Excel

Other than the above-mentioned approach, you can use another method to remove duplicate values in Excel. Conditional Formatting is a feature used to filter, sort, and organize data as per the user’s needs. In this method, “Highlight Cell Rules” is the frequently used feature that helps in formatting cell values in terms of color, font, and several other formats.

Well, to be more clear, you must understand this method never remove duplicate values directly. It will only make the duplicate entries clear to identify easily.

1. Choose the cell range you want to apply the conditional formatting feature. Click on the Home tab and choose the Conditional Formatting option. Now, select Highlight Cell Rules and then click on the Duplicate Values option.

2. Choose “Classic” in the “Style” section and then click on the “Format only unique or duplicate values” option. Ensure that the drop-down menu is set to “duplicate”. Now, use the “Format with” option to set the formatting style and click OK.
3. Excel will highlight all the duplicate values and cells. It will help you in removing duplicate rows or values.

Remove Duplicate Values in Excel Using the UNIQUE Function

Using the UNIQUE function helps in returning unique values located in a range. Keep in mind, this procedure will not replace the existing data.

You can find a unique list of values by choosing an empty column from your spreadsheet. Now, enter the UNIQUE function with the help of the cell range that is needed to be scanned for duplicates.

Using Power Query to Remove Duplicates from one or more columns in Excel

With the help of Power Query, Excel lets you remove duplicate values based on one or more columns. You can choose the columns you want to remove duplicates from. Let’s dive into how to remove duplicates in Excel using Power Query.

1. Go to Data and choose Get Data (Power Query) option.

2. Check that the Excel workbook is set as your data source.
3. Scroll down and choose the spreadsheet on which you want to apply the Power Query function. Hit the Next option.
4. Tick the checkbox that contains your data on the worksheet and then choose the “Load” option given in the bottom right side of the corner.
5. Now the dataset is all converted into a table.
6. Press CTRL/Cmd + click on the columns. Doing this will help you in choosing the columns to apply the Power Query.
7. Click on the “Remove Duplicates” option given in the “Data” tab. Now, click on the “OK” button given in the pop-up dialog box.
8. This time also you will find that Excel has made it easier to know the number of duplicates removed and how many unique values are left.

All the above-mentioned methods are highly useful and safe. You can use any of them to remove duplicate values from your spreadsheet. Keep practicing Excel tools and tricks!