How to Remove Duplicates in Excel? Three Methods for You

If you have combined different tables or let many people have access to the same document, you might get repeated entries in your spreadsheet. In this case, you need to find a way to delete all the duplicates.

For a small spreadsheet, it is very easy for you to find and remove duplicates. However, for a large dataset with hundreds or even thousands of names, you’d be better off removing duplicate entries in a semi-automated way. Because it can save you times of hunting.

So, how to remove duplicates in Excel? In this post, you will find some helpful methods for you to remove all the duplicates in Excel. And I will use the Index of Place Names in Great Britain (November 2021) sheet to demonstrate all the methods.

How to Make Barcodes Easily in Excel?

This post will recommend two ways to make barcodes easily in Excel. If you need to generate barcodes in Excel, you can just choose one to try.

Read More

Method 1. Use Remove Duplicates Function in Excel

If you just want to remove duplicates in a small spreadsheet, you can search and select them to delete one by one.

But if you want to remove duplicates in a large spreadsheet, you’d better select the Remove Duplicates feature under the Data tab. It can help you delete duplicates efficiently. Here’s the guide that can help you to find duplicates in excel and remove them:

1. Open the sheet in excel that has many duplicates you want to remove.
2. Click the small triangle in the upper-left corner of your sheet to select all the columns and rows.
3. Select the Data tab and click on the “Remove Duplicates” option.
4. In the Remove Duplicates window, click on OK to remove duplicate items from your data set.
5. Once done, you can delete all the duplicates on your sheet.

Method 2. Use the Advanced Filter Option

Do you know how to remove duplicates in Excel? The Advanced Filter option in Excel can also help you filter duplicate values. And it can also help you copy the unique values to a different location. Here’s the way you can use to find duplicates in excel:

1. Open the sheet that you want to delete the duplicates from in Excel.
2. Click on a cell or range in the dataset from which you want to remove duplicates.
3. Go to the Data tab and click the Advanced button next to the Filter button.
4. Then you will see a dialog that consists of a list of options for advanced filtering.
5. Select the Copy to another location option to copy the unique values to a different location.
6. Check the range of your records in the List Range field and make sure it is the range that you have specified.
7. In the Copy to: field, enter the range where the resultant unique values must be copied.
8. After that, check the Unique records only box and click on OK.
9. Once done, the unique values will be copied to the specific range.

How to Highlight a Column in Excel? [Window 10 & 11 Guide]

Do you want to know how to highlight a column in Excel? This post provides some methods to help you achieve it.

Read More

Method 3. Use Formulas to Remove Duplicates

Using formulas to remove duplicates is also a useful method to fix the question—how to remove duplicates in Excel. You can do as follows to complete it:

1. Open the sheet that you want to delete the duplicates from in Excel.
2. You need to combine the columns A, B, and C by using the concatenation operator “&”, such as =A2&B2&C2.
3. Entering the formula in cell D2 and then copying it to all rows
4. Then you need another column named “Count” to find out the duplicates in Column D.
5. Next, you need to use the COUNTIF function on cell E2 to count the number of occurrences of each value in column D. And the formula is “=COUNTIF($D$2:D2,D2)”
6. If the value of count is “1”, then it has only appeared once and is unique.
7. After that, you need to go to Data tab > Sort & Filter section >Filter.
8. Click on the filter at the top of Column E. Select “1” to keep only the unique values and remove the duplicates.
9. Click OK. The duplicate values will be removed from the table. You can copy these resultant unique records and paste them elsewhere.

How to Convert Notepad Data to Excel

This essay shows the ways to convert Notepad to Excel. You can convert Notepad by copying and pasting, converting data in Notepad, online tools and so on.

Read More

Bottom Line

After reading this post, have you ever known how to remove duplicates in Excel? Here are three methods you can choose from to remove duplicates in your Excel.

If you are interested in MiniTool Partition Wizard and want to know more about it, you can visit MiniTool Partition Wizard’s official website by clicking the hyperlink. MiniTool Partition Wizard is an all-in-one partition manager and can be used for data recovery and disk diagnosis.