Quick Retrieve easy Find Delete duplicate data

Excel 2007 worksheets often have duplicate data or records for reasons such as duplicate input or paste. If your worksheet is large, it's hard to find and delete duplicate data manually. But the problem is "a piece of cake" for Excel 2007来, because several of its new features can easily solve such problems.

1. Identify duplicate data

Open the worksheet and select the area where duplicate data or records may exist. Click Conditional Formatting on the Start tab to open the menu, select duplicate values under the Highlight Cells Rule submenu, and open the dialog box shown in Figure 1. Select Repeat in the left Drop-down list, select the format you want to set in the Set as Drop-down list (for example, green fill dark green text) to see the effect of identifying duplicate values in the selected area, and then click OK to apply the effect.

Figure 1 Setting the Duplicate Values tab

2. Filter duplicate data

If you need to further identify and delete duplicate data, you can filter the data based on the identity results above: Select the area to be filtered that identifies the duplicate data as described above, and click the Filter button on the Data tab to display a Drop-down button for each cell in the first row of the filtered range. Then click a Drop-down button to open the menu, select Sort by font color in the Filter by Color submenu, and then filter the rows that have duplicate data or records, and then you can view and manually delete duplicate data.

3. Automatically delete duplicate data

Excel 2007 provides a feature called "Remove Duplicates". It can quickly delete duplicate data from a worksheet by selecting a range that may have duplicate data or records, clicking the Remove Duplicates button on the Data tab, and opening the dialog box shown in Figure 2. If you need to keep one of several identical records and then remove all remaining identical records, you must select all of the column headings in Figure 2 and click OK, and the dialog box will display the deletion results.

Figure 2 Check for "Delete duplicates"

If there is one column (or columns) in the record with the same data, you want to keep a record and delete the remaining records. Select these columns in the column headings (for example, "address") or column labels (such as "E") in Figure 2, and "OK" to retain a record and remove all excess (see Figure 3).

Figure 3 Extra duplicate records have been deleted