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:

  1. Highlight the cells that you want to analyse
  2. Select home tab > styles group > conditional formatting > highlight cells rules > more rulesIdentify_unique_values1
  3. Choose the option to highlight only unique or duplicate values
  4. Under the ‘format all’ section select ‘unique’Identify_unique_values2


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