Our last tutorial taught you how to use conditional formatting on Microsoft Excel 2007 & 2010 to colour code data. This week we look at another use for conditional formatting – highlighting duplicate and unique values within a spreadsheet.
We’ve created a basic table of data of a pretend survey in which children were asked their shoe size. This tutorial shows you how to use conditional formatting to eliminate multiple entries and also to find unique values. If the data is entered manually these tips are great for making sure there are no mistakes with the data.
1. Eliminate accidental entries
For the purposes of the tutorial the data is limited and so it is easy to see duplicate/unique content by eye. However, if the data was for a whole school year of 300 pupils then conditional formatting becomes highly useful.
Firstly we are going to check to see if there are any multiple entries. Select the data you want checked and click Conditional Formatting > Highlight Cells Rule > Duplicate Values.
A window will appear with the option of Duplicate or Unique Values and options for choosing result colour.
We want to determine duplicate entries so we have selected Format cells that contain Dupliate values with Green Fill with Dark Green Text. The table now looks like this:
As you can see, 9 year old Anne with her size 3 feet appears twice in the table and so one of the entries needs to be deleted. This is highly useful for instances where a large amount of data is input to Excel manually as a way of reducing human error in reports.
2. Finding unique content
The same tool can be used to find anomalies in the data. Again this is a way of fixing human error. If our data was for 300 pupils of a small age range you would expect to find a small range of shoe sizes. You can use unique value formatting to quickly check if any values are unique and so could be wrong.
Again go to Conditional Formatting > Highlight Cells Rule > Duplicate Value but this time choose Unique Values:
As a result the spreadsheet shows up that Alan is the only child with size 6 feet:
It’s a while since I was 9 years old but for the purposes of this tutorial size 6 seems plausible compared to the other results. However, if the unique value was an extreme of 1 or 66 then it is more likely that the data was entered wrong and so the result would get deleted.
These two formatting tools aren’t highly effective for small data sets but are brilliant for finding and removing bad entries from large tables of data to produce accurate results. The steps are necessary to validate data before creating graphs and tables to show the results of surveys.