How to Delete Duplicate Cells in Excelby James Highland
In a large set of data, duplicate entries are inevitable. These may be caused by data-input errors or legitimate recurrences of common data items. While sometimes it is necessary to have these duplicates, other situations require their removal. Microsoft Excel includes a built-in filter function for handling this common task. It is always wise to back up an Excel spreadsheet prior to any significant manipulation of its data. Removing duplicates may at first seem to satisfy a need, but the full original data is often required for other studies. Make a separate copy of any Excel spreadsheet before removing data for any purpose.
Open the Excel spreadsheet that contains the duplicate cells you wish to remove.
Click on the "Edit" menu. Select the "Office Clipboard" command. A pop-up pane will appear on the right side of the Excel window. It will be empty.
Select the entire range of cells, including the column header.
Click on the "Data" menu and select the "Filter" sub-menu. Choose the "Advanced Filter" option. A pop-up window will appear.
Select the "Unique records only" check box at the bottom of the "Advanced Filter" pop-up window.
Press the "OK" button. The duplicate cells will disappear from the spreadsheet view, but are still present in the unfiltered data set.
Press the "Copy" button on the tool bar. Alternately, choose the "Copy" command from the "Edit" menu. The filtered data without duplicates is copied to the "Clipboard" pane on the right side of the program window.
Click the "Data" menu and select the "Filter" sub-menu. Choose the "Show All" command. The full unfiltered data returns.
Press the "Delete" button.
Click the copied data set on the "Clipboard" pane. The data containing duplicate cells is replaced by data without the duplicates.
- check This process is identical in all versions of Excel. However the placement of menu commands is slightly different in Excel 2007. The "Advanced filter" is located in the "Sort and Filter" group of the "Data" tab at the top of the Excel window. These commands are referred to as the "ribbon" in Excel 2007, not a "tool bar."
Items you will need
- photo_camera Jupiterimages/Pixland/Getty Images
Click here to provide feedback on this article