How to Find and Highlight Duplicates in Excel using Conditional Formatting

← PrevNext →

Excel is an application used by millions of professionals around the world. Its popularity is due its simplicity. Yet often people get stuck with some very common problems for which the solution is very simple. One the issues is finding duplicates in Excel.

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.

Select a Range to Find Duplicate in Excel

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.

Select Conditional Formatting in Excel

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.

Duplicates Highlighted in Excel Sheet

👉 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.

Remove Conditional Formatting in Excel

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. Now you know how to do it and you can also guide your friends or colleagues in solving the problem.

Thanks for reading.

← PreviousNext →