Excel tutorials

In the previous tutorial we used conditional formatting to highlight duplicate values in a range – we did not use a formula that time – but below I will show how we can accomplish the same thing with using a formula.

Start by entering some data in column A – like below.

We can see that we have a duplicate by just looking at this. Now, select the cells, A2 to A7 -> On the “HOME” tab go to “Conditional Formatting” – then select “New Rule“

In the field that appears we are going to make use of the COUNTIF formula. In the field, enter the following formula:

=COUNTIF(A:A;A2)>1

This says, look in the range of Column A – count how many cells that are the same as what is in A2.

Then we click on the “Format” to select a color – lets make them red. In this case duplicate values will be in red.