Ways to mark duplicate data in Excel

In Excel, we usually do a lot of data is dense, to find their own repeated data, will certainly be confused, what is the better way to find the data table in the Excel duplicate data? Yes, let's see.

Marks the repeating data for the worksheet in the following illustration. As shown in the figure:

Data tables

1. First open the EXCEL2007 worksheet, select the range of cells that may have duplicate data or records with the mouse box. On the Start tab, click Conditional Formatting to open the menu, and on the highlight Cells Rule submenu, select duplicate values to open.

Select "Duplicate Value"

2, in the left Drop-down list, select "Repeat", 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 formally.

Select "Repeat"

3, duplicate data is marked down, the light red area is duplicate data. As shown in the figure:

The light red area is the duplicate data

Second, we can use the function to mark a single row of duplicate data, using the COUNTIF function to achieve, also click "Conditional format-" new rule "-" use the formula to determine the cell to be formatted, enter the following formula, and then click the "Format" button, set the color to red.

=countif (A:A,A1) "1.

For example, I need to mark the repeat area is a1:a5000, it is recommended to manually select the area, and then change the formula to this:

=countif (A$4:A$5000,A4) "1.

The above is in Excel data table marked duplicate data method, we can try when necessary, really can alleviate a lot of trouble, as long as the mark out, the data becomes clear, so it will not be confused.