Conditional formatting to duplicate values ​​in Excel

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.

Apply conditional formatting to duplicate values ​​in Excel

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 .

Duplicate Values ​​command to create Conditional Formatting rule

Selecting this option will display the dialog box and in the first list we make sure to choose the option .

Conditional Formatting Rule for Duplicates in Excel

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.

Highlight duplicate values ​​with Conditional Formatting

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.

  1. Select the range of cells on which the conditional formatting will be applied.
  2. On the Home tab, in the Styles group, click the Conditional Formatting button and choose Rules for highlighting cells> Duplicate values.
  3. In the first drop-down list of the dialog you must choose the option and finally choose the format you want to apply.

Conditional formatting for unique values

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.

Highlight unique values ​​with conditional formatting in Excel

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.

How to highlight duplicate values ​​in more than one column

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.

Conditional formatting duplicate values

Pressing the OK button will highlight the duplicate values ​​considering the two columns of data.

Download workbook

Leave a Comment