I'm using Excel 2010 and would like to know if there's any way I can choose conditional formatting to highlight only instances of duplicates when it's found in ALL the columns I chose?
For instance, I have six columns of data, with duplicates in them, but I only want those duplicates to be highlighted if they appear in ALL SIX columns.
E.g.
Column A Dog Cat Fish Horse Platypus
Column B Cat Platypus Panda Chicken Dog
Column C Bird Zebra Giraffe Platypus Panda
Column D Platypus Bird Dog Zebra Horse
Column E Otter Lion Platypus Giraffe Zebra
Column F Lion Ostrich Platypus Dog Snake
Only "Platypus" appears in all six columns, but "Dog", "Cat", "Horse", etc all have one or more duplicates, which will usually end up being highlighted. If I can find a solution that will allow me to have the flexibility of choosing to highlight instances of duplicates in 5 out of 6 columns, 4 out of 6 columns, 3 out of 6 columns, etc, that'd be even better!
Note that the data is not arranged nicely in a row, so that I couldn't use "Countif" across rows to see how many times "dog" appeared in Column A-L on a specific row (i.e. "dog" could appear anywhere in the columns, not necessarily on the same row).
If anyone has any tips on this, I'd really appreciate it, thanks!!
Best Answer
Set up a conditional formatting rule for the entire table with the following formula:
This checks that the value of the cell is in each column.
Example:
To highlight values that are duplicated in 5 columns, you can use the following rule:
This can be customized to other numbers of duplicates by changing the last number of the formula.
Example: