• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Conditional formatting to identify differences in groups of items in a list

I am trying to set up a conditional formatting rule that will highlight blocks of rows where a cell value is different from another in a group of similar items.

So for example column A has a course number.  Each course has a number of events, all given the same number.

In column B is the status. Some of those courses will show as "Evaluated" and some as "Not Evaluated".  I want to be able to highlight the courses that have a mix of "Evaluated and Not Evaluated.  The courses where all events are Evaluated or all events are Not Evaluated should not be highlighted.

An example of the resulting highlighting I'd like to achieve is attached

Grateful for any quick help here for an urgent job.


  • 3
  • 3
1 Solution
Hi Colin,

Choose "Use a formula to determine which cells to format". For that formula, paste in the following:


Change the 14 to however many rows you have.

colin_thamesAuthor Commented:
Brilliant, almost there but for some reason the formatted cells are one row too high.  Maybe I got something wrong.   I've attached an example (you'll see that the columns are actually J and K)

I've set the bottom row as 10,000, so the formula I've used is now:
Hmm.. I can't see any reason why that would be that way. Could J16 be slightly different somehow? Like with a space before or after 8642, or with a decimal being hidden or something?

Does   =J16=J17     return TRUE?
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

colin_thamesAuthor Commented:
No, this seems to be an issue all the way through the table.  We've checked and teh numbers are identical.  No spaces, no decimals, no rounding.  The conditional format highlighting is one row out.

I'm afraid I'm not smart enough to understand how your formula works.

Any ideas?
The formula is pretty simple once you break it down, so I'm guessing the problem lies in where the conditional formatting was first entered (if it was in row 1 or row 3, it could skew the data one row since it refers to row 2).  You could try to clear the current formatting, and then re-enter it.

For what it's worth, here's how the formula works. Essentially it consists of two parts

The first part counts the number of cells in J that equal J2. For offering 8642 this equals 16.
The second part returns a count of cells in J that equal J2 AND cells in K that equal K2. For 8642 / Not Evaluated this is 4. I can break this SUMPRODUCT formula down further if you'd like. In newer versions of excel, the COUNTIFS() function would do the same thing. I'm just an old dog that forgets the new tricks sometimes.
Then the formula asks if the two formulae return a different result ("<>" means "not equal"). In row 2's case, it looks to see if 16 is not equal to 4. Since they are not equal, it returns a TRUE value, which is what triggers the conditional formatting.
colin_thamesAuthor Commented:
Hi mvidas
You star!  You were quite right.  We'd highlighted the column so it threw out the highlighting by a row.

Thanks also for the explanation of how the formula works.  I've learned something as well as having a problem solved.

I wouldn't have thought of using countif but having read the explanation it's a brilliant way to go.

Major kudos to you and, of course, points!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now