There are a couple of different ways of identifying duplicates within a dataset but by far the easiest way is with conditional formatting. There are also plenty of other things that you can do with this tool in Excel, but to start with, we’ll look at highlighting duplicate values.

Start off with your dataset, and highlight the cells that you want to evaluate. Our example here is simply a list of numbers.


From the ‘Home’ tab under the ‘Styles’ group select Conditional Formatting –> Highlight Cells Rules –> Duplicate Values


Here you can select from the drop down boxes to customise how your cells are highlighted.


When you have made you selection click ‘Ok’ and you will see that you duplicates are highligted straightaway. That’s all there is to it!


More often than not, you will have a reason for wanting to identify such values. You could then, for example, take this further and sort your data by colour of cell. You now have all of your duplicate records ready to edit, remove or analyse – or simply leave it as it is.

Remember that the conditional formatting will remain in effect in the cells that you have specified until you remove this formatting. This means that if you change the value of a cell so that it is no longer the same as any other cell, the highlighting will be removed automatically.

If you’ve not used conditional formatting before, this is an easy way to get started and explore the features of this tool. Next time, we’ll look a bit further into the use of conditional formatting and the other useful things it can be used for!

Steph Middleton, owner of Outhouse