I need to highlight duplicates in column A, but only if column B is equal to 10.

67890 10

67891 10

67891 20

67891 30

67892 10

67892 20

67894 10

67895 10


In this example, what I want it to do is, as soon as I input the value on column A the cell automatically changes colour.

I have tried using sumproduct and countifs on conditional formatting, but it does not highlight the values.

Thank you in advance.

  • 2
    How is this a Apple problem ? In this example you do not have duplicates ?
    – Ruskes
    Jul 13, 2015 at 22:52
  • 1
    This should be moved to Super User, it's not about Apple. Jul 13, 2015 at 23:34
  • 1
    I'm voting to close this question as off-topic because ....
    – Ruskes
    Jul 14, 2015 at 1:02
  • This isn't off-topic. Unless the OP requires a Windows Excel version - it's just fine here. Even if it's more suited for another site, we don't migrate unless the OP asks for it or it's so clearly defined that it would fly on the new site. It might need to be closed as unclear since there are no version information for the OS or the spreadsheet software, but even then - someone could explain how to do this in numbers or excel even without that detail. Thanks for the flags, but this isn't good enough to migrate to another site.
    – bmike
    Jul 14, 2015 at 2:14
  • 1
    @bmike IMHO this question is OS-agnostic, i.e. the commands would be the same on Excel for OS X and Excel for Windows. It has much higher chance to be solved on SO.
    – n1000
    Jul 14, 2015 at 6:37

1 Answer 1


I found a solution; it's not "pretty", but it works. Unfortunately, it is a bit hard to describe, so I'll make an example file with explanations available, instead. In short, it uses

  • a "helper cell" the locate and mark duplicates
  • Excel's use a formula to determine which cells to format Conditional Formatting option to then color the original cells
  • The link is broken
    – Matt
    Jul 18, 2018 at 16:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .