Dileep's Blog on Software Testing: How to find duplicate values in Excel sheet

Did you ever face a problem to find duplicates in MS Excel? Certainly I had problems in finding duplicates, in my report, in resolved defect list, release notes and in traceability matrix. At first I was doing it manually and it is very time consuming and boring job, but now I can do in 1-2 minutes, we can use excel functionality to find duplicates, I was not aware about this. I would like to share this information to every person who faced the same situation. When you have to generate various kinds of reports for Testing/QA this will be helpful.

Herez the steps to find duplicates

1. Open the sheet

2. Select the fist cell in the column to find duplicate

3. Click on Format menu and select the option “Conditional Formatting”

4. Select Formula Is option in the dialog box

5. Enter formula as follows =COUNTIF ($B$2:$B2, B2)>1 (Where B is the column selected and 2 is the row number. >1 means if the count of the cell value is greater than 1

6. Then select Format button on the dialog box and select any color of your choice (It will help you to identify the duplicate cell)

7. Click OK on the dialog box

8. Now copy the cell (You can use Edit -> Copy or Ctrl + C)

9. Press Ctrl + Space to select the entire column ( Now we are going to apply this format to all the cells, then it will find all the duplicates, i.e. the format will look for the duplicate with each and every cell value in the selected column)