Showing a difference between values

**MattG** · 09-14-2009, 04:02 PM

I have a spreadsheet with a lot of rows and there are three columns that can have a one of two values in it. These values are constantly changing from row to row, not just like a yes or no sort of thing. However 80% of the time they are the same values in all 3 columns, but I want some way of quickly identifying when there is a difference between column G, I, & K which are my three column names. So is there a way of conditionally formatting this to make a difference highlight in red or something like that?

Matt

**NBVC** · 09-14-2009, 04:07 PM

Select all 3 columns and go to Format|Conditional Formatting and select Formula Is from 1st drop down...

Enter formula: =COUNTIF($A1:$C1,A1)<>3

where A1:C1 are top 3 cells in your selection and then click Format and choose colours...

This will highlight all 3 cells in the row, if not all 3 are equal.

**MattG** · 09-14-2009, 04:23 PM

This is going to sound like a real newbie sort of question and because I am it is. So my columns are not consecutive, they skip, how do I enter the value that it is looking for as individual columns, not columns a:c, like a and c and e. I tried to find this in the help, but not sure on the phrasing of what I am looking for.

Thanks,
Matt

**NBVC** · 09-14-2009, 04:30 PM

Try then,

=OR(A1<>C1,C1<>E1)

**MattG** · 09-14-2009, 04:45 PM

Okay you are pretty helpful at this, now riddle me this. So say I am using columns A,C, and E. With this it appears we are assuming A is correct. When I put it in lets say I have the number 10 in A2, the number 10 in C2 and the number 10 in E2. Well I do not want this to do anything in my conditional formatting and the last function we tried was always formatting my Column A number as a base or something. Does this make any sense? I only want it to show the discrepencies between the three.

Thanks for the help on this!

**NBVC** · 09-14-2009, 04:52 PM

Try:

=NOT(AND($A1=$C1,$C1=$E1))

**ChemistB** · 09-14-2009, 04:54 PM

Going back to NBVC's original formula, putting a $ in front of the Cell ID will fix it

Please Login or Register  to view this content.

Hope that helps.

**NBVC** · 09-14-2009, 04:57 PM

Originally Posted by ChemistB

The formula NBVC gave you will shade your cells if either A does not equal C or C does not equal E. This will only be true if at least 1 (any 1) of your cells doesn't equal the others. Your cells will not be shaded if all three values are the same. Isn't this what you are looking for?

Except I should have made the column references absolute:

=OR($A1<>$C1,$C1<>$E1)

**MattG** · 09-14-2009, 04:58 PM

Excellent that is what I was looking for!

Now this is just a side thing that I don't really need to know, but just interested if there is a way. Is there a way to add the additional formatting to change the ones that are different in the series of three? I am not sure how that would work, because we would be implying that two of the cells are correct and only one is ever different. I may be just talking crazy talk here.

**NBVC** · 09-14-2009, 05:02 PM

You would have to separately format each column...

Select Column A and apply:

=AND(A1<>C1,A1<>E1)

Format

Select Column C and apply

=AND(C1<>A1,C1<>E1)

Format

Select column E and apply

=AND(E1<>A1,E1<>C1)

Format

This will highlight the "odd" one out... or if all 3 different...