Last updated: 27th September 2022
One of the fastest and easiest ways to find duplicates in Excel is using Conditional Formatting. There’s another advantage in using conditional formation, is that it highlights duplicates using specific colors.Here are few steps that you call follow.
Find and highlight duplicate values 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, it will open a small window with options to highlight the duplicate values in the sheet. In there, you will also have options to choose colours of your choice to highlight. Do not forget the click the OK button.
Note: Press Ctrl+Z to undo the highlighting of the range.
👉 Do you know you can automate the process of finding duplicates using VBA? 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" options and choose Clear Rules from Selected Cells. This would clear or remove the highlighted portion in the range.
That's it. 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.