Earlier in the month, I showed you how to delete duplicates in an Excel sheet. You can use this technique in all versions of Excel. If you’re using Excel 2007 or 2010, there’s a built-in feature that easily removes duplicate data and duplicate records (rows).
Using the simple sheet shown below, let’s remove the duplicate values from the Sold column. As you can see, the value 87 appears more than once, so let’s delete the duplicates as follows:
- Select cells B3:B11.
- Click the Data tab.
- Click Remove Duplicates in the Data Tools group.
- Because you’ve selected a single column in a contiguous data range, Excel will ask you to confirm your selection. In this case, click the Continue With The Current Selection option, and then click Remove Duplicates.
- Excel will display a second dialog that shows the Sold column selected. Click OK without making any further changes.
- Next, Excel displays a short information message that shares the number of duplicates removed. Click OK.
You can see the results below. Excel deleted the three duplicate values and moved everything below the removed values (the value 82) up, accordingly. For this reason, you’ll want to be careful how you use this feature. You’d probably not want to use this method to delete duplicate values from this particular sheet because of the displaced value.
Now, let’s use this feature to delete duplicate rows. There’s only one (rows 4 and 9 are the same). To delete row 9, do the following:
- Select A3..C11.
- Click the Data tab.
- Click Remove Duplicates in the Data Tools group.
- In the resulting dialog, click OK without making any changes.
- Click OK to confirm the change.
This time, Excel deleted only one row-row 9-and moved rows 10 and 11 up one row. When deleting rows, moving records up may be desirable. As long as you know what to expect, and you get what you expect, all is well!
