Excel – Highlighting duplicate rows based on 2 columns

conditional formattingmicrosoft excel

I have the following data:

Title                                         | Volume | Price
---------------------------------------------------------------
Girl Friends Omnibus                          | 1      | 9.99
Girl Friends Omnibus                          | 2      | 9.99
Kisses, Sighs and Cherry Blossom Pink Omnibus | 1      | 9.99
Hana & Hina After School                      | 1      | 5.99
Hana & Hina After School                      | 2      | 8.99
Girl Friends Omnibus                          | 2      | 8.99
Girl Friends Omnibus                          | 3      | 9.99

If I wanted to use conditional formatting to highlight duplicates I'd use the "Format Unique or Duplicate Values" conditional formatting rule. However if I did that it would look for duplicates of just one row, so for the first column only Kisses, Sighs and Cherry Blossom Pink Omnibus would not be highlighted.

What I want, however, is for the highlighting to only occur if the first two columns are not unique. So only Girl Friends Omnibus Volume 2 should be highlighted. The price should not factor at all. In a sense Title and Volume serve to create a composite Primary Key if they were in a database.

When I try to look this up I get variations of this which highlights values in one column if they exist in another. This won't work for me since the example data shows that the two aren't comparable.

Best Answer

This can be done simply by using the COUNTIFS() function:

Worksheet Screenshot

Select the appropriate cells in the table, making sure that A2 is the active cell, and enter the following for the conditional formatting formula:

=COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2)>1


While this works for the simple cases, more advanced situations might require a different technique using SUMPRODUCT(). It would be a good idea to learn how to use this more advanced technique. (I always use instead of using COUNTIFS().)

The more advanced equivalent to the above formula would be:

=SUMPRODUCT(($A$2:$A$8=$A2)*($B$2:$B$8=$B2))>1
Related Question