Microsoft has introduced an option called the Conditional Formatting, to find duplicates in its 2007 version itself. You will need to follow very few (easy) steps to locate and highlight the duplicates in the sheet. I am assuming you have the latest versions of Excel, i.e. 2007 or above. Please follow these steps.
Find and Highlight Duplicates in Excel
1) Select the range of data in which you want to find the duplicates. There are two ways you can select the range. Use the mouse and select the range. Alternatively, click a cell inside the sheet and press Ctrl+a. This key combination will automatically select the data for you. However, if you do not wish to select the entire sheet, they use a mouse.
2) On the top of the Excel file, you will find the Home tab at the left top corner. Inside the Home tab, look for Conditional Formatting and click it. Its a dropdown and has a list of other options. Roll your mouse over the first option Highlight Cells Rules, it will open another list of options and now choose Duplicate Values.
3) When you choose Duplicate Values, Excel will open a small window with options to highlight the duplicate values in the sheet. In there, you will also have options to choose colors of your choice to highlight. Do not forget the click the OK button.
Note: Press Ctrl+z to undo the highlighting of the range.
Remove Conditional Formatting in Excel
However, if you have saved the file with the duplicates highlighted, and later you wish to remove the highlighting from the selected range, then you follow these steps.
1) Select the range you wish to remove the highlighting.
2) Under Conditional Formatting option in the Home tab, you will find Clear Rules option. Please see the below image.
3) Roll the mouse over Clear Rules and choose Clear Rules from Selected Cells. This would clear or remove the highlighted portion in the range.
That’s it folks. I am sure next time if you across this situation of finding duplicates, you will not hesitate or waste time in searching the sheet manually or looking for solutions elsewhere. Now you know how to do it and you can also guide your friends or colleagues in solving the problem.
Thanks for reading.