This time I will show you one of the most used and easy to apply conditional formatting rules to highlight duplicate values.
Conditional formatting to duplicate values
The first step we must take is to select the range of cells that contains the data to which we will apply the conditional formatting and which in this example will be the range A2: A20.
After having made the selection, we must go to the tab , and within the group , we will click on the button and we will choose option R and later we choose the option .
Selecting this option will display the dialog box and in the first list we make sure to choose the option .
The second drop-down list allows us to choose the style we want to apply to the cells that comply with this rule and for this example I will leave the option selected .
When we click the OK button, we will see that some cells of our data will be highlighted in red color because they have a value that is repeated at least once. Cells that do not have a fill color is because they contain a non-repeating value.
In this way we can easily highlight duplicate values using conditional formatting and this will help us to visually detect those cells that contain duplicate data.
In the second part of our exercise we will modify the newly created conditional formatting rule so that the conditional formatting is applied to the unique values in our list.
Conditional formatting to unique values
If instead of highlighting the duplicate values, we want to apply the condition format to the unique values, then we must follow the same steps described in the previous section, but making a very small change.
- Select the range of cells on which the conditional formatting will be applied.
- On the Home tab, in the Styles group, click the Conditional Formatting button and choose Rules for highlighting cells> Duplicate values.
- In the first drop-down list of the dialog you must choose the option and finally choose the format you want to apply.
To finish you must click on the OK button and as a result we will have a conditional formatting rule that will highlight those values that are not repeated.
This is how we can use Conditional Formatting to highlight unique values or duplicate values.
Duplicate values in multiple columns
If your data is contained in two columns and you want to highlight those that are repeated, then you must use an auxiliary column where you previously make the concatenation of the values that will be evaluated. In the following image you can see that column C is in charge of concatenating the values of both columns.
In this example I have not concatenated a blank space between the First Name and Last Name since it is not necessary to find duplicate values, but you could add it in case you want to show it on the screen.
Our conditional formatting rule will be created on the auxiliary column, so we will open the window again from the button > and we create the new rule.
Pressing the OK button will highlight the duplicate values considering the two columns of data.