Excel 2013 All-in-One For Dummies
Book image
Explore Book Buy On Amazon

You can use Excel’s Eliminate Duplicates feature to quickly find and remove duplicate records from a list (or rows from a table). This is a great feature especially when you’re dealing with a really large data list in which several different people do the data entry and which should not have any duplicate records (such as client lists, personnel files, and the like).

To have Excel remove all duplicate records from a data list or table, you follow these simple steps:

Position the cell cursor in one of the cells of the data list or table.

Position the cell cursor in one of the cells of the data list or table.

You use the Remove Duplicates dialog box to remove duplicate records from a data list.

Click the Remove Duplicates command button on the Ribbon’s Data tab or press Alt+AM.

Click the Remove Duplicates command button on the Ribbon’s Data tab or press Alt+AM.

Excel selects all the cells in the data list while at the same time displaying the Remove Duplicates dialog box.

When this dialog box first opens, Excel automatically selects all the fields in the list (by placing check marks in the check boxes in front of their names in the Columns list box). When all the fields are selected and you click OK in this dialog box, Excel deletes only complete duplicates (in other words, copies) of the records in the list.

If you want the program to remove records where there’s any duplication of entries in particular fields (such as the ID No field), you remove the check marks from all the columns except for those whose duplication are sufficient reason for deleting the entire record (as described in Step 3). Otherwise, you proceed directly to Step 4.

(Optional) Remove the check marks from all fields in the Columns list box except for those whose duplicates are reason for deleting the record.

(Optional) Remove the check marks from all fields in the Columns list box except for those whose duplicates are reason for deleting the record.

If only one or two fields out of many need to be selected in the Columns list box, click the Unselect All button to remove the check marks from all field check boxes and then individually click the fields that can’t have duplicate entries.

Click OK to have Excel close the Remove Duplicates dialog box.

Click OK to have Excel close the Remove Duplicates dialog box.

The duplicate records (rows) are removed from the selected data list.

About This Article

This article is from the book:

About the book author:

Greg Harvey, PhD, is President of Mind Over Media and a highly skilled instructor. He has been writing computer books for more than 20 years, and his long list of bestsellers includes all editions of Excel For Dummies, Excel All-in-One For Dummies, and Excel Workbook For Dummies.

This article can be found in the category: