0

I am comparing setting files between devices and have loaded all the settings into an excel sheet. I want to see if any setting is deviating between the devices.

Example:

Setting1: ok ok ok ok ok

Setting2: 100 100 104 104 103

Setting3: no yes no yes yes

Setting4: 30 30 30 30 30

Now i want line 2 and 3 to be highlighted because the values are not equal on those rows. How to do that? I tried a conditional formatting with "A1=B1=C1=D1" should be green, but that did not work

Improve this question

1 Answer 1

Reset to default
2

Alright, assuming your data is set up so column A is a label row, we have data from B1:F4.

First, add a helper column in between columns A and B. In B1 enter the following formula:

 =IF(AND(C1=D1,C1=E1,C1=F1,C1=G1),TRUE,FALSE)

Then copy this formula down to row 4. Your helper column should now have a value of either TRUE or FALSE for each row.

Now highlight from B1:G4 and select Conditional Formatting. Add a new rule, and select the option "Use a formula to determine which cells to format".

On Office '11 I have to select "Style: Classic" first, I'm not sure if this is necessary in Windows versions.

In the formula bar for your rule, enter "=$B1=FALSE" and select Ok.

This should do it!

Improve this answer
2
  • You can do this without the helper column as well. Put =NOT(AND($C1=$D1,$C1=$E1,$C1=$F1,$C1=$G1)) directly in the conditional formatting formula.
    – andy holaday
    Feb 25, 2014 at 0:46
  • Thank you. I used your solution and it worked very well :)
    – fluxmodel
    Feb 25, 2014 at 15:53

You must log in to answer this question.

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