Formulas to find duplicate data methods in Excel

I use Excel to manage the student's achievement, I want to have the same name student's result data to highlight (changes the font and the cell fill color), I use "the conditional format" to realize.

1. Start Excel 2003 (similar to the basic version) and open the student score sheet.

2. Select the range of cells in which the data is located (such as B2 to J2001), and perform the format-conditional Format command to open the Conditional Formatting dialog box.

3. Click the Drop-down button to the right of the leftmost box, select the Formula option in the Drop-down list that pops up, and then enter the formula in the following box: =countif ($B $: $B $2001, $B 2) >1 (This assumes the name is stored in B2 to B2001 area).

4. Then click the Format button, pop-up Cell Format dialog box, under the Font tab, press the Drop-down button to the right of the color, expand the palette, select Red, and then switch to the Pattern tab to select a color.

5. After the setting is completed, the data rows of the students with the same name are highlighted twice by pressing the OK button.

If you are not accustomed to doing so, you can also set the format as above in cell B2, using =countif (B$2:B$865,B2) >1, and then brush the following cells in a format.

Summary: Select b2:b865, format, conditional format, formula, =countif (B$2:B$865,B2) >1, set format.