Conditional formatting is a very useful tool in Microsoft Excel that can be used for a variety of purposes when analysing data. It’s particularly useful for identifying trends in a particular record field or for highlighting inconsistent data.
Sometimes it might be useful to identify unique values in a list of data and here’s how to do it using conditional formatting.
Take a look at this data, which identifies students and dates that they submitted their assignments. Each student needs to submit two assignments and you need to identify those that have only submitted one. Here’s how to do it in Excel 2007/2010:
- Highlight the cells that you want to analyse
- Select home tab > styles group > conditional formatting > highlight cells rules > more rules

- Choose the option to highlight only unique or duplicate values
- Under the ‘format all’ section select ‘unique’

- Click ‘format’ to set the formatting for the data that matches your criteria
- Here you can choose from a wide choice of options, change cell colour, font, alignment and more. In our example we have chosen to change the cell colour

- Click ‘ok’ and then ‘ok’ again
You’ll see your unique values are highlighted so that you can clearly identify them. From here you can use the formatting to manipulate your data and use it, such as filtering it on colour or format or using it in a formula.
Thanks Steph – I always learn so many great Excel tips from your articles and Jo’s mentoring!
Glad to be of service Sharon! Don’t forget to let us know if there are things you think would be useful to include in future articles too.
I helped a client with this very thing earlier today – got his entries down from 2500 to just over 500.
that’s quite a clear up Jessie! Imagine how long it would have taken to do it all by hand…!