Microsoft has introduced an method called 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. 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.
Note: Press Ctrl+z to undo the highlighting of the range.
👉 Do you know you can automated the whole process of finding duplicates using VBA. Yes, read this article.
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.
Thanks for reading. ☺