Here’s how you can find and avoid duplicates in your data using Conditional Formatting:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a list of names in cells H2:H20:
I want to make sure I have no names that are the same, and as I add more names I want to make sure I have no duplicates. After I highlight my range, from the Conditional Formatting option on the Home tab, I will select New Rule, then “Format only unique or duplicate values”:
I now have the option to format those that are unique or are duplicate. I will choose duplicate:
Then clicking on the Format button, I will choose to format the duplicates in yellow.
I’ve now determined that my list has no duplicates, but if I change the name “fred” to “ed”, it will show up as already having that name in the list:
Here’s an interesting bonus! As I add more names to my list, even though I had originally defined the range as H2:H20, Excel will continue that formatting as long as I don’t have any breaks in the range:
One word of caution: I tried the same process in a workbook with dozens of worksheets and a multitude of conditional formatting, and Excel did not continue the formatting beyond the original defined list. So, if you are going to continue to add to your list, you may only want to use a process like this on a single worksheet.