Excel Tips and Tricks / Business Reporting Question: Is there an automated way of deleting duplicate data rows from a worksheet?

Answer: Yes, by using remove duplicates data tool

Why: To remove duplicate values from a worksheet

Applies To: MS Excel 2007 & 2010

1. With reference to the data given below staff codes, P1, P2 and P7 have duplicate values.

2. Highlight the data table. You may include/exclude the heading row.

3. Select the Data tab and Select Remove Duplicates under the Data Tools group. Refer to the image below.

4. The screen shot below will be displayed.

5. In order to delete duplicate values at least one column that contain duplicates must be selected.

6. Select OK, and refer to the dialogue box below. Thereafter select OK again.

The duplicate data rows have been deleted from the worksheet. The user will therefore not locate and delete the duplicate values manually, and this will result in time saving.