Excel Guide to Removing Duplicates from a Spreadsheet

When you are importing data from another source, or you are working with a large spreadsheet that is continuously updated with new records, you can often find yourself with a worksheet filled with duplicate data. In many cases these duplicates are unnecessary and can even hurt the outcome that you are attempting to generate. Fortunately you can learn how to remove duplicates in Excel 2010 to eliminate this extraneous data from your spreadsheet. Anyone who has ever attempted to manually delete duplicate records in Excel knows what a chore that can be, plus it is prone to a lot of human error. The utility in Excel that allows you to remove duplicates is quick, efficient and easy.


Deleting Duplicates in Excel 2010


Without using the automatic duplicate removal tool in Excel 2010, you may have toyed with the prospect of using the Find & Replace tool to manually sort through your data. While slower, this is an effective method for deleting duplicate data, provided that you know which duplicates exist. A massive spreadsheet could contain many duplicates of the same data, and might even contain duplicates that you are not even aware of. Continue reading to learn how to use Excel’s dedicated tool to remove duplicates from your file.


Step 1: Open your spreadsheet in Excel 2010.

Step 2: Click the Data tab at the top of the window.

excel 2010 data tab


Step 3: Click the Remove Duplicates button in the Data Tools section of the ribbon at the top of the window.

how to remove duplicates in excel 2010


Step 4: Click the box to the left of each column that you want to check for duplicates. For example, if the duplicate data needs to exist in multiple columns, check each column. But if you just need to delete duplicate rows that show up in only one column, then only select that column. For example, in the image below, I could select “Column A” to delete every instance of the name “John,” but I would actually be deleting rows that contained only partial duplicates.

choose columns to check for duplicates


Step 5: Click the OK button to remove the duplicates from the columns that you have selected.

Join Our Free Newsletter

Featured guides and deals

You may opt out at any time.
Read our Privacy Policy

Newest Guides